ANSI vs. traditional syntax in joins

ANSI vs. traditional syntax in joins

Post by Rory Plai » Wed, 04 Dec 2002 05:04:18



Hi all,

I'm fairly new to informix, but reading groups, manuals, etc. brought
me up to a practical level of understanding. Now, I am having trouble
filling in some gaps. Perhaps someone might help. (And, if I have been
paying attention to the postings over the last 5 years in this group,
I have a good idea who it might be. 8)

In a system I am now responsible for, the last admin created several
user views. In them, the traditional join syntax was used, in which
the joins were in the SQL WHERE clause. I was thinking, for standards
sake, and since I have more knowledge of ANSI over informix SQL, I
converted them to the ANSI JOIN syntax.

A surprising thing has happened. In one view that I have tested, the
performance drops close to zero, from what was a pretty speedy query
with the traditional join. When I rearranged the INNER JOIN statements
and removed the nesting, it improves significantly, but is still
slower than the traditional join syntax.

What could be causing the difference in speed? Could someone point me
to where I might find how Informix optimizes such queries?

appreciative,
-rory 8)

 
 
 

ANSI vs. traditional syntax in joins

Post by Obnoxio The Clow » Wed, 04 Dec 2002 06:41:37


Rory Plaire came forth and spake, saying:

Quote:> Hi all,

> I'm fairly new to informix, but reading groups, manuals, etc. brought
> me up to a practical level of understanding. Now, I am having trouble
> filling in some gaps. Perhaps someone might help. (And, if I have been
> paying attention to the postings over the last 5 years in this group,
> I have a good idea who it might be. 8)

> In a system I am now responsible for, the last admin created several
> user views. In them, the traditional join syntax was used, in which
> the joins were in the SQL WHERE clause. I was thinking, for standards
> sake, and since I have more knowledge of ANSI over informix SQL, I
> converted them to the ANSI JOIN syntax.

> A surprising thing has happened. In one view that I have tested, the
> performance drops close to zero, from what was a pretty speedy query
> with the traditional join. When I rearranged the INNER JOIN statements
> and removed the nesting, it improves significantly, but is still
> slower than the traditional join syntax.

> What could be causing the difference in speed? Could someone point me
> to where I might find how Informix optimizes such queries?

I think I saw something recently about optimiser bugs in the handling of
ANSI join syntax. Would certainly explain it.

--

Warm wishes (or KMA, depending on the tone of my post)
Obnoxio

"C'est pas parce qu'on n'a rien dire qu'il faut fermer sa gueule"
  - Coluche

 
 
 

ANSI vs. traditional syntax in joins

Post by Malcolm Weallan » Wed, 04 Dec 2002 20:07:23


Rory,
I suggest you should use SET EXPLAIN ON and then SELECT * FROM viewname.
If you did that for the oldstyle view and the new one you could compare
the differences.  As to understanding why there is a difference in speed
I suspect that the informix optimizer was written with Informix joins in
mind and the changes to make it cope with both sets of syntax, possibly
in the same statement, are the cause of the different performance.

Malcolm

-----Original Message-----

Sent: 02 December 2002 20:04

Subject: ANSI vs. traditional syntax in joins

Hi all,

I'm fairly new to informix, but reading groups, manuals, etc. brought
me up to a practical level of understanding. Now, I am having trouble
filling in some gaps. Perhaps someone might help. (And, if I have been
paying attention to the postings over the last 5 years in this group,
I have a good idea who it might be. 8)

In a system I am now responsible for, the last admin created several
user views. In them, the traditional join syntax was used, in which
the joins were in the SQL WHERE clause. I was thinking, for standards
sake, and since I have more knowledge of ANSI over informix SQL, I
converted them to the ANSI JOIN syntax.

A surprising thing has happened. In one view that I have tested, the
performance drops close to zero, from what was a pretty speedy query
with the traditional join. When I rearranged the INNER JOIN statements
and removed the nesting, it improves significantly, but is still
slower than the traditional join syntax.

What could be causing the difference in speed? Could someone point me
to where I might find how Informix optimizes such queries?

appreciative,
-rory 8)

