Solved: Power Query Vs SQL Efficiency

Anonymous · ‎11-23-2018

Hi all,

I have a bit of experience with both sql and power query and I was wondering what uses less computing power and bandwidth: querying with SQL or power query.

For example if I was going to select dog type, dog cost and dog breed from a million row DB for the time range 2017 to now, is it roughly the same time and power to do this with power query and SQL? are there positives and negatives Im not aware of?

Thanks!

AkhilAshok · ‎11-23-2018

When connecting to SQL DB, Power Query tries to do Query Folding and tries to push maximum logics to data source, means the time take in Power Query and SQL will be the same in such cases. In your example, ideally Power Query should just trigger a SQL with a where clause for the time filter. You can see this by checking "View Native Query" option.

In case you do some transformations which PQ cannot convert to SQL, then it will take more time to process, since PQ has to bring in all the data and do the Transofrmation on its side.

View solution in original post

AkhilAshok · ‎11-23-2018

When connecting to SQL DB, Power Query tries to do Query Folding and tries to push maximum logics to data source, means the time take in Power Query and SQL will be the same in such cases. In your example, ideally Power Query should just trigger a SQL with a where clause for the time filter. You can see this by checking "View Native Query" option.

In case you do some transformations which PQ cannot convert to SQL, then it will take more time to process, since PQ has to bring in all the data and do the Transofrmation on its side.