>>>>>This monstrous query is very hard to read and impossible to understand. Can you break it to managable pieces using either CTE or temp tables?
>>>>
>>>>Hi Naomi - as I said my TSql is very limited - don't know what CTE is and how would temp tables help ? - thanks
>>>
>>>Hi Pete
>>>
>>>if I was attempting to improve the performance of the query I'd first remove all the sub queries in it. See how the basic query performs and how it can be improved . then run the sub queries on their own and do the same for them then put it all back together.
>>
>>Hi Nicholas, if I remove the sub queries the performance improves dramatically but I don't know of a way to bolt the sub queries back on in an efficient manner ?
>
>Try running each subquery on its own to check its performance, for example below replace dbo.Claim.ClaimRef with a valid claimref and run the query and check its performance. Do that for all of them and it'll help you find whats taking most time.
>
>select count(block)
>from dbo.multiblk
>Where active = 1
>and clm_ref = dbo.Claim.ClaimRef

Thanks