Starting fresh from previous posting now the the issue has been narrowed down.
Uses two different standward Window servers (no VMWare, etc. at play). Started the same DB on both boxes and performed a cache flush on both.
Plans of first query test on each box as follows:
Both seems to run slow. Subseqent repeats of the same query on the boxes revealed a large delta in speed:
The query is run hunderds, if not thousands, of times per day. Query is mature and runs on many other instances on many other servers for our clients. It is only recently that one of our servers started showing the slow performance, presumable because it is using a hash table rather than using the index on table ReseSeats. The full query is here:
More than willing to pay for direct assistance on this matter.
It is as you reported earlier, but I had to see it for myself...
It looks like forcing the index
left outer join ReseSeats rs Force Index (EventNo) on (rs.EventNo=e.Code)
did change the plan, but not the performance; instead of a table scan, the slow query now uses
Index Scan Scan rs using index EventNo (all rows)
whereas the fast query uses a completely different plan, with a different kind of index scan
Index Scan Scan rs using index EventNo
At this point, would you be happy with a different query that (a) returned the same rows, (b) ran well on both boxes, and (c) was created by waving a dead chicken over the keyboard?
If so, I suggest Divide and Conquer: Rip the ReseSeats table out of the original query and create a separate query that pre-selects rows from ReseSeats into a temporary table to replace ReseSeats in the original query.
I won't make any suggestions about WHICH tables and WHICH predicates to copy/move, because YOU understand the data...
Try to ensure that the temporary table is a LOT smaller than ReseSeats, but perfection is not required... the temporary table can be larger than the subset of ReseSeats that is required for the final result.
Some of the other tables will have to appear in both queries. In some cases, it might be possible to MOVE tables to the temporary query, but it probably doesn't matter if they are tiny (and there are a lot of tiny tables).
If the temporary table can be constructed by joins between ReseSeats and these tiny tables, and those joins can efficiently reduce the size of the result, then victory may be the result.
It would also be interesting to see what SQL Anywhere 12 does with this query.
In the end, upgrading the DB from SA-9 to SA-11 resolved the issue. Though I probably should put the effort into creating and posting the plan that SA-11 generated I am just so happy to have this issue behind me (for now) that I am going to spend the next month drinking instead.
Thanks again to all of those that provided guidance!
answered 10 Apr '12, 02:41