SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Execution Plan and temporary tables
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Desmag
Starting Member

United Kingdom
18 Posts

Posted - 09/02/2006 :  09:49:16  Show Profile
Hi,

I'm trying to run Execution Plan on one of my stored procedures and this is an error I'm getting:

Server: Msg 208, Level 16, State 1, Procedure Forum_thread_replies_paged, Line 27
Invalid object name '#TempItems'.


#TempItems is a temporary table that I'm using in my sp. Does Execution Plan have a general problem with temp tables or is there something wrong in my t-sql?
PS. Stored procedure executes without errors.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/02/2006 :  09:54:58  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
The temporary table which is causing problem is created in the current SP only, or is it a part of calling hierarchy where temp table is created in parent SP and child SP processes it?

By execution plan, do you mean Estimated Execution Plan?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

Desmag
Starting Member

United Kingdom
18 Posts

Posted - 09/02/2006 :  10:12:40  Show Profile
Thanks for reply!

The temporary table is in current SP only, and I ment Estimated Execution Plan.
Go to Top of Page

Desmag
Starting Member

United Kingdom
18 Posts

Posted - 09/02/2006 :  12:07:21  Show Profile
What I'm really trying to do is to compare performance of 2 stored procedures. It's just one of them is failing when I run Estimated Execution Plan.

Are there other ways of comparing performances of stored procedures?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/02/2006 :  12:20:26  Show Profile
Yes, temp tables created inside the SP will cause problems when trying to examine the execution plan of the SP.

>>Are there other ways of comparing performances of stored procedures?
You can examine the actual or estimated execution plan of individual statements from your SP outside the context of the stored procedure.
You can "benchmark" the two SPs. Time them to compare the which performs quicker.
You can temporarily add logging and/or print statements including the current time, rowcounts, parameter values, etc. to time specific sections of your code


Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000