bharat sethi (12/17/2013)
Hi!This SP has
an initial query to load data in temp. table with 5 tables joins. This query executed within 15 seconds.
Then have 7 update queries to update diff. diff. columns of this Temp. table from diff. diff. tables.
Each of these updates executed within 5-10 seconds.
Then we have the SIMPLE SELECT statement to return all the rows (207958) of this TEMP. table.
Temp. table don't have Indexes. That's why tried to add Identity column and Non-Clustered Index on it and
in execution plan of this SIMPLE SELECT statement SQL is doing TABLE SCAN.
Will attach the snap of Execution Plan soon.
Of course it is a table scan. You are selecting all the rows from it. Remember that you are first writing and retrieving 207958 rows of data. Most of the time being spent here is most likely disc I/O.
Temp. table don't have Indexes.
You could add them but I don't think it will help in this case because you are selecting all the rows so an index isn't going to help here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/