Hey there,

I'm currently building a query in DB2 that ultimately will retrieve a bunch of data limited to a date range. The year is split into 12 periods and I always want the data limited to the previous period to the current date.

If I write the query so that it can dynamically determine this date range it seems to execute this portion of the code after it has retrieved all the data, filtering it afterwards. This kills the performance of the query.

Whereas if I take the same query and just hard code the date range then the query takes a fraction of the time to complete.

What I'd like to know, is there any way to tell DB2 to complete a sub select or method called within another query first and then proceed with the main query? Or if anyone can suggest a different approach to getting the same results I'd really appreciate your help.



TIA,

Brian

I don't think anyone here will be able to suggest anything meaningful without your describing the problem in more detail. At the very minimum you will need to provide the query, table and index definitions, and the query execution plans in both cases.

I don't think the table structure or indexing has anything to do with it. It's more how DB2 handles the query. Below are two examples of basically the same query.

The first uses a method to derive the date I need. Using this method blows the overall cost through the roof and will make my query run for hours. The method itself is not costly on it's own.

select *
from mytable t
where t.period_ending_date = get_period_date('PREV', CURRENT DATE, 'END')


Where as in this query I’ve hard coded the date to the same value the method would return and the cost of the query and the time it takes to run is a fraction of the previous query.

select *
from mytable t
where t.period_ending_date = '7/29/2007'


From what I've been told the first method takes so long because it is retrieving all the data from mytable and then filtering it afterwards. While the second query is only bringing back the data I need based on the date value.

Any help would be greatly appreciated.

Brian

Since your have performance problems, the only way to help you is if you provide all the requested information. In particular, you also have to tell us:

• how the GET_PERIOD_DATE function is defined,
• how DB2 is configured (buffer pools etc.),
• if and when you collected statistics on the table,
• what the exact execution plan is for the queries.

The thing is that the DB2 optimizer tries to take all relevant system and DB2 configurations as well as statistics etc. into account to come up with the (hopefully) best access plan.

My (wild) guess is that your function is created NOT DETERMINISTIC and/or with EXTERNAL ACTION. Thus, DB2 must call the function for each row in the table, resulting in a slow table scan. The 2nd query w/o function call can be much faster if there is an index on PERIOD_ENDING_DATE, for example.

p.s: The whole idea of an RDBMS is that you do not tell the system how to retrieve the data - you only tell it which data it should return and the system does the rest.

Try putting the method into a non-correlated sub-query. That should retrieve the date once and you might get a better access path.

Here's another option that might provide some relief. Create a declared temp table and insert one row with the date, and then join to the temp table for the date.