SSRS 2008 Temp Table


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 )

             MM VARCHAR(6)

             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
more ▼

asked Jun 22, 2012 at 08:07 PM in Default

avatar image

0 1 1 2

@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.

Jun 25, 2012 at 02:13 PM chan

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!!!

Jun 25, 2012 at 02:28 PM Kev Riley ♦♦

+1. Nice example ;)

Jun 25, 2012 at 03:15 PM Usman Butt

@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.

Jun 25, 2012 at 03:55 PM chan
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered Jun 22, 2012 at 08:17 PM

avatar image

Kev Riley ♦♦
66k 48 63 81

You could also be running into tempdb contention, especially if you are loading large datasets into the temp tables.

Jun 23, 2012 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, 2012 at 12:00 AM chan

Try using local variables instead of the parameters in the query

Jun 25, 2012 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, 2012 at 10:30 AM Sacred Jewel
(comments are locked)
10|1200 characters needed characters left

Try to Lock a Stored Procedure for Single Use Only, I think that this would be a good solution for your problem.

more ▼

answered Jun 25, 2012 at 08:13 AM

avatar image

323 24 26 31

That would rule out ANY concurrency - there are better ways of solving this problem.

Jun 25, 2012 at 08:18 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 22, 2012 at 08:07 PM

Seen: 3034 times

Last Updated: Jun 25, 2012 at 03:55 PM

Copyright 2017 Redgate Software. Privacy Policy