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
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?
answered Jun 22 '12 at 08:17 PM
Kev Riley ♦♦
Try to Lock a Stored Procedure for Single Use Only, I think that this would be a good solution for your problem.
answered Jun 25 '12 at 08:13 AM