RE: CTE's and SQL performance

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Chris,

When you generate several CTE's, I expect you're joining them all together in 
the main clause of your sql.  I expect that the joins are being processed 
inefficiently, but without that part of the statement I couldn't say....

Perhaps a few things to look at.....

Try to use the formal join syntax, specifying inner/outer and using the on 
clause to define your join criteria.  Evaluate the where clause for selection 
criteria that could be moved to your join criteria, in cases where you're 
testing a primary key in an index....  I believe that filtering in the on 
clause reduces the number of rows that need to be joined to the rest of the 
record sets, improving efficiency.

Make sure that your have appropriate indexes.  Run the statement in debug mode 
to pick up access path suggestions.

hth,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Chris Payne
Sent: Monday, October 09, 2006 1:09 PM
To: midrange-l@xxxxxxxxxxxx
Subject: CTE's and SQL performance


Hello,

            I have a SQL problem, I have a very large SQL statement with
a great many CTE's, when I add a couple more, which contain relatively
simple SQL that runs in a couple of second by themselves, my performance
gets much worse. Here is a sample

 

SELECT

  sum(NPDR04 + NPCR04) as Ponc_cost

 FROM

  oslglf3.flp004

 WHERE

  cono = 'C1' and

  ledno = 'I/S' and

  pstper >= 10608 and

  pstper <= 10608  and

  accn04 in (

   '081421001000',

   '081421002000', 

   '081421003000', 

   '081421004000', 

   '081421005000' 

            )

 

So if I run this statement solo, it take 2 or 3 seconds, I have 4
statements like this, if I add them all as CTE's like so

 

 

GL_PONC as(

SELECT

  sum(NPDR04 + NPCR04) as Ponc_cost

 FROM

  oslglf3.flp004

 WHERE

  cono = 'C1' and

  ledno = 'I/S' and

  pstper >= 10608 and

  pstper <= 10608  and

  accn04 in (

   '081421001000',

   '081421002000', 

   '081421003000', 

   '081421004000', 

   '081421005000' 

            )

)

Then my total query run time goes from something like 2 minutes to more
like 8-10 minutes. Does anyone have an idea what might cause that?
Visual explain crashes when run against this query (I have been told
that it cannot handle certain complex queries) one thing I notice in the
job log while the query is running is that it keeps complaining about
"Unable to retrieve query options file." It tells me that qaqqini is not
in qusrsys.

 

Thanks, 

 

Chris

___________________________________
Christopher Payne
CPayne@xxxxxxxxxxxxxxx
Pricing Financial Analyst

 

The Crown Group
Corporate Offices
2111 Walter Reuther Drive
Warren, MI 48091-6199

 

Phone: (586) 575-9800
Direct: (586) 558-5317
Fax:  (586) 575-9856