January 25, 2021 at 12:00 am
Comments posted to this topic are about the item Power Query or SQL Server? Which, when and why
January 28, 2021 at 7:18 pm
I've experimented with PowerBI, but not PowerQuery. From what is said in this blog, it seems that PowerQuery was created for the SQL uninitiated. Having been a professional SQL DBA/Developer/Architect for 2.5 decades, I prefer Stored Procedures for a number of reasons. 1) It encapsulates the code, so that the calling application doesn't need to know what's going on in the background, making changes seamless. 2) You can pass parameters into the stored procedure, making it generic instead of specific. 3) Stored procedures keep their query plans in a cache. This reduces response time because the query optimizer only kicks-in if the query-plan expires. If the report is run often, then it reuses the plan in the cache, which makes response time is less. 3) Security is better with Stored Procedures. It can be very granular who can execute the Procedure, or you can give a whole group access to it.
When the data lives in SQL tables, I will always go to Stored Procedures to do CRUD. What I don't know is how PowerQuery stacks up to OpenRowset in T-SQL. OpenRowset uses a data provider (i.e. Jet4 to access Excel). The number of external providers has more than doubled in recent years with addition of XML & JSON and many others. However, there is always a resource cost to reaching out beyond SQL. Whether that cost is more expensive with OpenRowset or PowerQuery is the question on my mind.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply