0 Kudos

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

Status: New
by Novox Frequent Visitor on ‎06-08-2018 12:46 PM

 

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

Idea Statuses