October 17, 2022 at 10:01 pm
Currently, I have a stored procedure that produces a dataset. A parameter value is passed in, and this causes the stored procedure to execute for each parameter value. What I need is for the procedure to execute once without the parameter (I already modified the SQL code to process all values in one swoop), BUT with the option to filter using what was the parameter. So instead of passing in a value for each run, I am looking to have everything in the dataset and use the parameter as a filter instead. The reason I am doing this is for better performance. I can't have the procedure run hundreds of times. How can I accomplish this? Other ideas are welcome as well, if this isn't a good approach.
October 17, 2022 at 10:56 pm
Is this for reporting queries? Is this approach driven by knowledge/belief that the query is too expensive to run repeatedly or blocks other processes? Are the tables used in the queries indexed correctly for the queries?
If I'm following your logic, are you saying you'd like the first run of the procedure to get an unfiltered result set, & then have any run that uses parameter filters somehow get data from that initial resultset rather than via querying the supporting tables? If so, you'd need to insert the data into a different table (properly indexed for your various queries), & then query that when parameters are passed. But how will you ever refresh that data?
I think you need a procedure (maybe even a scheduled job) to populate the full query-result table, & a separate procedure with parameters that queries the query-result table.
You could potentially also accomplish this by turning your unfiltered query into an indexed view, but I don't recommend that. That would add cost to every insert/update into the tables involved, and has restrictions that can be hard to meet.
October 17, 2022 at 11:25 pm
Thank you for your response. To clarify, I have a stored procedure that used to pass in a multi-value parameter, so the proc was running over and over for each parameter value. I removed the parameter and now the proc is running once, producing a dataset comprised of all values. What I would like to do is filter off of that full dataset for each value I previously had passed in as a parameter. The proc was previously running for each value, which became very resource-intensive. I need to run it once, produce a full dataset, and then filter off of that.
October 18, 2022 at 1:17 am
Can you post the code? Or a reasonable example?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply