Using a date or date/time parameter as a filter always results in 4096 rows being returned first

[Novox]

With a SQL data source, when I add a filter to a query on a date or date/time column with a literal value, it works as expected.

When I instead use a date or date/time parameter as the value for the filter on my date or date/time column, it ALWAYS retrieves top 4096 rows, then, hits the database again to actually perform the query.

I tried the same thing with a Decimal (int) type filter/parameter combination and there were no issues.  It immediately queried the correct number of rows (not first 4906) as in the intial "literal" case...

I did not try any other parameter types.

I traced the queries against my SQL server.

This SQL:BatchCompleted happens:

select top 4096
    [$Ordered].[Id],
    [$Ordered].[CenterId],
    [$Ordered].[BusinessDate],
    [$Ordered].[IDCash],
    [$Ordered].[CashDrawerName],
    [$Ordered].[OpenTime],
    [$Ordered].[OpenUser],
    [$Ordered].[CloseTime],
    [$Ordered].[CloseUser],
    [$Ordered].[ReportAmount],
    [$Ordered].[Shift_Id]
from 
(
    select [Id],
        [CenterId],
        [BusinessDate],
        [IDCash],
        [CashDrawerName],
        [OpenTime],
        [OpenUser],
        [CloseTime],
        [CloseUser],
        [ReportAmount],
        [Shift_Id]
    from [dbo].[CashTurns] as [$Table]
) as [$Ordered]
order by [$Ordered].[Id]

Then a few events later, I see my actual query, SQL:BatchCompleted:

execute sp_executesql N'select [_].[Id] as [Id],
 [_].[CenterId] as [CenterId],
 [_].[BusinessDate] as [BusinessDate],
 [_].[IDCash] as [IDCash],
 [_].[CashDrawerName] as [Drawer Name],
 [_].[OpenTime] as [Open Time],
 [_].[OpenUser] as [Open User],
 [_].[CloseTime] as [Close Time],
 [_].[CloseUser] as [Close User],
 [_].[ReportAmount] as [Amount],
 [_].[Shift_Id] as [Shift_Id]
from 
(
 select [Id],
 [CenterId],
 [BusinessDate],
 [IDCash],
 [CashDrawerName],
 [OpenTime],
 [OpenUser],
 [CloseTime],
 [CloseUser],
 [ReportAmount],
 [Shift_Id]
 from [dbo].[CashTurns] as [$Table]
) as [_]
where [_].[BusinessDate] = convert(datetime2, ''2018-06-04 00:00:00'') and [_].[CenterId] = 2'

While it does eventually work, Power BI could potentially, unnecessarily, download 33,013,760 bytes (~33MB = 8,060 max bytes * 4096 rows) for every table in my pbix report.  I happen to have about 8 tables, so I'm looking at potentially downloading ~264MB of data from my data store that is almost instantly discarded... 

Obviously, my 8 tables don't have rows of maximum size, but ANY extraneous data downloaded/imported seems quite unnecessary and incorrect behavior.

Please advise,

Thank you,

~Bill

[v-qiuyu-msft]

Hi @Novox,

Based on my research, though the backend query to retrieve data mentions top 4096, it actually all valid rows. In Query Editor after you set filter, you can apply the change, then go to Data view, you will find the number of rows on the bottom. 

Best Regards,
Qiuyun Yu

[Novox]

The top 4096 rows that it retrieves for every table with a date or date/time filter parameter are not all valid rows.  I can see this in both the refresh screen in Power BI and SQL Profiler.  Power BI first shows the row count as 4096, then shows a much lower row count prior to disappearing.  SQL profiler shows identical activity with a "top 4096" of ALL rows (ordered by Id, which happens to be the clustered key), followed by my actual query.

The refresh window will show this (top 4096 rows or all rows for tables with fewer than 4096):

Then seconds later, it shows this (actual row counts from my defined queries):

My Power Query M query is:

let
    Source = Sql.Database("(local)", "DataWarehouse_xxxxxx", [HierarchicalNavigation=true]),
    dbo = Source{[Schema="dbo"]}[Data],
    CashTurns1 = dbo{[Name="CashTurns"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(CashTurns1,{"OpenAmount", "OpenTerminal", "CloseTerminal", "CloseAmount", "UnderOver", "UnderOverNote"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CashDrawerName", "Drawer Name"}, {"OpenTime", "Open Time"}, {"OpenUser", "Open User"}, {"CloseTime", "Close Time"}, {"CloseUser", "Close User"}, {"ReportAmount", "Amount"}}),
    Filter = Table.SelectRows(#"Renamed Columns", each [BusinessDate] = #"Business Date" and [CenterId] = 1)
in
    Filter

The Native Query is:

select [_].[Id] as [Id],
    [_].[CenterId] as [CenterId],
    [_].[BusinessDate] as [BusinessDate],
    [_].[IDCash] as [IDCash],
    [_].[CashDrawerName] as [Drawer Name],
    [_].[OpenTime] as [Open Time],
    [_].[OpenUser] as [Open User],
    [_].[CloseTime] as [Close Time],
    [_].[CloseUser] as [Close User],
    [_].[ReportAmount] as [Amount],
    [_].[Shift_Id] as [Shift_Id]
from 
(
    select [Id],
        [CenterId],
        [BusinessDate],
        [IDCash],
        [CashDrawerName],
        [OpenTime],
        [OpenUser],
        [CloseTime],
        [CloseUser],
        [ReportAmount],
        [Shift_Id]
    from [dbo].[CashTurns] as [$Table]
) as [_]
where [_].[BusinessDate] = convert(datetime2, '2018-06-07 00:00:00') and [_].[CenterId] = 1

Query Editor does show the proper number of rows in the Data view.

Again, this issue does not happen when I use a Decimal (int) filter parameter (haven't tried other data types, but maybe it's time).

[Novox]

These filter parameter data types cause the extraneous top 4096 queries:

• Date/Time
• Date
• Time
• Date/Time/Timezone
• Duration - (Power BI won't let me set a Time column to a Duration, but it will let me change the Time type to Text, then the Text type to Duration...)

These filter parameter data types do not cause the extraneous top 4096 queries:

• Decimal Numer
• Text
• True/False
• Any (unless you're trying to filter a date/time type SQL column)

(I didn't test Binary.)

It would therefore seem that the top 4096 issue is only for date/time related data types.

[Novox]

Can anyone else confirm this?  I'm surprised no one has Date/Time type filter parameters in their queries.