RE: CTE's and SQL performance

Not sure what your performance issue is exactly, but I want to bring up
couple of points that might help.

* Are you joining different CTEs in some manner?  
Keep in mind, when there is no explicit join specification and you list more
than one file on the FROM clause of the select, there is an explicit
Cartesian product join taking place.  I assure you, you do NOT want that to
happen.  Although, in your example you're performing a SUM so really there
should only be one resultant row.  That probably eliminates that scenario as
being the cause of poor performance.

* You have some unnecessary code in the WHERE clause.
In your example you show:

 pstper >= 10608 and
 pstper <= 10608  and

This code does nothing so you may just as well take it out.

* Do you have indexes over 'cono' and 'ledno'?

* You can ignore the message complaining about qusrsys/qaqqini file not
found, it's inconsequential.  Or if you'd rather not see it, just CRTDUPOBJ
of QAQQINI from QSYS to QUSRSYS library with DATA(*YES).  This will
eliminate the message and won't have other side effects.

* Finally, try running your statement with debug turned on (STRDBG).  You
can effect this in Run Sql Script session by prefixing the command with
"CL:".
When you start debug in a job running SQL, you're in effect telling query
optimizer to write it's performance optimization messages to the joblog.
These messages could help you figure out your problem or even better what
indexes might help your queries.

HTH.

Elvis

-----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