Hello,

I wrote an application which gets information from an DB2 (readonly access). The user can specify parameters on the UI and gets the informations. Therefor I have to do some requests with IN clauses, sometimes with about 1,000 elements (specified by the application). The performance of this requests is slow (execution time: about 75 seconds). Is there any way to speedup the request? What's about the idea of starting 1,000 'simple' requests?

Shall I avoid IN clauses? If yes what's the alternative?

pkoeppe

insure that the elements of the IN-LIST are sorted and duplicates are removed.

is this dynamic sql or inbedded?

Duplicates are eleminated. How shall I sort string values ascending or descending?

It's a parametrized request.

Craig Mullins says ascending-
is the column in an index?

I'm almost sure DB2 will do that more efficiently; besides, it won't know the list is sorted and unique and will attempt to sort it anyway.

Typically, any attempt to tune an SQL statement begins with analyzing its explain plan.

Since the author chose not to disclose the plan, the statement itself, or even the DB2 version, it is difficult to give more specific advice.

true

true

possibly, but if db2 only has to do one pass against the IN-LIST to determine sort and duplicates, there is less time spent by db2.

But db2 will only sort and remove duplicates if the column is indexed; otherwise db2 will not sort and remove duplicates.

and, leaving all the work to db2 is not always a good thing for the machine;
but that requires one to think about other than just his sql.

Even if there are duplicates and the list is not sorted, only a "single pass" (whatever that shall mean exactly) is needed. DB2 just sorts the list and removes duplicates as part of that if it finds any.

You are sure about that? Because if IN-lists are sorted, DB2 may be able to convert it to a range predicate (if such an optimization is done). Also, probing each value into the IN-list can be done with binary search instead of touching every element in the list if the list is sorted. That's completely independent of the existence of an index, of course.


I think the only advantage of removing duplicates in large IN-lists is that the SQL statement becomes shorter and less data needs to be transferred over the wire to the DB2 server, and parsing the SQL statement is slightly faster. But then, you have to have a rreally large IN-list and a lot of duplicates. Another minor possibility could be that the DB2-internal sort algorithm is faster for pre-sorted lists (e.g. like qsort, which has a complexity of O(n^2) worst case but is generally faster than O(n^log(n))). Whether such differences are measurable, is up to debate - I rather doubt that you will see differences in real-world scenarios.

sorry about the language problem. db2 can sort a 'sorted' list, faster than an 'unsorted list'.

as far as the indexing comment is concerned, I can only quote Craig Mullins' advice and info.
I assume he does not lie in his publications and have found his info to be correct in the past.

and you have to keep my prospective in mind. I run in real-world situations which are not beefed-up with special hardware to perform BI and Warehousing DB2 functions.
Just run on z/OS machines that have to allocate the cpu between different application needs - program utilization as well as service calls - db2, cics, mqs etc.

if I have 10,000 requests to db2, I want to make them as streamlined as possible.
a nano-second here, a nano-second there ....

I believe that Craig Mullins works/writes mostly in the z/OS arena, and you mentioned z/OS above. The OP poster did not mention which OS he is working on for the DB2 server, but about 95% of the questions in this forum are for LUW.

I do believe that a large "IN" list has been a performance problem in the past for DB2 LUW, but not sure about the latest release (9.7).

Put all values from the IN to some (temporary) table and replace IN by JOIN, using "="....

Lenny

Thanks for your replies until now.

The database version is DB2/6000 8.2 and isn't administrated by myself. Also I only have readonly access to a specific tablespace. How can I create a temporary table in this scenario? And how can I see the explain plan?

@dbzTHEdinosaur: the column used in the IN clause is in an index.

pkoeppe

Temporary tables are created in temporary tablespaces. Check out DECLARE GLOBAL TEMPORARY TABLE in the manual.


If you have DB2 GUI tools installed, you could use Visual Explain. If not, the simplest way would be to run the db2expln command line utility.

Also you can divide you IN clause on few INs.
Example:

<=>
Also you can use UNION ALL instead of OR in same way.

Lenny

Also, instead of IN you can use = ANY, or = SOME, or EXISTS.
It could also increase performance of the query....

Lenny