Stored Proc with table variables running very slow?

Question

Stored Proc with table variables running very slow?

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Another thing, statistics could be different between the two systems, and this could affect the query plan generated in production compared to UAT.

WayneS SSC Guru Points: 95460 More actions · Answer 2

Jeff Moden (1/29/2010)
I forgot about that and that's very true. Have you ever seen code that does that, though?

Another use is in auditing... if you want to audit what was attempted, regardless of whether it succeeded.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 3

Another good idea. Thanks Wayne. Just curious though... have you ever used it that way?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Rule of Thumb for Normal Stuff: avoid table variables until such time as you can prove that you are having recompilation problems.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 5

The myth about the Optimzer always treating table variables as if they have one row...!!! :w00t:

So ok yes, if you ask for an 'estimated' plan from SSMS you'll see one row. Cool. This can be extremely useful in some plans with poor cardinality estimates...but that's another story.

Everyone always forgets that at execution time, the engine will know how many rows are in the table variable if anything in the query triggers a recompilation, or if OPTION (RECOMPILE) is used.

Try this:

DECLARE @OneRow

TABLE (row_id BIGINT PRIMARY KEY);

INSERT @OneRow (row_id)

SELECT TOP (150000)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM master.sys.columns C1,

master.sys.columns C2;

SELECT COUNT_BIG(*)

FROM @OneRow OR1

JOIN @OneRow OR2

ON OR1.row_id = OR2.row_id

OPTION (RECOMPILE);

That should give a MERGE JOIN plan. If you add a HASH JOIN hint, you should even get a parallel plan 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Thanks for the Myth Busting there, although I will point out the obvious in that you get the 'right' plan with temp tables without the need for the OPTION (RECOMPILE). 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

san43 Hall of Fame Points: 3196 More actions · Answer 7

Thanks for all the suggestions that every one has given me. I have replaced the code for table variables with temp tables. Now it is running fine in prod in around 1min.

Thanks

San

WayneS SSC Guru Points: 95460 More actions · Answer 8

Jeff Moden (2/1/2010)
Another good idea. Thanks Wayne. Just curious though... have you ever used it that way?

No, but I read about it and realized it would work pretty good.... I think I read about it on this site somewhere...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 9

san43 (2/2/2010)
Thanks for all the suggestions that every one has given me. I have replaced the code for table variables with temp tables. Now it is running fine in prod in around 1min.
Thanks
San

Good to hear that it's working better now.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 10

TheSQLGuru (2/2/2010)
Thanks for the Myth Busting there, although I will point out the obvious in that you get the 'right' plan with temp tables without the need for the OPTION (RECOMPILE). 🙂

True, but then 'real' temp tables also use locks and consume log. Horses for courses. There is no one right answer, and a lot of it comes down to personal preference. Except in this case and many like it LOL

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Samson SSC Veteran Points: 216 More actions · Answer 11

My personal rule of thumb has been to never use a table variable if more than 500 rows. This posting by the SQL Server team says never use a table variable if more than 100 rows.

http://www.sqlteam.com/article/temporary-tables

Peter

http://seattleworks.com including my blog The SQL Janitor

Paul White SSC Guru Points: 150467 More actions · Answer 12

Peter Samson (2/4/2010)
My personal rule of thumb has been to never use a table variable if more than 500 rows. This posting by the SQL Server team says never use a table variable if more than 100 rows.
http://www.sqlteam.com/article/temporary-tables

That is not from the SQL Server Team. It is merely a random article, and one which perpetuates the myth that table variables use memory and tempdb any differently from regular temporary tables. Both have pros and cons - complex ones - applying a number-of-rows rule seems overly simplistic.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

I agree with Paul on this one. There is no real set number of rows per query that works better in one than the other. However, there is a tipping point from which you will see improved performance by switching from one to the other.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

The inability to have statistics on table var (except for PK if you have one) can be a killer even if there is just ONE row in the table var. Imagine a table with 1 Billion rows that you are going to join to. The join column has 99.999% of the values as '123456' and the remaining values are all different. Now you take your one row table var and join on that column. If the table var row has '123456' in it that nested loop plan will crush your server. You want a hash/merge there.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 15

TheSQLGuru (2/5/2010)
The inability to have statistics on table var (except for PK if you have one) can be a killer even if there is just ONE row in the table var. Imagine a table with 1 Billion rows that you are going to join to. The join column has 99.999% of the values as '123456' and the remaining values are all different. Now you take your one row table var and join on that column. If the table var row has '123456' in it that nested loop plan will crush your server. You want a hash/merge there.

I wouldn't mind the highly-biased set up if it actually demonstrated your point 😛 😀 😛

Your assumption that the QO would choose a loop join does that component a great injustice. Assuming distribution statistics are available for the large table, the QO will optimize the query quite nicely on the basis that there are very few distinct values.

In fact, for a simple SELECT * a hash join *is* produced with a table variable! Trying an aggregate over the values produces a partial aggregate and a hash join! Both run in milliseconds. If you don't want to take my word for it, run the code...

USE tempdb;

GO

CREATE TABLE dbo.Data

(

row_id INTEGER IDENTITY PRIMARY KEY,

value INTEGER NOT NULL,

);

INSERT dbo.Data WITH (TABLOCK)

(value)

SELECT TOP (750000)

value = 123456

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3;

INSERT dbo.Data

(value)

VALUES (95);

DECLARE @OneRow

TABLE (

row_id INTEGER IDENTITY PRIMARY KEY,

value INTEGER NOT NULL

);

INSERT @OneRow VALUES (123456);

-- Also try MAX(D.value), AVG(D.value) and so on instead of the SELECT star

SELECT *

FROM @OneRow R

JOIN dbo.Data D

ON D.value = R.value;

DROP TABLE dbo.Data;

Once again with feeling: the choice between table variables and temporary tables involves some complex considerations. Rules of thumb generally suck (quite pleased with the imagery there!)

Cheers

Paul

P.S. Answers on a postcard for the business need behind the join described, with a SELECT * output 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi