SA-9 Using Hash Table; Degrading Performance

[Former Member]

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:

http://www.activitylink.net/downloads/SQLHelp/SlowBox_FullQuery_03_10_12.xml http://www.activitylink.net/downloads/SQLHelp/FastBox_FullQuery_03_10_12.xml

Both seems to run slow. Subseqent repeats of the same query on the boxes revealed a large delta in speed:

http://www.activitylink.net/downloads/SQLHelp/SlowBox_FullQuery_03_10_12_AfterSeveralRuns.xml http://www.activitylink.net/downloads/SQLHelp/FastBox_FullQuery_03_10_12_AfterSeveralRuns.xml

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:

http://www.activitylink.net/downloads/SQLHelp/FullEventQuery.txt

More than willing to pay for direct assistance on this matter.

TIA, Kumar

[jeff_albion]

More than willing to pay for direct assistance on this matter.

See: http://www.sybase.com/contactus/support/

[Breck_Carter]

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.

[Former Member]

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?

Yea, pretty much getting there. Just odd that this MATURE query just recently started having problems. I will try on SA-12 first, as upgrading the DB is going to be easier than re-writting the SQL (I think)...if it works. Appreciate your advice.

Sure do just wish that SA supported something that said "regardless of what you think is the best way to gather this data, I want you to do it the way I tell you to". 😉

[Former Member]

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!

-k-