cancel
Showing results for 
Search instead for 
Did you mean: 

Having table in ML publication kills update performance

Former Member
3,383

This is under SQL Anywhere 8.0.3.5594 on Windows Server 2003. I have been trying to resolve an issue of poor performance when running a series of updates on a single table. Testing with a copy of the customer's database, I found with repeated tests that making no change other than removing the table from the Mobilink publication reduced the time for updating 800 records from over 7 minutes down to 2 seconds. This was very unexpected for me. Can anyone explain why this might be and how it might be resolved (the table can't be dropped from the publication on the customer system)?

Notes: No Mobilink client was running in my test setup. The database service was configured to restart the transaction log at each checkpoint (-m). The table had a somwhat complex WHERE clause in the publication.

Thanks for any ideas on this!

Accepted Solutions (1)

Accepted Solutions (1)

jeff_albion
Product and Topic Expert
Product and Topic Expert

A publication's WHERE clause is evaluated at INSERT/UPDATE/DELETE time, in order to mark the records in or out of the publication scope in the transaction log for synchronization or replication. The overhead of doing this record keeping is most likely the difference in the INSERT times you're seeing.

Former Member
0 Kudos

Thanks Jeff, I guess that explains it.

VolkerBarth
Contributor
0 Kudos

With SQL Remote, DBTRAN -sr would show the addional record-keeping for publications. I don't know whether the same is true for MobiLink clients...

Answers (1)

Answers (1)

Breck_Carter
Participant

So... it's turning out to be a standard "Query From Hell" problem 🙂

Try running a SELECT * FROM WHERE [clause from publication] to make sure it really is slow.

If so, try obtaining a Graphical Plan With Statistics for the SELECT * FROM WHERE [clause from publication].

Scroll down in that article to see the Version 8 instructions.

Save the plan as a *.xml file and post it here.

Former Member

You might also want to get the plan as Breck has suggested above but when running an UPDATE statement, as that is the problematic statement.