April 12, 2022 at 9:16 pm
I have a report that has many datasets that populate different tables. Depending on an ID tables are being shown or hidden. The issue we have is regardless of the show/hide the stored procedure for that table runs against the server. We have 10 datasets for one entity and have this set up to run for thousands of entities at a time, creating a lot of overhead on the server. Is there a better way to do this so, for instance if table 4 is the only data needed then ONLY that SP is run?
Any help is appreciated here, been banging my head against the wall for too long.
April 13, 2022 at 2:06 pm
It sounds as if you need multiple stored procedures. Call the appropriate one depending upon the ID.
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/
April 13, 2022 at 2:44 pm
Sounds like you're trying to build a universal query, or a catch-all query. These things are notoriously difficult, and as the linked article explains in detail, extremely inefficient (I can't seem to link to two of Gail's blog posts on the topic, but they're out there). You're almost always better off building a query for the information your need. Period. Full Stop. If that means more than one report, it means more than one report.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply