Stored procedure executing select statement indefinitely
I have one huge stored procedure that collects data from tables into 3 temporary 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 rows) that converts the data from these temporary tables and some physical tables to XML and returns to the client.
I am unable to post the stored procedure here due to project confidentiality. The stored procedure is stuck with this expression select
. Even after running the stored procedure within 24 hours, it did not complete execution.
Then I replaced all the table variables with a local temporary table ( #table1, #table2, #table3
). To my surprise, the stored procedure succeeded with the same data.
I can't figure out the difference b / w two approaches; and why did the stored procedure run indefinitely with table variables
?
source to share
Table variables are a little tricky because
- they have no statistics associated with them
- The SQL Server Query Optimizer — due to the lack of statistics on these table variables — always assumes that they contain only one row .
These "flaws" can lead the Query Optimizer to go astray - in a very bad way, it seems! Assuming a single row can lead to very inefficient execution plans if you do have significantly more rows in those table variables.
If you are using 5 or 10 lines - not a biggie, but in your case you are using tens of thousands of lines that are significantly different from one line.
Therefore, in such a case, I would always recommend using "correct" temporary tables instead of table variables.
source to share