Sorry Kevin, I've deleted those txt's and added the sqlplan files now

I attached the two exec plans (Query1 and 2) as txt files as the UI wouldn't allow me to attach XML files

If I've done that wrong let me know.

This is...

Thanks for that Chris, CROSS APPLY is a lot neater in this case alright

I just looked at your very first query in that article and saw the first inner join and shouted a four letter word in my head 🙂 Sometimes you can...

That sounds about right to me Grant, the join on this subquery are on some very non specific values - NAVDate and QID. This sub query is basically a catch...

The Temp table is a lookup table for each record that I'm applying the update for instead of using a function call (which returns the Ret value) within the main...

So the original DB owners, in their infinite wisdom, are using varchar(10)'s to store DATE values

For testing purposes, I just changed the date field on my temp table to a...

Exec plan attached

I'm wondering if this might be combination of using the <= operator in the inner join along with using a temp table which might be throwing the stats...

Yeah tried that along with rebuilding and reorganzing the indexes.

The index used does cover the query and performs a seek not a scan

Just want to clarify something:

I was looking at a previous post:

http://www.sqlservercentral.com/Forums/Topic415829-338-4.aspx

and I'm thinking that the way in which this query is coded is the most likely answer for...

Thanks Eric, I'll try that the next time the problem arises

Unfortunately I can't reproduce it all the time to test this, but if I can get it running slowly again...

I don't fully understand MAXDOP but I do vaguely remember an issue in a previous job where the MAXDOP was the cause of a similar issue.

I don't know the processor...

Hi Phil

No blocking on the db, from what I've compared on an Index Seek (looking at the operation with the highest cost 88%), the numbers look more or less the...

You're correct Gail (I'd say you get that a lot :-)), after I reviewed the code compared to the stuff I posted, I didn't state the select in the same...

Hi Gail

The select in use is a select of a view of the table in question using different columns in the where clause - the view holds the where clause...