RE: SQL Select Statement Woes

["Joe Wells" <jwells@xxxxxxxxx>]

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@xxxxxxxxxxxxxxxx]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