SQL QUERY TUNING

desiz db2 consultant

SQL QUERY TUNING
Here is a small question which I guess , very well can be answered by the
experts. It is not coming to my mind at this momemt. I could see only the
CPU gian and would appreaciate if i get a clear picture how I can gain
I/O.

Select a.c1,ac2,a.c3,a.c5,b.c1,b.c5,b.c6,c.c1,c.7

from a,b,c

where a.c1=100

and b.c1=a.c1

and b.c2=a.c2

and b.c3=a.c3

and a.c7 not in ('xxx','yyyyy','zzzz');

I could see the access path as first a is selected and getting joined to
nested loop to table b. Then the result set is joined with nested loop to
table c. Table a is getting 3000 odd rows and getting selectd to table b
with and with matching index to three columns c1,c2,c3. The index
containg the column c1,c2,c3 of table is unique and so i guess the same
number of rows from table a as well table b are joined in nested loop.
Then these resultset is joiend to table c with nested loop but the access
type is R. So i guess 3000 odd rows as a result of table a and b are
joined to all the records of table c with table space scan. So there
would be 3000 odd time sequential scan to table c. Correct me if I am
wrong here.

So what i did added another predicate which is not in the query like
c.a1=c.a1.

Now the access path is table C is selected with acess type R and then
nested loop to table a and nested loop to table b. I get the same result
set. Now i feel there would be one scan table C and it would join to
table A with 3000 odd records as per the prediactes and then again 3000
odd records for table B. So this way i could avoid the scan fo table C
3000 odd times and saving CPU. But can any one points to me how i could
save I/O for this. In the package i could see there are simialr other
queries which is in the same pattern. The program it self goes for lot of
I/O wait. So my question is if i rewrite the query the way i tried how it
would gain me in CPU ,I/O and I/O ait time.

Thanking you all ini advance.

Desig

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: SQL QUERY TUNING
(in response to desiz db2 consultant)
The first thing I notice is that there is no predicate on table c. Or
have you mis-re-coded your original query - was it supposed to be

select ... c.c7
from ...
where ...
and c.c7 not in ...

But even here there is no predicate to be the basis for a join
between (a,b) and c - so each row coming out of the join between a
and b will be joined to all rows in c that satisfy the "not in"
predicate. This is a form of cartesian product and generally shows
a misunderstood requirement.

Depending on the size of table c and your bufferpools, rewriting the
query might not be decreasing IO times. The first time the
tablespace scan for table c is done it is fairly efficient (lots of pages
brought in in a single IO), and then all the pages are in the
bufferpool. The second time there is no IO because (hopefully) the
pages are still in the bufferpool.

What you need to do is
- question why this query is fetching data from un-related tables
- determine whether there are more rows that satisfy the
appropriate predicates from the (a,b) join or from table c. The one
which returns the least rows should probably be the outer table (so
there are fewer searches of the inner table)
- an index on c.c7 might be usefull. But I'm not sure.
- a lot of statistics would be necessary to analyse this.

James Campbell

On 18 Dec 2005 at 2:15, desiz db2 consultant wrote:

>
> Here is a small question which I guess , very well can be answered by the experts. It is not
> coming to my mind at this momemt. I could see only the CPU gian and would appreaciate if i get
> a clear picture how I can gain I/O.
>
> Select a.c1,ac2,a.c3,a.c5,b.c1,b.c5,b.c6,c.c1,c.7
> from a,b,c
> where a.c1=100
> and b.c1=a.c1
> and b.c2=a.c2
> and b.c3=a.c3
> and a.c7 not in ('xxx','yyyyy','zzzz');
>
> I could see the access path as first a is selected and getting joined to nested loop to table b.
> Then the result set is joined with nested loop to table c. Table a is getting 3000 odd rows and
> getting selectd to table b with and with matching index to three columns c1,c2,c3. The index
> containg the column c1,c2,c3 of table is unique and so i guess the same number of rows from
> table a as well table b are joined in nested loop. Then these resultset is joiend to table c with
> nested loop but the access type is R. So i guess 3000 odd rows as a result of table a and b are
> joined to all the records of table c with table space scan. So there would be 3000 odd time
> sequential scan to table c. Correct me if I am wrong here.
> So what i did added another predicate which is not in the query like c.a1=c.a1.
> Now the access path is table C is selected with acess type R and then nested loop to table a and
> nested loop to table b. I get the same result set. Now i feel there would be one scan table C and
> it would join to tableA with 3000 odd records as per the prediactes and then again 3000 odd
> records for table B. So this way i could avoid the scan fo table C 3000 odd times and saving
> CPU. But can any one points to me how i could save I/O for this. In the package i could see there
> are simialr other queries which is in the same pattern. The program it self goes for lot of I/O wait.
> So my question is if i rewrite the query the way i tried how it would gain me in CPU ,I/O and I/O
> ait time.
>
> Thanking you all ini advance.
> Desig
>
>
> --
> ___________________________________________________
> Play 100s of games for FREE! http://games.mail.com/
> --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list.
> To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm