which sql is better on perfomance in DB2 and how conditional where clause works?

[damodharan]

Hello and thanks in advance.

I have two table in DB2 (ver 8). table details are

table one(Cust_det) has two fields (Cust_cin and cust_name).
table two(cust_add_de t) has cust_cin and cust_address fields.

To join above two table which sql is better on perfomance point of view?
and how conditional where clause works?

select
  A.cust_cin, B.cust_address
from
  Cust_det A, cust_add_det B
where
  A.Cust_cin = ?
and
  A.cust_cin=B.cust_cin

or

select
  A.cust_cin, B.cust_address
from
  Cust_det A, cust_add_det B
where
  A.cust_cin=B.cust_cin
and
  A.Cust_cin = ?

[KiranKGone]

The first option should work better, coz, the first predicate is indexable, the second one is not.. the resultant set of the where clause is filtered by the first predicate and then the second predicate is applied to the resultant set of the first.. Hope this helps !!

[docdiesel]

Hi,

both statements will be executed by DB2 at the very same speed. Before execution, DB2's internal optimizer is analyzing the SQL and will weigh each condition before setting up an execution plan, so that the SQL is executed the fastest way (in its point of view).

Besides, if you're searching for the field cust_cin, to speed up the query forget about the table Cust_det and just rely on table cust_add_det:

select
  C.Cust_CIN, C.Cust_Address
from
  Cust_Add_Det C
where
  C.Cust_cin = ?
 

Furthermore, if you need to join those two tables in a query,

• define Cust_CIN as primary key in both tables (this sets up indexes)
• define a foreign key constraint between Cust_CIN in both tables
• use an INNER JOIN within your SQL statement:
  

  SELECT ...
  FROM       tab1 A
  INNER JOIN tab2 B
  ON    A.cin=B.cin
  WHERE ...

Regards,

Bernd

P.S. I added some code tags to your post for better readability.

[docdiesel]

Btw., you may see how DB2 and its optimizer are working by using the EXPLAIN functionality. Open the control center, right-click the database and choose the EXPLAIN option. Enter your sql statement and click OK. You will be presented a graphical plan how DB2 would be retrieving the date, using table scans, index scans etc. On this plan, you may also see where the most time is needed to fetch the data (in DB2 internal units "timerons") , means, where the biggest resource for optimization by change of design lies (e.g. adding indexes, splitting tables).

Regards,

Bernd