My immediate reaction was "Interesting, but where's the hint as to how to avoid/mitigate the problem?". I have a real-world case that suffers from poor performance and this has pointed me back to an area which I already knew caused a problem, but hadn't had a chance to focus on. I look forward to seeing your later articles.

In case you're interested, a simplified explanation of problem is invoices that get queries raised. If there's no query, the status goes straight from 'F' (Finance) to 'C' (Complete)' If there's a query, status goes from 'F' to 'P' (Client - I don't know the history of why it's 'P'), then later back to 'F'. Once back at 'F' it may go to 'C' or 'P' again. The data extracted from the business system simply has a list of invoice numbers, dates/times and statuses (plus other info not relevant to this problem). I need to get the turnaround time for query responses, i.e. the difference in date/time for each invoice with a 'P'->'F' status change. Historicaly, other status changes have been tracked, so we haven't preselected on particular statuses and the current view has at its core has a query similar to:

select

a.invoice_id,

a.status as 'stA',

a.stdate as 'dtA',

b.status as 'stB',

min(b.stdate) as 'dtB'

from

workflow a

join workflow b

on a.invoice_id = b.invoice_id

and a.stdate < b.stdate

and a.status <> b.status

group by

a.invoice_id,

a.status as 'statusA',

a.stdate as 'stdateA',

b.status as statusB

I already knew from the execution plan that this was a killer for the performance of this query. Now I know I need to revisit it.

Any suggestions wlecome! 🙂