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.
--Jeff Moden
Change is inevitable... Change for the better is not.