|
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] 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
(comments are locked)
|
|
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 could also be running into tempdb contention, especially if you are loading large datasets into the temp tables.
Jun 23 '12 at 04:17 AM
SirSQL
@sirsql -- I have about 2232 rows as the output of 1 st table and 100 rows as the final output of second table. I am just wondering why the execution time varies with Temp tables only when multiple users access the report
Jun 25 '12 at 12:00 AM
chan
Try using local variables instead of the parameters in the query
Jun 25 '12 at 08:15 AM
Kev Riley ♦♦
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 ;)
Jun 25 '12 at 10:30 AM
Sacred Jewel
(comments are locked)
|
|
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.
Jun 25 '12 at 08:18 AM
Kev Riley ♦♦
(comments are locked)
|


@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.
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!!!
+1. Nice example ;)
@Kev Riley -- Good example, I know, but sometimes u have to work the way it is. Thanks all for your time, i will try looking at the execution plan hopefully soon.