What i can do to reduce sort operation cost

Dimitris 2017-01-19 06:45:21

Rob Farley 2017-01-21 02:11:39
It would be handy to know what was inside ACV_SubscriptionContacts. But anyway…

It's sorting to find the most recent row in AC_Contact, based on (DateTimeInserted desc, ContId). But the Contact must have ContType=4, and have the same InfoID as the Company described in the Subscription.

You're starting with a subscription, finding the company that matches, getting any contacts that happen to share the InfoID and ContType=4, and then needing to find which of those appeared most recently. It's not ideal.

But… if the Contact table had CompID rather than InfoID (and I don't know about your data and whether this is even remotely possible), then an index on (ContType, CompID, DateTimeInserted desc, ContID) would help, because it would be able to pick out that most recent Contact without having to do a sort.

For the lower Sort, that's similar, but a bit trickier because of the extra level added with User + Person.

But I wonder whether you're actually treating Contact correctly. It seems like the Sort ought to be deeper, and be fetching the most recent Contact Information for the Company / Person, rather than for the Subscription. I think you need to look inside your view, and push the TOP a bit closer to the Contact table. Like:

FROM AC_Subscription s
JOIN AC_Company c ON c.CompID = s.CompID
CROSS APPLY (SELECT TOP (1) * 
             FROM AC_Contact con 
             WHERE con.InfoID = c.InfoID 
             AND con.ContType = 4 
             ORDER BY con.DateTimeInserted DESC) con

But I haven't seen inside the view, and am only guessing. But using TOP inside CROSS APPLY is definitely worthwhile when you want to find the most recent Contact Info for a particular thing.