473,696 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to find query plan for a stored procedure using temporary tables

This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro

--*************** *************** *****
use pubs
go
CREATE PROCEDURE Test @percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor

--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)

--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1

go
set showplan_Text ON
go
EXEC Test @percentage = 100
GO
set showplan_Text OFF
go
*************** *************** ********

I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.

I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.
Regards

Oct 25 '06 #1
3 6869
On 25 Oct 2006 15:41:52 -0700, comp_databases_ ms-sqlserver wrote:
>This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
(snip)
>I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.
Hi comp_databases_ ms-sqlserver,

You get these errors because SET SHOWPLAN_TEXT ON tells SQL Server to
generate a plan INSTEAD OF executing the SQL. As a result, your temp
tables are not generated.

The only way to get execution plans is to allow SQL Server to execute
the statements as well as outputting the plan. You do this by issuing
the command
SET STATISTICS PROFILE ON;
Note that this includes other (run-time) info as well as the plan.

Of course, you can also decide to use CREATE TABLE for all temp tables
at the start of your procs instead of using INSERT INTO.

--
Hugo Kornelis, SQL Server MVP
Oct 25 '06 #2
SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.

Oct 26 '06 #3
On 26 Oct 2006 11:29:57 -0700, comp_databases_ ms-sqlserver wrote:
>SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.
Hi comp_databases_ ms-sqlserver,

That's a pretty broad question!

Some of the things I'd look into if I was assigned this task would be
(in random order):

* Try to combine some or even all steps of the procedure into one single
query. Procs like this are often the result of procedural thinking. New
SQL coders with a background in procedural languages often tend to think
in the steps required to get somewhere. They will then code a sequence
of steps, with temp tables to hold intermediate results. A truly
set-based and declarative solution gives the optimzer more freedom to
rearrange steps and reduces the amount of moving data around. This can
yield huge benefits in performance.

* Create temp tables at the start of the stored proc. This reduces the
number of recompiles (and at 2000+ lines, recompiling the proc will
probably take a noteable amount of time). It also gives you the
opportunity to declare indexes on the temp tables BEFORE data is put
into them - this will reduce the number of recompiles even further and
it may speed up execution. However, it can also sometimes be better to
postpone index creation until after the temp table is populated, even
though this means accepting a recompilation. Test various strategies to
find out.

* Copy the code from the stored procedure to Query Analyzer and run it
one step at a time, using BEGIN TRAN, ROLLBACK and COMMIT as needed to
be able to repeat each step multiple times. This gives you the option to
get an execution plan for each step, and also to try different versions
of the query and/or different indexes to see how they change the
execution speed of that particular part of the proc.

* Setup a profiler trace, run the stored proc, then use the output from
the profiler trace to identify which part(s) of the stored proc are
responsible for the largest portion of the execution time.

* Check if your procedure might be subject to parameter sniffing (google
for it if you've never heard of the term).

* If you really can't combine the steps, consider breaking the procedure
in several smaller parts. This reduces compilation time when
recompilations are needed and can be leveraged to solve parameter
sniffing problems.

There are probably more things you can do, but these are the ones I can
think of at the top of my head.

Good luck!

--
Hugo Kornelis, SQL Server MVP
Oct 26 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7874
by: Jonathan | last post by:
Hi all! For a match schedule I would like to find all possible combinations of teams playing home and away (without teams playing to themselves of course). I now the simple version works something like this: For a (very) simple table containing three rows like this: row 1: A
10
3737
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty well and used a query plan that made sense. Now, I know what you're all thinking... stored procedures have to optimize for variable parameters, etc. Here's what I've tried to fix the issue: 1. Recompiled the stored procedure 2. Created a new,...
6
561
by: Umar Farooq | last post by:
Hello all, Please bear with the long explanation of my scenario. As I'm relatively new to the query world, I like to write my queries using the visual toos such as the "View" option in SQL Server or in MS Access. If I have a complicated query with sub-queries, I create a query (view1) as (for example): select ID,count(ID) as NumberOfUsers from tblContact
4
2455
by: serge | last post by:
I tried all the INFORMATION_SCHEMA on SQL 2000 and I see that the system tables hold pretty much everything I am interested in: Objects names (columns, functions, stored procedures, ...) stored procedure statements in syscomments table. My questions are: If you script your whole database everything you end up having in the text sql scripts, are those also located in the system tables? That means i could simply read those system tables...
1
2603
by: Sampath Reddy | last post by:
Hi Everybody, We are using UDB v8.1 I will explain about my Stored procedures which we are executing in UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By applying the business logic through Stored procedures on 22 tables and writing into 3 new tables. We have used all temporary tables except starting 22 tables. The Stored procedures we have used nearly 6 temporary tables to handle the business logic. Finally we are loading...
8
7940
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10); SELECT A FROM SESSION.TEMP; works from a query tool.
4
2206
by: Raj | last post by:
Hi all, I have couple of questions some one plzz help 1.I have a query which run for 2 hours on my production machine, it returns 1.5 millon rows, i looked at the explain plan it is picking up the indexes on big table. The query joins 5 tables ( 1 has 30 million records 4 other tables have less than 100000) i see the cost at each step is less than 1000 so i guess it shouldn't take that long to return the result set??? it does a sort...
6
3277
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server table, 200K rows, yearMonth is an indexed long integer the primary key is t_orno, t_pono
29
5502
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where basically almost any change I make to how the query is executed (so that it still performs the same function) causes the performance to jump from a dismal 7 or 8 seconds to instantaneous. It's a very simple query of the form: SELECT Min(MyValue)...
0
8656
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9140
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9005
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8869
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6512
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5848
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4603
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3025
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2304
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.