Hi,

I have a question regarding processing of sql queries in databases. I have implemented a two audit columns approach for change data capture. The result of the following views is same under experimental conditions. However, I am getting strange performance results when i extract data from the following views using cursors.

1. CREATE VIEW V_AUTHOR_UPD AS SELECT A_ID, A_FNAME, A_LNAME FROM AUTHOR WHERE (A_TIMESTAMP_UPD > CDCTS ) AND (A_TIMESTAMP_INS < CDCTS )@

vs

2. CREATE VIEW V_AUTHOR_UPD AS SELECT A_ID, A_FNAME, A_LNAME FROM AUTHOR WHERE A_TIMESTAMP_UPD > CDCTS@

Now the first query is more complex than the second one but is giving results much much faster than the second one.

The expectation was that the second query should have performed much better than the first one as it is less complex.

I have observed a similar behaviour in another scenario where a complex query is giving better results than a simple one.

Any reasonable explaination about the behaviour of sql queries?

Thanks in advance.
Faisal

DB2 may be interpreting the 1st query as one that will have fewer qualifying rows, and therefore DB2 will use an index; whereas the second query will need to read a significant percentage of the table and a table scan (instead of index access) may be faster.

It is possible that DB2 is mistaken in its assumptions and has selected the wrong access path for the second query, but you didn't say how many rows each of the two queries returns.

The access path chosen may depend on the accuracy of the statistics from runstats command and the sequence of the rows (reorg sequence).

Thanks for the reply!

The qualifying rows for both cases is same. And the the point is, for case 1 we need to look at both insert and update timestamps whereas for case 2 only the update timestamp is enough. Consider the following statistics:

Number of rows inserted = 186624 (updates A_TIMESTAMP_INS)
Number of rows updated = 186624 (updates A_TIMESTAMP_UPD)
Rows retrieved for both cases = 186624
number of inserts plus updates is equal to 80% of the total size of data.

Time taken by case 1 : 6 sec
Time taken by case 2: 132 sec

I can provide more details if required.

DB2 has decide the access path (table scan vs using an index) based on the statistics gathered with runstats. If no statistics have ever been gathered, it will use default statistics.

Generally, all other things being equal, DB2 will assume that a GT and LT will return fewer rows than GT alone.

Is there a way that I can disable the query optimizer? ... I dont want DB2 to use Indexes by itself until i create indexes by myself. In a study i have to show the performance gains with indexes which i cant do with db2 doing it automatically.

Thanks in advance ...