x
login about faq Site discussion (meta-askssc)

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

asked Jun 22 '12 at 08:07 PM in Default

chan gravatar image

chan
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 '12 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 '12 at 02:28 PM Kev Riley ♦♦

+1. Nice example ;)

Jun 25 '12 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 '12 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 '12 at 08:17 PM

Kev Riley gravatar image

Kev Riley ♦♦
46.1k 38 43 69

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)
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 '12 at 08:13 AM

Gogolo gravatar image

Gogolo
313 12 21 25

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

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x42
x20
x16

asked: Jun 22 '12 at 08:07 PM

Seen: 899 times

Last Updated: Jun 25 '12 at 03:55 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.