CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
Jainendra
Jainendra

Reputation: 25143

Stored procedure executing select statement indefinitely

I have one huge stored procedure which collects data from tables into 3 temp tables (table variables). 

@table1: 50,000 records
@table2: 23,000 records
@table3: 15,000 records

After preparing the data, the stored procedure executes one huge select statement (180 lines) which converts data from these temp tables and some physical tables to XML format and returns to the client.

I can't post the stored procedure here because of the confidentiality of the project. The stored procedure was getting stuck at this select statement. Even after running the stored procedure for 24 hours it didn't complete execution.

Then I replaced all table variables with local temporary table (#table1, #table2, #table3). To my surprise the stored procedure executed successfully with the same data.

I am not able to understand the difference b/w two approaches; and why the stored procedure was executing indefinitely with table variables?

Upvotes: 3

Views: 211

Answers (1)

marc_s
marc_s

Reputation: 754488

Table variables are a bit tricky because

  1. they don't have statistics associated with them
  2. the SQL Server query optimizer - due to lack of statistics on those table variables - always assumes they contain only one row.

These "shortcomings" can lead the query optimizer astray - in very bad ways, it seems! Assuming one row can lead to very inefficient execution plans, if you really have significantly more rows in those table variables.

If you use 5 or 10 rows - no biggie - but in your case, you're using tens of thousands of rows, which is significantly different than one row.

So in such a case, I would always recommend using "proper" temp tables instead of table variables.

Upvotes: 2

Related Questions