DISCLAIMER
This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system. E-mail transmission cannot be
guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or
contain viruses. The sender therefore does not accept liability for any
errors or omissions in the contents of this message, which arise as a
result of e-mail transmission. If verification is required please
request a hard-copy version.

 
 
 

ANSI vs. traditional syntax in joins

Post by Rory Plai » Thu, 05 Dec 2002 07:27:52



Quote:> I think I saw something recently about optimiser bugs in the handling of
> ANSI join syntax. Would certainly explain it.

T'would indeed. Seeing as I took Malcom's advice and SET EXPLAIN ON.
The results are the same, identical, that is. One wonders why the
optimizer would treat them so differently?

I'm not ready to throw in the towel yet... I think I'll call Informix.

Thanks, all,
-rory 8)

 
 
 

ANSI vs. traditional syntax in joins

Post by Andre » Thu, 05 Dec 2002 09:54:06



Quote:> Hi all,

> I'm fairly new to informix, but reading groups, manuals, etc. brought
> me up to a practical level of understanding. Now, I am having trouble
> filling in some gaps. Perhaps someone might help. (And, if I have been
> paying attention to the postings over the last 5 years in this group,
> I have a good idea who it might be. 8)

> In a system I am now responsible for, the last admin created several
> user views. In them, the traditional join syntax was used, in which
> the joins were in the SQL WHERE clause. I was thinking, for standards
> sake, and since I have more knowledge of ANSI over informix SQL, I
> converted them to the ANSI JOIN syntax.

> A surprising thing has happened. In one view that I have tested, the
> performance drops close to zero, from what was a pretty speedy query
> with the traditional join. When I rearranged the INNER JOIN statements
> and removed the nesting, it improves significantly, but is still
> slower than the traditional join syntax.

> What could be causing the difference in speed? Could someone point me
> to where I might find how Informix optimizes such queries?

> appreciative,
> -rory 8)

I don't know what version you're on - but when I went on the9.21 course
about a year ago  -they were still teaching the old WHERE IN / WHERE NOT IN
kind of stuff.  I would assume   from that, that at least to 9.21 the old
SQL syntax is the recommended one.

--

- Show quoted text -

Quote:> 'One accusation you can't throw at me is that I've always done my best.'
> -
> Alan Shearer

 
 
 

ANSI vs. traditional syntax in joins

Post by Art S. Kage » Fri, 06 Dec 2002 05:40:27


Part of the problem is that the optimizer was not written with this kind
of a join in mind.  Part is getting used to using ANSI syntax
efficiently.  If you have left all of the single table filters in the
WHERE clause and only have join conditions in the ON clauses the
optimizer has to join all records then when it has a results set of
tuples apply the WHERE filters.  So if you took a join like this:

select *
from tabla a, tablb b
where a.key = b.key
   and a.key between 1 and 100
   and b.fluff = 12;

and tranformed it into:

select *
from table a inner join tablb
   on a.key = b.key
where a.key between 1 and 100
   and b.fluff = 12;

you are in trouble but make it:

select *
from table a inner join tablb
   on a.key = b.key
     and a.key between 1 and 100
     and b.fluff = 12;

And things get better.  IBM still needs to do some work on the optimizer
to get these to perform best but this way is usually very close and
sometimes faster.

Art S. Kagel


> Hi all,

> I'm fairly new to informix, but reading groups, manuals, etc. brought
> me up to a practical level of understanding. Now, I am having trouble
> filling in some gaps. Perhaps someone might help. (And, if I have been
> paying attention to the postings over the last 5 years in this group,
> I have a good idea who it might be. 8)

> In a system I am now responsible for, the last admin created several
> user views. In them, the traditional join syntax was used, in which
> the joins were in the SQL WHERE clause. I was thinking, for standards
> sake, and since I have more knowledge of ANSI over informix SQL, I
> converted them to the ANSI JOIN syntax.

> A surprising thing has happened. In one view that I have tested, the
> performance drops close to zero, from what was a pretty speedy query
> with the traditional join. When I rearranged the INNER JOIN statements
> and removed the nesting, it improves significantly, but is still
> slower than the traditional join syntax.

> What could be causing the difference in speed? Could someone point me
> to where I might find how Informix optimizes such queries?

