How can I optimise Zumero sync queries

Stephen James Hand

I am currently experiencing very long sync times on a zumero synced database (well over a minute), and following some profiling, the culprit appears to be a particular query that is taking 20+ seconds (suitably anonymised):

WITH relevant_rvs AS
(
  SELECT rv.z_rv AS rv FROM zumero."mydb_089eb7ec0e2e4772ba0dde90170ee368_mysynceddb$z$rv$271340031" rv
  WHERE (rv.txid<=913960)
  AND NOT EXISTS (SELECT 1 FROM zumero."mydb_089eb7ec0e2e4772ba0dde90170ee368_mysynceddb$z$dd$271340031" dd WHERE dd.rv=rv.z_rv AND (dd.txid<=913960))
)
INSERT INTO #final_included_271340031_e021cfbe1c97213dd5adbacd667c08439fb8c6 (z_rv)
SELECT z$this.z_rv
 FROM zumero."mydb_089eb7ec0e2e4772ba0dde90170ee368_mysynceddb$z$271340031" z$this
  WHERE (z$this.z_rv IN (SELECT rv FROM relevant_rvs))
  AND MyID = (MyID = XXX AND MyOtherField=XXX)
UNION SELECT z$this.z_rv
 FROM zumero."mydb_089eb7ec0e2e4772ba0dde90170ee368_mysynceddb$z$old$271340031" z$this
  WHERE (z$this.z_rv IN (SELECT rv FROM relevant_rvs))
  AND (MyID = XXX AND MyOtherField=XXX)

I have taken the latter SELECT part of the query and ran it in isolation, which reproduces the same poor performance. Interestingly the execution plan recommends an index be applied, but I'm reluctant to go changing the schema of zumero generated tables, is adding indexes to these tables something that can be attempted safely and is it likely to help?

The source tables have 100,000ish records in them and the filter results in each client syncing 100-1000ish records, so not trivial data volumes but levels I would not expect to be causing major issues in terms of query performance.

Does anyone have any experience optimising Zumero sync performance server side? Do any indexes on source tables propagate to these tables? they don't appear to in this case.

j.r

Creating a custom index on the z$old table should be safe. I do hope it helps boost your query performance! (And it would be great to see a comment letting us know if it does or not.)

I believe the only issue such an index may cause would be that it could block certain schema changes on the host table. For example, if you tried to DROP the [MyOtherField] column from the host table, the Zumero triggers would attempt to drop the same column from the z$old table as well, and the transaction would fail with an error (which might be a bit surprising, since the index is not on the table being directly acted on).

Another thing to consider: It might also help to give this new index a name that will be recognized/helpful if it ever appears in an error message. Then (as always) feel free to contact [email protected] with any further questions or issues if they come up.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How can I optimise this css code for not writing same css code several times in html?

How can I optimise this mysql query that includes a where clause with an epoch time range?

How can I optimise a Neo4j MERGE query on a node with many relationships?

How to optimise a large table in MySQL, when can I benefit from partitioning?

How can I optimise Range Set and .Value

How can I optimise sql query

How can i optimise these loops

How can I optimise my recursive Lisp function

How can I optimise this SQL query to delete every second duplicate row?

How can I optimise this active record query in this filterable, paginated rails API?

How can I optimise the ordinal encoding of a 2D array of strings in Python?

How can I optimise ext4 for reliability?

how to optimise ajax queries to mysql

nginx redirects - how can I optimise my code?

Zumero sync: knowing what tables are changed

How can I optimise array multiplication by constant?

How can I optimise this to run in an efficient manner?

How can I optimise this code?

How can I optimise this method in Ruby using preload, includes, or eager_load?

How can I optimise this Python code?

How can I index these queries?

How can I optimise / replace these 2 nested While loops in Pandas

How can I optimise this Bubble Sort algorithm in MATLAB, or replace it by some other more efficient method?

How can I optimise my Haskell so I don't run out of memory

How can I optimise this code for my Google sheet macro?

how can i optimise following code for producer consumer problem

How can I optimise long if statements and make better code?

Can I optimise this Matlab for loop?

This is a code for finding anagrams in string. How can I optimise further as its giving me TLE error