RE: CTE's and SQL performance -- MIDRANGE-L

I can see in my question I did a bad job explaining the problem, the
performance problem happens after I add the inline views to the query,
but before I have joined them into the main select clause. Their
presence alone cause the problem, however by themselves they each run
for a couple of seconds and returns a single number. I suspect that I am
bumping up against some kind of DB2 size limit, perhaps too many
characters or too many inline views, which is making it change something
about the way it runs it. When I try to run the statement in STRSQL it
refuses on the grounds that " Length of statement exceeds 32,767
characters."

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Monday, October 09, 2006 2:45 PM
To: Midrange Systems Technical Discussion
Subject: RE: CTE's and SQL performance

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