on 2013 Jul 17 5:44 PM
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!
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.