PRR.DB (8/22/2013)
Thanks for your reply .As per above code(as per functional wise) not possible to insert at single time . It is looping 1.2 millions time and joining with EVALUATION_DETAIL(35 millions) table. All indexes are working fine, if observe execution plan (attached with question) all are Clustered index seek and Index seek. Even though it is talking 24 hrs time
If I keep while loop instead of cursor , can i get performance
No a while loop is pretty much the same thing as a cursor. Given that each iteration of your loop you are performing 3 select statements and an insert you are executing somewhere around 140 million queries.
From the code you posted I don't see any reason you have to loop. I agree with Jack that this should be done in batches or the transactions will be huge. Maybe do 10-20k at a time. This does mean that you need a looping mechanism but each iteration of the loop would be thousands of rows, not just one.
_______________________________________________________________
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/