October 8, 2015 at 1:30 pm
Guys,
For creating a report on report builder which method would be more efficient, calling it as query type 'Text' copying the whole code and paste it on the report. Or converting the code to stored procedure @ SSMS and then calling it as a Stored procedure. Code has no parameters.
October 8, 2015 at 1:38 pm
The SQL query is executed server side, so it will run approximately the same, regardless of where it originates (SSMS, SSRS, stored procedure).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 8, 2015 at 1:53 pm
Thanks Eric, makes sense, I did not make any difference, I tried both.
October 8, 2015 at 4:29 pm
One caveat... SQL Server will create a reusable execution plan for the stored procedure but will treat the "text" code as ad-hoc and will need to create a new plan any time a parameter value changes.
As a side bonus, the code is easier to maintain when it's not burried in RDL files.
October 9, 2015 at 7:16 am
For an OLTP database (medium size tables, single inserts, many users, mostly small repetitive queries), you want re-usable query plans as much as possible.
However, in a data warehouse (very large tables, batch inserted, occasional usage, ad-hoc queries), cached query plans are less useful. In fact, they can degrade performance, because the query plan cache fills up with single-use plans, consuming memory that could best be used by the page buffer cache. Also, because DW tables are batch imported, these plans quickly become sub-optimal due to changing statistics. It actually better to allow SQL Server to rebuild the query plan for each execution, use it, and then discard it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 9, 2015 at 9:15 am
That still makes a case for using stored procs. SQL Server caches plans for ad-hoc sql. I just creates and caches one for every distinct version. So, executing the same ad-hoc script twice with two different parameter values results in two identical (or nearly identical) plans in cache.
October 9, 2015 at 9:23 am
When I say ad-hoc queries, I'm referring to scenarios where users are typing SQL into a query tool ad-hoc, or perhaps where an application has a search feature with an unlimited combination of search conditions or table joins.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply