IBM Mainframe Forum

Posted: **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 all

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 all

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.

Posted: **Sat Jun 26, 2010 1:57 am**

Hello,

The difference will be too small to measure.

Posted: **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,

IBM Mainframe Forum

DB2 Peformance: Host Variable v/s Join

DB2 Peformance: Host Variable v/s Join

Re: DB2 Peformance: Host Variable v/s Join

Re: DB2 Peformance: Host Variable v/s Join