RE: SQL Select Statement Woes -- MIDRANGE-L

Joe,

Check the see what indexes VE recommends. But I'd say make sure an index exists over FOINSNAME and in
addition to FOINSNAME, the index contains the join fields.

Also sounds check the indexes on SCHDETAIL, VE should be telling you why the QE is choosing to build
an access path.

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Wells
Sent: Tuesday, September 11, 2007 9:05 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL Select Statement Woes

Eric,

Thank you for the tip about LOCATE. I had never heard of
this so I gave it a shot, but got the same results. I
certainly understand that LIKE would be a poor performer, but
I would have thought that the LIKE would have been applied
only to the 25 records the statement returns w/o the LIKE
condition - so that should not affect the performance.

What really confounds me is that FOINSNAME is not a field in
SCHDETAIL, yet SQL starts building an index over SCHDETAIL
when I use the LIKE condition over FOINSNAME - this make no
sense to me. W/o the LIKE condition, the select runs in a
second or two and returns 25 records.

Thanks,

Joe

DeLong, Eric<EDeLong-sBXa2RcqsacTAJazuiU7bg@xxxxxxxxxxxxxxxx>
09/10/07 5:04 PM >>>

Hmmm,

Just a thought, but it's weel known that LIKE performs
poorly, so I have experimented in the past with an
alternative that seems to perform better....

Instead of

AND ( FOINSNAME LIKE 'HEALTHSP%' OR FOINSNAME LIKE 'healthsp%')

try

AND locate('HEALTHSP', upper(FOINSNAME))>0

I have found that locate scans very quickly... Let me know
if this improves your results.

Thanks,
Eric

-----Original Message-----
From: midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
[mailto:midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxx

.org]On Behalf Of Joe Wells

Sent: Monday, September 10, 2007 4:32 PM
To: midrange-l-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
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 a
s 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

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.