DB2 Peformance: Host Variable v/s Join



IBM's flagship relational database management system

DB2 Peformance: Host Variable v/s Join

Postby rajkusar » Fri Jun 25, 2010 9:22 pm

Is there any thumb rule to use host variable over joins in order to get better performance.
Please let me know which is a better option in both the Examples mentioned below

Example 1
Select  a.Col11,  b.Col21,  c.Col31
From
         table1 a
        ,table2 b
        ,table3 c
where
        a.Col12 = :WS-INPUT
and   b.Col22 = :WS-INPUT
and   c.Col32 = :WS-INPUT


Example 2
Select  a.Col11,  b.Col21,  c.Col31
From
         table1 a
        ,table2 b
        ,table3 c
where
        a.Col12 = :WS-INPUT
and   b.Col22 = a.Col12
and   c.Col32 = a.Col12

Please let me know if you need any other information.
rajkusar
 
Posts: 5
Joined: Mon May 10, 2010 9:26 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Peformance: Host Variable v/s Join

 

Re: DB2 Peformance: Host Variable v/s Join

Postby dick scherrer » Sat Jun 26, 2010 1:57 am

Hello,

The difference will be too small to measure.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: DB2 Peformance: Host Variable v/s Join

Postby Lindovaldo » Tue Jul 06, 2010 9:45 pm

Hello, the difference is:
You need connect table1 with table2 and table3. It is pre-requisite to have a good performance.
when you use a host variable you simply filtering the result. If this columns that you used a host-variable is a unique index column then the results is better.
Hope this helps,
Lindovaldo
 
Posts: 5
Joined: Thu Mar 11, 2010 11:56 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post