× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.