I wonder if using CTE could help. If he could filter the SCHDETAIL table (12 million records) to eliminate rows BEFORE doing the joins and LIKE clauses. The OP stated that without the LIKE, the select returned 25 rows, of which 5 were returned with the LIKE.
The objective would be to eliminate rows BEFORE the like clause gets evaluated....
Eric
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Elvis Budimlic
Sent: Tuesday, September 11, 2007 9:33 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Select Statement Woes
You cannot force a statement to go down SQE path, but you can force it to go
down CQE path by putting in a dummy LIKE (pre-V5R4) or dummy UPPER (even on
V5R4). Obviously in your case CQE is the one performing poorly so that
doesn't get you anywhere.
Since the difference in performance of 2 engines is so vast, I would deem
this as a PTF worthy issue. Your best bet is reporting it to Rochester IBM
support as a functional problem and have them provide you a PTF (they may
already have one).
Now, if issue was with SQE, my experience has been that they would fix it in
an IBM heartbeat.
With CQE though, since it's only in maintenance mode, it is hard to predict
how IBM will treat it. They may punt or not.
You will need to provide them with two database monitor files, one going CQE
path and other going SQE path. That ought to be sufficient for them to
diagnose the issue. It would be for me.
Elvis
RPG & DB2 Summit | Minneapolis | October 1-4
Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Wells
Sent: Tuesday, September 11, 2007 8:25 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL Select Statement Woes
Elvis,
I must confess that I was ignorant and had no idea what Visual Explain was.
After Googling around, I learned what it was and ran it and you were right
on target. With the LIKE clause, CQE is used. W/o it, SQE is used. The
graph that is displayed is also drastically different. I will dig around
more and see what else VE has to offer.
So, now the question is - Is there a workaround? Can you force a statement
to use one engine or another? Maybe with the QAQQINI?
Thanks,
Joe
Elvis
Budimlic<ebudimlic-Ebbh53c6wcYkzXN11fiE55igCaZBIYHkAL8bYrjMMd8@xxxxxxxxxxxxx
org> 09/10/07 5:37 PM >>>
Prior to V5R4, LIKE clause goes down CQE path. If you take it out, perhaps
it goes down SQE path and happens to perform better.
Another possibility is that it uses an index in one case and not another.
It is possible for query optimizer to use an index over a literal with
trailing wildcard, but you're using upper & lower case and that is not
possible for both cases.
If you use UPPER, it'll definitely go down CQE path, and may (highly
unlikely) use an index.
There are other possibilities too... Visual Explain is your friend.
Elvis
RPG & DB2 Summit | Minneapolis | October 1-4
Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400
-----Original Message-----
Subject: SQL Select Statement Woes
My apologies in advance for the long post.......I hope my question can be
understood........here goes!.....
Our users use ShowcaseQuery 7.0. Some of the users run queries that take an
excessive amount of time to run (I guess this always the case when user have
the ability to run queries!). At any rate, I have been asked to analyze
some of the queries and see where they went astray. So far, I have had
good luck identifying and correcting the issues, but the SQL statement
listed below has me stumped. This query is realy quite simple, but takes
over two hours to run! As an experiment, I wrote an equivalent RPG program
and it takes a second or two to run! That tells me that something is wrong
with this statement. Throught trial and error, I have found that if I
remove "AND ( FOINSNAME LIKE 'HEALTHSP%' OR FOINSNAME LIKE 'healthsp%'" from
the select statement, then it runs in a second or two as well. With this
statement, it wants to build an access path over SCHDETAIL (a 12 million
record file). I should add that with this statement removed, 25 records are
returned. Of these 2
5, about 20 would have been eliminated with the offending statement
included. I would have thought that this selection would have been done
after all of the other selections......so why is it trying to build an
access path? BTW, I have run in debug and no access paths are suggested.
Thanks,
Joe
****************************************************************************
************
SELECT
DEPTDESC,
PROVNAME,
APPTDATE,
CATMNE,
PRVTYPEMNE,
MRN,
PATLNAME,
PATFNAME,
PATHOMEPH,
PATWORKPH,
SCHEDDATE,
SCHEDINITS,
CASE
WHEN ( FOEXPDATE = '0001-01-01' ) THEN ( ' ' )
ELSE ( CONVERT ( FOEXPDATE, SQL_CHAR ) )
END AS FOEXPDATE,
"CASE" AS CCASE,
FOINSNAME
FROM
WARESCHED.SCHDETAIL SCHDETAIL,
WARESCHED.SCHDEPT SCHDEPT,
WAREHOUSE.PATHEADER PATHEADER,
WAREHOUSE.PATFOLOWUP PATFOLOWUP,
WARESCHED.SCHPROVDRS SCHPROVDRS,
WARESCHED.SCHCATEGRY SCHCATEGRY,
WARESCHED.SCHPROVTYP SCHPROVTYP
WHERE
SCHDETAIL.SCHDEPT = SCHDEPT.DEPTNUM
AND SCHDETAIL.PATMRN = PATHEADER.MRN
AND PATHEADER.FSC1CD = PATFOLOWUP.FOFSC
AND PATHEADER.MRN = PATFOLOWUP.FOMRN
AND SCHDETAIL.PROVIDER = SCHPROVDRS.PROVNUM
AND SCHDETAIL.CATEGORY = SCHCATEGRY.CATNUM
AND SCHDETAIL.SCHDEPT = SCHPROVTYP.PRVTYPEDEP
AND SCHDETAIL.PROVIDER = SCHPROVTYP.PRVTYPEPRV
AND SCHDETAIL.TYPE = SCHPROVTYP.PRVTYPETYP
AND(APPTDATE = date( Case when DAYOFWEEK( CURDATE( ) ) = 6 Then
days( CURDATE( ) ) + 3 Else days( CURDATE( ) ) + 1 End )
AND STATUS NOT IN( 'CANCELLED', 'BUMPED' )
AND SCHDEPT <> 3700
AND PROVIDER NOT IN( 1870, 931, 3058, 1554, 1071, 3689, 1415, 2947,
2537, 2731, 3195, 1537, 2025, 530, 1620, 2426, 3040, 3316, 1708, 2977, 1558,
10238 )
AND DEPTANCIL = 'N'
AND DEPTRESOUR = 'N'
AND ( FSC1INSCD IN( 'SF', 'HPM', 'HPA', 'TOH' )
OR ( FSC1INSCD = 'CMO'
AND ( FOINSNAME LIKE 'HEALTHSP%'
OR FOINSNAME LIKE 'healthsp%'))))
ORDER BY
1,
6,
3
midrange.com
