CTE's and SQL performance

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

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