Jeff Moden (4/16/2013)
sqlfriends (4/16/2013)
Now we change the view to hopefully speed it up....but we have a batch process at night that uses it [font="Arial Black"][highlight=""]for each [/highlight][/font]individual. so overall it makes the process slow.
I believe that changing the view might not do any good. I've highlighted the problem that I think is the real problem above.
I run it in SSMS with Execution plan, I see in :
older version: Sort cost : 58%, [font="Arial Black"][highlight=""]Clustered Index scan[/highlight][/font]: 41%
new version: Sort 53%, [font="Arial Black"][highlight=""]Clustered index scan [/highlight][/font]47%
Perhaps you don't understand that a Clustered Index Scan is really nothing more than a Table Scan. That's what you need to work on first and then you need to work on the "for each" problem.
If you really want help with a performance problem, then read the article at the 2nd lik in my signature below.
We know it is the sort part makes it slower, but it seems we don't have any choice.
For clustered index scan, because this is a view of schema binding, it uses the index , the index is the PK of the underlying table, I don't know why view make it to index scan not index seek