> appreciative,
> -rory 8)

 
 
 

ANSI vs. traditional syntax in joins

Post by Andrew Ham » Fri, 06 Dec 2002 08:11:18



> Part of the problem is that the optimizer was not written with this
> kind of a join in mind.  Part is getting used to using ANSI syntax
> efficiently.  If you have left all of the single table filters in the
> WHERE clause and only have join conditions in the ON clauses the
> optimizer has to join all records then when it has a results set of
> tuples apply the WHERE filters.  So if you took a join like this:

>[SNIP]

> And things get better.  IBM still needs to do some work on the
> optimizer to get these to perform best but this way is usually very
> close and sometimes faster.

I would hazard a guess that the ANSI joins can mostly be commutatively
transformed into more optimal queries by hoisting some of the WHERE filters
into the ON clause, however I wouldn't like to be writing *that* code - or
any of the optimiser code for that matter &^(). Presumably improved
efficiencies will come with new versions as the optimiser gets more
effective in this area?
 
 
 

1. ANSI JOIN syntax

I have a discerpancy between the results i get using ANSI vs. T-SQL syntax.
Can anyone explain why the two following SELECT statements yield two
different answers:

--T-SQL syntax

SELECT  SUM((AU.Usage_Per_Cust/CP.Percentage) * CC.Cust_Count)

FROM   tbl_GNG_Cust_Count  AS CC,
  tbl_GNG_AVG_Use AS AU,
  tbl_GNG_CIS_Percentages AS CP,
  tbl_GNG_Rate_Values AS RV

WHERE   CC.Data_Month  = CP.Data_Month
AND  CC.Scenario  = AU.Scenario
AND  CC.Data_Month   = AU.Data_Month
AND  CC.Pool_Group   = AU.Pool_Grp
AND  CC.Rate_Sched   = AU.Rate_Schedule
AND  CC.Scenario  *= RV.Rate_Scenario
AND  CC.Data_Month *= RV.Data_Month
AND  CC.Rate_Code *= RV.Rate_Code

AND CC.Scenario  = 'B'
AND DATEPART(Year,CC.Data_Month)  = 2003
AND CC.Eff_Date  = '8/22/2002'
AND AU.Eff_Date  = '9/9/2002'
AND RV.Eff_Date     = '9/16/2002'

----------------------------------------------------------------------------
-----------------
-- ANSI SQL JOINS

SELECT  SUM((AU.Usage_Per_Cust/CP.Percentage) * CC.Cust_Count)

FROM   tbl_GNG_Cust_Count  AS CC
LEFT OUTER JOIN tbl_GNG_Rate_Values AS RV

ON  CC.Scenario = RV.Rate_Scenario
AND  CC.Data_Month = RV.Data_Month
AND  CC.Rate_Code = RV.Rate_Code

INNER JOIN tbl_GNG_AVG_Use AS AU

ON  CC.Scenario  = AU.Scenario
AND  CC.Data_Month   = AU.Data_Month
AND  CC.Pool_Group   = AU.Pool_Grp
AND  CC.Rate_Sched   = AU.Rate_Schedule

INNER JOIN tbl_GNG_CIS_Percentages AS CP

ON   CC.Data_Month  = CP.Data_Month

WHERE  CC.Scenario  = 'B'
AND DATEPART(Year,CC.Data_Month)  = 2003
AND CC.Eff_Date  = '8/22/2002'
AND AU.Eff_Date  = '9/9/2002'
AND RV.Eff_Date     = '9/16/2002'

/*******************************

I am testing the query, so I've replaced all the variables with values.
Tables and data too large to post, but we can try another method of file
transfer if anyone would like to give it a shot...

*****************************/

Thanks,
Daniel Mikkelsen

2. Locked out in exclusive mode

3. Resource Manager internal consistency error

4. Help: ANSI join syntax

5. Perform data ordering

6. ANSI SQL Join Syntax for sproc

7. Microsoft SQL Server 2000 Named Best Overall Database

8. Ansi Join Syntax

9. ANSI Outer Join Syntax Fails

10. ANSI JOIN syntax question.

11. ANSI outer join syntax

12. Bug in UPDATE using ANSI JOIN syntax????????