2

I am running the following stored procedure to add a merge replication join filter,

sp_addmergefilter

What else does that stored procedure do behind the scenes?

I am calling that stored procedure and in one case it is timing out after 10 minutes. The join filter is this for the two tables DispatchLink and Dispatch,

Dispatch.DispatchId = DispatchLink.DispatchLink1 and Dispatch.ContractId = DispatchLink.ContractLink1
or Dispatch.DispatchId = DispatchLink.DispatchLink2 and Dispatch.ContractId = DispatchLink.ContractLink2

The structure for those two tables is explained here,

How to Optimise Query

If I remove all the stuff after the 'or' it runs fast.

0

1 Answer 1

0

It's really interesting what I found. I start SSMS as an administrator, and chose the option to debug the stored procedure. I found it was doing this,

declare @test int
select @test=1 from [dbo].[DispatchLink] , [dbo].[Dispatch] [Dispatch]
where (DispatchLink.ContractLink1 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)
or (DispatchLink.ContractLink2 = Dispatch.ContractId and DispatchLink.DispatchLink2 = Dispatch.DispatchId)

And it seems that the reason it was doing that was to test whether the join clause was feasible, i.e. whether it had syntax errors etc.

The resulting query plan is the problem, and can be see here,

Analysing A Query Plan

Adding WITH (FORCESEEK) makes the query instant, but of course I cannot do that because that is just what sp_addmergefilter is doing internally. The query optimiser is making an incorrect choice for some reason.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.