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


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.

  • Go to the database in question in SSMS, expand the Programmability\Stored Procedures\System Stored Procedures folder, and find sys.sp_addmergefilter. Right-click, and choose 'Modify' from the menu. This will allow you to see the source code of the stored procedure... perhaps you can determine what it is doing with your join filter! – Hannah Vernon Sep 6 '12 at 1:56
  • hi there , try to get all information about this sp_help 'sp_addmergefilter' sp_helptext 'sp_addmergefilter' – user9399 Sep 8 '12 at 15:59

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, privacy policy and cookie policy

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