asked
SSRS 2008 Temp Table
Hi, Here is the what i have encountered. I have a report which uses a couple of temp tables. The reports are deployed and here is the issue. The execution time of report is changing with No.of users. If 2 or 3 users run the report at the same time, then the time taken to run the report is longer almost double. Is it bcoz of using temp tables. Anyone encountered it before. what is the solution for this. [Edit by moderator. Details transferred from content supplied in an answer] @Kev Riley -- Thanks for the quick reply Here is the psuedo code. If i replace the temp tables with temp variables, then with no of users the execution time remains the same. Any idea why this happens with only temp tables I have 3 parameters, they are the zone, state and job type CREATE TABLE #table1 ( Cnt INT, MM INT ) CREATE TABLE #Mth ( MM VARCHAR(6) ) INSERT INTO #Mth(MM) SELECT -- (12 Distinct Months ) INSERT INTO #table1 Select --------- FROM (Joins) WHERE ----------- GROUP BY -------------- SELECT * FROM #table1 -- DataSet Output DROP TABLE (Temp Tables), If i replace the temp tables with temp variables, then with no of users the execution time remains the same. Any idea why this happens with only temp tables
This may sound harsh, but if you've not got the access to view the execution plan, you might be the wrong person to ask sort out the performance issue. It's like asking a mechanic to look at your badly running car, but then say he can't have the keys!!!
@Sacred Jewel -- Thanks for the quick reply I am not using Stored proc and i typically join 4 tables for the report and i do not have access to look at execution plan !!! Well i executed the same report from 2 or 3 different PC's in report viewer. Thats how i saw the slow performance. Any insight is greatly appreciated.
At a guess I would say that you have a bad execution plan, possibly caused by parameter sniffing, possibly due to differing uses of the underlying query - can you post the query and sample parameters?
You can also use OPTION(RECOMPILE), OPTION(OPTIMIZE FOR @variable_name = literal_constant [ ,…n ] ) for that matter. I've found the latter more handy in certain cases. I feel the ugly code is hidden under your joins (missing indexes) and you are highlighting the temporary table issue. It can also be a case of outdated statistics... It will be interesting to know how did you find that it happens when multiple users access it? Why don't you attach the execution plan to clarify?....Also, your psuedo code does not justify the use of temporary tables at all. Post your query and hopefully I will show why don't you need them ;)
Try using local variables instead of the parameters in the query
Try to Lock a Stored Procedure for Single Use Only, I think that this would be a good solution for your problem.
That would rule out ANY concurrency - there are better ways of solving this problem.
12 People are following this question.
