This is under SQL Anywhere 22.214.171.12494 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!
asked 17 Jul '13, 16:44
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.
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.