Serverless SQL Pools filepath() function and range filters

Question

Serverless SQL Pools filepath() function and range filters

Serverless SQL 216 MVP

Hi,

I have a question about why Serverless SQL Pools scans all data in the source folders when I use the filepath() function as a column in a View and join a Date table to it.

EG this query will scan all data in the source folder. The FilePathDate is a column in the [LDW].[vwSalesOrdersLinesBI] view that exposes the filepath() value

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date between '2016-07-08' and '2016-07-10'

However, if I change the query to use IN and remove the join, then only the specific source folders are queried and data processing is reduced:

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
--where fct.FilePathDate IN (SELECT '2016-07-08' UNION SELECT '2016-07-09' UNION SELECT '2016-07-10')
where fct.FilePathDate IN (CAST( '20160712 00:00:00' AS datetime),CAST( '20160726 00:00:00' AS datetime),CAST( '20160713 00:00:00' AS datetime))

HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-06-28T22:17:15.277+00:00

Hello @Serverless SQL ,
Thanks for the ask and usng the Microsoft Q&A platform .
Can you please share the the data in the vwDimDate view ? I see that the FilePathDate have values like 2016-07-12 00:00:00.000 but then you are passing the values as 2016-07-08 and 2016-07-10 with a between clause .

When you say that

scan all data in the source folder

. is it not only for the range 2016-07-0 and 2016-07-10?

When you are using IN clause Determines whether a specified value matches any value in a subquery or a list. . In this case its the few days whcih you passed .

In case of BETWEEN the operator is inclusive: begin and end values are included. , in this case all the date values starting from 2016-07-08 and 2016-07-1' , if you have the folder path with a different timestamp (eg 02:05:30.000) , those will also be captured in this case .

Please do let me know how it goes .
Thanks
Himanshu
Serverless SQL 216 Reputation points MVP

2021-07-01T11:18:02.77+00:00

Hi,

Here's the data from both Views:

Basically if you use IN and pass a list of dates in then only those folders that match the date values are processed. If you use a JOIN then this is not respected and all folders are scanned, it seems that using a JOIN means Serverless SQL ignores the date ranges and just scans all data
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-07-01T23:35:20.057+00:00

Can you please let me kow what happens when you run the query

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date in ('2016-07-08' ,2016-07-10')

Thanks
Himanshu

1 answer

Your answer

HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-06-28T22:17:15.277+00:00

Hello @Serverless SQL ,
Thanks for the ask and usng the Microsoft Q&A platform .
Can you please share the the data in the vwDimDate view ? I see that the FilePathDate have values like 2016-07-12 00:00:00.000 but then you are passing the values as 2016-07-08 and 2016-07-10 with a between clause .

When you say that

scan all data in the source folder

. is it not only for the range 2016-07-0 and 2016-07-10?

When you are using IN clause Determines whether a specified value matches any value in a subquery or a list. . In this case its the few days whcih you passed .

In case of BETWEEN the operator is inclusive: begin and end values are included. , in this case all the date values starting from 2016-07-08 and 2016-07-1' , if you have the folder path with a different timestamp (eg 02:05:30.000) , those will also be captured in this case .

Please do let me know how it goes .
Thanks
Himanshu
Serverless SQL 216 Reputation points MVP

2021-07-01T11:18:02.77+00:00

Hi,

Here's the data from both Views:

Basically if you use IN and pass a list of dates in then only those folders that match the date values are processed. If you use a JOIN then this is not respected and all folders are scanned, it seems that using a JOIN means Serverless SQL ignores the date ranges and just scans all data
HimanshuSinha-msft 19,486 Reputation points Microsoft Employee Moderator

2021-07-01T23:35:20.057+00:00

Can you please let me kow what happens when you run the query

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date in ('2016-07-08' ,2016-07-10')

Thanks
Himanshu

Answer 1

Hi,

If I run that query then I get all the data being scanned 6.49GB (which is the source CSV data in the data lake).

If I run the following query then I only get 29MB scanned

select count(fct.OrderID)
from [LDW].[vwSalesOrdersLinesBI] as fct
inner join [LDW].[vwDimDate] dd on dd.Date = fct.[FilePathDate]
where dd.Date in ('2016-07-08')

It seems that if there are multiple values being passed to the DimDate table to filter, then it doesn't select only the source folders, it just scans all the source data. If a single value is passed in, then it will successfully only scan the folder it needs to.

Share via

Serverless SQL Pools filepath() function and range filters

1 answer

Your answer