why adding filepath slows down the query ?

Question

Hi ,

  I am trying to create view for the partitioned parquet files in my filesystem (TB of data). when querying an single parquet file without adding any filepath wildcard performs faster and returns result within few seconds   

But when I introduce filepath wildcard , it slows down a lot (running more than 10 min). Both supposed to query the same file and i am expecting the same performance. Please advice if I am doing anything wrong

Answer 1

I guess you need to use dynamic SQL:

SELECT @sql = concat('SELECT TOP * 100
         FROM  OPENROWSET (BULK, 'http://.../Day=', @day, '/TripID=', @trip', 
          '/*.parquet', FORMAT = PARQUET) AS result'
EXEC sp_executesql

From what I can tell this is supported on Synapse as well.

Answer 2

Let me start off to say that I know very little about Synapse.

But I can't say that I am surprised by the performance difference. For the performance to be the same, the optimizer would know how to push the arguments in the WHERE clause into the OPENROWSET function. Else OPENROWSET needs to retrieve all data, and only then Azure Synapse is able to filter it. On the other hand, with an exact path, OPENROWSET can go straight to the matter.

But as I said, I do not work with Synapse, so I could be completely off target.

Answer 3

Hi @Mahesh Raja Mahalingam ,

Thank you for posting your query in Microsoft Q&A Platform.

Could you please try using IN keyword for filepath() function and see how it behaves. Click here to know about filepath() function