poor performance

ashok.cheppali · #1 (**permalink**) 01-18-08, 06:53

Hi,
If any one provide better query for than below query

SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID

FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT

WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00';

ani_dbforum · #2 (**permalink**) 01-18-08, 06:58

use the AND conditions on smaller table first and then on bigger table.

ani_dbforum · #3 (**permalink**) 01-18-08, 07:00

and try to see the JOINs on the below conditions

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

JOINS will improve the performance.

ashok.cheppali · #4 (**permalink**) 01-18-08, 07:05

thanks for your response,

can you send joins for the below statements

Quote:

Originally Posted by ani_dbforum

and try to see the JOINs on the below conditions

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

JOINS will improve the performance.

stolze · #5 (**permalink**) 01-18-08, 07:06

The order of conditions is completely irrelevant. The DB2 optimizer will choose the execution order for predicates as it seems best. Also, it is completely irrelevant for the optimizer whether join conditions are written in the ON clause of the explicit join syntax or in the WHERE clause with implicit joins. (Personally, I prefer the explicit join syntax because it is easier to read and to separate join conditions from other filter predicates.) So you can ignore ani_dbforum's statements right away.

What should be done here is:
(a) make sure that the query is exactly what the application needs and not too much (e.g. is the DISTINCT necessary?)
(b) verify that the necessary indexes exist
(c) ensure that statistics were collected on tables and indexes

You could also run this query as workload through the DB2 design advisor. Maybe there are some other objects like MQTs that could improve the situation.

ashok.cheppali · #6 (**permalink**) 01-18-08, 07:17

thanks for your response, can you send me using JOINS, for the below query

SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT
WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE = '2009-11-25 00:00:00';

-----------------------------------------------------------------------------

Quote:

Originally Posted by stolze

The order of conditions is completely irrelevant. The DB2 optimizer will choose the execution order for predicates as it seems best. Also, it is completely irrelevant for the optimizer whether join conditions are written in the ON clause of the explicit join syntax or in the WHERE clause with implicit joins. (Personally, I prefer the explicit join syntax because it is easier to read and to separate join conditions from other filter predicates.) So you can ignore ani_dbforum's statements right away.

What should be done here is:
(a) make sure that the query is exactly what the application needs and not too much (e.g. is the DISTINCT necessary?)
(b) verify that the necessary indexes exist
(c) ensure that statistics were collected on tables and indexes

You could also run this query as workload through the DB2 design advisor. Maybe there are some other objects like MQTs that could improve the situation.

ani_dbforum · #7 (**permalink**) 01-18-08, 07:24

Thanks stolze for correcting me,

I've one more question:

A have 1 million records, B has 40 million records.
Can A inner join B with On condition faster or B inner join A faster?
or both are same?

ashok.cheppali · #8 (**permalink**) 01-18-08, 07:43

thanks, i am expecting for the above query using Joins.

Quote:

Originally Posted by ani_dbforum

Thanks stolze for correcting me,

I've one more question:

A have 1 million records, B has 40 million records.
Can A inner join B with On condition faster or B inner join A faster?
or both are same?

stolze · #9 (**permalink**) 01-22-08, 13:34

Quote:

Originally Posted by ani_dbforum

Thanks stolze for correcting me,

I've one more question:

A have 1 million records, B has 40 million records.
Can A inner join B with On condition faster or B inner join A faster?
or both are same?

DB2 has a cost-based optimizer. It will evaluate both join orders (also with different join strategies and index evaluations) and choose the plan that has the lowest costs. So writing "a JOIN b ON ..." is exactly the same as "b JOIN a ON ...".

grofaty · #10 (**permalink**) 01-28-08, 08:53

Quote:

Originally Posted by stolze

DB2 has a cost-based optimizer. It will evaluate both join orders (also with different join strategies and index evaluations) and choose the plan that has the lowest costs. So writing "a JOIN b ON ..." is exactly the same as "b JOIN a ON ...".

Hi,
this is easy to test. Run db2 explain...
Hope this helps,
Grofaty

Wim · #11 (**permalink**) 02-01-08, 05:14

Your SQL code

Code:

SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT
WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00'
;

can be rewritten as:

Code:

SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,
		SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
FROM SCANOSEC.I_APPLICATION
  INNER JOIN SCANOSEC.I_APP_RELATIONSHIP ON
    SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
  INNER JOIN SCANOSEC.I_LOAN_ACCOUNT ON
    SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
    AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
    AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00'
;

The INNER JOIN statements make it clear what the relations between the tables are. The WHERE statement now only contains business logic.

Both SQL statemenst will give the same results, in the same amount of processing time. The second is just easier to understand (once you're used to it) by separating the join conditions from the business logic conditions.

Internet Services

Web Development

Digital Marketing

Consumer Tech