February 5, 2004 at 1:01 am
Hi!
I had a large stored procedure, which ran for about 3 minutes and had 32 recompiles. To reduce this time and lower the number of recompiles, I moved to the top all "create #temp_table..." statements and got 26 recompiles, but the execution time went up to indefinite...
Am I doing wrong things to optimize my procedures?
Thanks.
February 5, 2004 at 8:16 am
Maybe this article will help you
http://www.sqlservercentral.com/columnists/RDyess/ospr.asp
Because of the formatting it is somehow difficult to read.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 1:51 am
2Frank Kalis:
The article tells:
--------------------------------------------------------------------------------
"By placing all of your temporary table creation statements together,
SQL Server can create plans for those temporary tables when one of them
is referenced for the first time. "
--------------------------------------------------------------------------------
So I am doing right things by placing 'create #temp_table' statements at
the top of the procedure. What is the reason for such a long execution time?
February 6, 2004 at 2:32 am
Hard to tell without seeing the code and knowing your environment.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 2:35 am
Umpf... too quick.
Here's another one by Randy Dyess
http://www.transactsql.com/Articles/temptabl.html
Interesting is also the link list he provided
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 6:12 pm
As far as I know using # and ## tables will force your proc to always recompile. If you have to use a temp table internal to the proc (for performance reasons or something) then try using an @ table variable. If you need to data outside of the proc then move the creation of the temporary table outside of the proc and then do an Insert...Exec to populate it.
cl
Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply