Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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! –  Max 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
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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