x

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 ♦♦
50.7k 43 49 76

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
323 19 26 27

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

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

Topics:

x49
x22
x19

asked: Jun 22 '12 at 08:07 PM

Seen: 1800 times

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