IBM Mainframe Forum

by **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 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.

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

Hello,

The difference will be too small to measure.

by **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,

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