July 30, 2008 at 10:12 am
So this is one of those situations that I was asked to look into that I haven't ever looked into.
In my database we run several reports where for the bulk of them I'm building this same temporary table that I later perform operations against according to the report. To reduce maintenance I'm looking for a way to call this temp table in the report query. Currently I'm just copying and pasting the same code that builds the table into the stored procedure. The structure for this table is always the same but the data is filter off of the variables being passed into the store procedure.
I'm not sure if i should just create a store procedure that creates this temp table, and then just call the temp table stored procedure from my report queries. I would think a View but I didn't think i could filter the view with the variables for the report query before the View is called.
So i was just curious if anyone had any advice for my situation.
Thanks
July 30, 2008 at 10:39 am
It depends on the problem you are trying to solve.
If this is a code-reuse issue you are trying to deal with, you could encapsulate the code for populating the temp table into a stored procedure. You would have to create the temp table before calling the procedure or your temp table would go out of scope when the procedure populating it completed (thus not being available for your main procedure to use).
If you are trying to improve performance, it may be worth it to materialize the data into an actual table and populate the table regularly. This way, your procedures would not have to create and populate the table every time they ran. If it takes more than 10 or 15 seconds to populate the table, this could make your reports nice and snappy, but you would have to deal with the contention of the reports running while populating the table (pretty easily handled by locks).
July 30, 2008 at 11:17 am
I only need the temp table for the life of the procedure. I was thinking of maybe combining your two ideas, maybe creating a View with the data that i might need, then run some filter through that view and create a temp table off of that view then run my query through that temp table. I suppose a big issue is just going to be performance. I'm really just looking to get away from copying and pasting the code for this temp table because that idea just doesn't seem to eloquent and a head ache if i ever decided to go back and change that temp table i'd have to find ever stored procedure where i used that temp table and update it.
July 30, 2008 at 12:51 pm
Your idea is a good one. You might even look into using an indexed view instead to populate the temp table, to improve performance.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2008 at 1:10 am
If the structure of the temp table remains the same for all the reports, and only the data is changed according to the criteria, then the INDEXED VIEW will be the BEST solution.
Another drawback of creating temp table at runtime is that you may find an error when two users generate the report at the same time. The temp table will support only one user and other will get the error as the temp table will already be there for one of the users.
STILL Choice is URS...
Atif Sheikh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply