bharat sethi (12/17/2013)
HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.
Please advice.
I assume that you didn't bother to read the article I suggested. Ask yourself if you honestly think you would be able to provide anything resembling solid advice based on what you posted. I can't see your screen, I have no idea what your tables and indexes are like, I have no idea what the actual query you are running looks like, I have no idea how many rows are in the tables, I have no idea if your stats are current, to summarize, I have no idea how to help you.
At the absolute bare minimum you need to post an actual execution plan. Including ddl for the tables and indexes would be a big help too.
From your brief description of what you are doing I would ask why you need to copy rows from a table into a temp table to simply then select those rows back out. But without more details about what you are actually doing I can't really guess on that either.
_______________________________________________________________
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/