Query hangs on table variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Query hangs on table variable

Query hangs on table variable

Rate Topic

Display Mode

Topic Options

Author

Message

msheeha2

Posted Wednesday, March 06, 2013 10:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:18 AM
Points: 5, Visits: 49

I have a query that consistently runs fast and returns the expected output, but when I try to insert the output into a table variable it just runs indefinitely, and I can't find the problem in the code. I can't really post the query or the output (this is a data mining task with healthcare data and there are strict privacy rules) but I will describe the situation as best as I can.

The query that runs fine takes these steps:
Declares datetime variables for StartDate and EndDate
Declares a table variable (an ID to ID crosswalk) with 2 fields and inserts 691,969 records with a select query
Declares another table variable (simple list of codes) with 1 field and inserts 465 records directly
Finally, there is a union select that pulls 3 fields each from 3 different tables, each inner joined to the crosswalk table variable and where Date is between StartDate and EndDate and the code is in the code list table variable.

This query returns 53,463 records in about 50 seconds.

When I try to insert this output into another table variable, it doesn't throw an error, it just runs - I have let it go over 26 hours before just to see what would happen - it just keeps executing...

Am I pushing my luck with all the table variables? I'm stumped.

Post #1427763

Lynn Pettis

Posted Wednesday, March 06, 2013 10:12 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 21,610, Visits: 27,441

Here is my suggestion. Drop the table variables in favor of properly indexed temporary tables. Let us know how it works.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1427764

opc.three

Posted Wednesday, March 06, 2013 11:17 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 6,714, Visits: 11,751

I'll second that. I almost exclusively favor traditional temporary tables over the use of table variables. Performance is one reason, which you may be experiencing first hand, and ease of use when debugging is another major one. To be fair, there are a couple scenarios where I will use a table variable over a temporary table but they are corner-cases.

Here is a great article comparing and contrasting the two classes of object:

Comparing Table Variables with Temporary Tables By Wayne Sheffield

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1427780

wolfkillj

Posted Friday, March 08, 2013 10:10 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 721, Visits: 1,375

opc.three (3/6/2013)

The key part of the article @opc.three cited is this, I think:

1) SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan.

You have a table variable that has over 600K rows. SQL Server constructs an execution plan for your query based on an estimation of 1 row in that table variable. Operations that are efficient for 1 row may be extremely inefficient for 600K rows. Eliminating the table variables would probably shave a significant amount of time off your 50-second query response, not to mention the effect on the variant with the final INSERT into another table variable.

I would venture a guess that adding the final INSERT to another table variable caused SQL Server to make that one additional "bad" choice about the execution plan ("bad" only because it is based on an inaccurate estimate of 1 row) that tips the whole thing over into "forever" response times. Please post a reply to let us know how you resolve the issue - it's nice to see our suggestions work for someone, and even when they don't, we may learn something, too!

Post #1428681

msheeha2

Posted Friday, March 08, 2013 1:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:18 AM
Points: 5, Visits: 49

That was an excellent article - I did some research a while ago about the best way to approach this project, but I don't remember seeing the "one row assumption" difference in execution plans explained so well.
So I have gone through and altered the query to use temporary tables instead of table variables - not only does the query actually complete, it completes in 18 seconds! Thank you so much, everyone!

I'm off to re-think several other queries now...

Post #1428765

« Prev Topic | Next Topic »

Permissions