Skip to Content
0
Former Member
Nov 21, 2016 at 01:50 PM

Leverage Statement Cache for queries with large IN clause - ASE

108 Views Last edit Nov 21, 2016 at 01:51 PM 3 rev

We have a system that execute small number of generic queries against ASE, in response to on-demand queries. In general, the statement looks like:

SELECT ... FROM ... JOIN ... JOIN ... JOIN ... JOIN ...

WHERE P1 = ? and P2 = ? and P3 = ?

AND P4 in ( L1, L2, L3, L4, L5, L6, ...)

The Quesion:

Is there a way to tune the optimizer to consider that all those queries, regardless of the number of parameters in the IN-list should use the same entry in the statement cache ?

Details:

The value lists are generated on the fly, based on user input. It's size vary between 100 and 1000 items.

To speed up performance, we turned on statement cache and autoparam. This works wonders for repeated calls: First call took about 0.3 seconds (most of the time is for the Parse and Compile). Follow-up call take 0.05 seconds (just execution from a cached plan).

We noticed that Sybase creates a distinct entry in the cache (and spend the time to optimize it) for each distinct number of variables in the IN list - we get one entry for 4 parameters, one entry for 5 parameters, one for 6 parameters

Two problems:

* Since the size of the IN clause vary, it takes a lot of 'first time' calls to get all the combinations added to the statement cache.

* The cache size need to be very large to keep all those query plans, with the IN list going up all the way to 1000 items (Actual list is bigger, and is split into 1000 item pieces).

Thanks a lot!

Yair