RE: SQL Performance Question -- MIDRANGE-L

Disregard that line about the missing EXECUTE...

It is of course not used for dynamic cursors. Just PREPARE and OPEN as you've shown...

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Thursday, July 31, 2008 9:17 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Performance Question

Ok,

Well I'd be willing to bet that when you just "run the SQL statements" in
iNav, the statement you run
is a lot simpler; which explains why it's quicker also.

First thing I notice, you're using dynamic SQL for no apparent reason.
Meaning, you're building a SQL
statement in a string, then using the PREPARE and EXECUTE commands. Though
EXECUTE actually seems to
be missing from your post. I see no reason for this as the columns you
return, the files you access
and even the order of the returned data are all static.

Besides performing poorly, dynamic SQL like you are using it is a security
risk due to its
vulnerability to SQL injection attacks. If you really want to use dynamic
SQL, then at least use
parameter markers. Not only is it more secure, but it will simply the
statement somewhat.

Instead of

IF IN_EVENT_MONTH > 0 THEN
SET STMT = STMT || ' AND lh.EventMonth = ' || IN_EVENT_MONTH ||'' ;
END IF ;

You'd use this:

IF IN_EVENT_MONTH > 0 THEN
SET STMT = STMT || ' AND lh.EventMonth = ?'
END IF ;

And you'd say

OPEN C1 using IN_EVENT_MONTH;

Now, if you wanted to switch to static SQL instead of dynamic, a technique
I use to handle the various
filters passed in is like so:

DECLARE C1 CURSOR WITH RETURN FOR
SELECT lh.company,
lh.EventName,
lh.CustId,
-- snipped a few lines
WHERE (lh.ListType = 1)
AND (lh.Company = IN_COMPANY )
AND (lh.IsDeleted = 0 )
AND ( (IN_EVENT_MONTH is NULL)
Or (lh.EventMonth = IN_EVENT_MONTH)
)

Now onto what is probably the root of your problem....
AND (lower(replace(lh.Fname,'' '',''''))
like lower(replace(''' || IN_REGISTRANT_FIRST_NAME || '%''
, '' ''
,'''')
)
OR lower(replace(lc.Fname,'' '',''''))
like lower(replace(''' || IN_REGISTRANT_FIRST_NAME || '%''
, '' ''
, '''')
)
)

First off, using LOWER and REPLACE or any other function on a column in a
table , like lh.fname or
lc.fname, will usually result in a full table scan.

Secondly, I'm not entirely sure why you're even using the REPLACE; if they
are fixed length, it would
have no effect. If they are VARCHAR, then the spaces shouldn't be there.
Unless you're trying to take
out embedded spaces?

Third, instead of using LOWER, consider creating a case-insensitive index
along and making your select
case-insensitive. To do so, first create the case-insensitive indexes
simply by changing sort
sequence you use to create them to *LANDIDSHR then just add a SET OPTION
SRTSEQ = *LANGIDSHR to your
stored procedure.

Lastly, consider moving the functions on the input parameters, such as
concatenation, outside the SQL
statement itself.

Like so:

Declare wFirstName as varchar(50);

If IN_REGISTRANT_FIRST_NAME is not NULL
Then set wFirstName = IN_REGISTRANT_FIRST_NAME CONCAT '%';
End if;

--another form

Set wFirstName
= case
When IN_REGISTRANT_FIRST_NAME is not NULL
Then IN_REGISTRANT_FIRST_NAME CONCAT '%'
Else
NULL
End;

--now use it

DECLARE C1 CURSOR WITH RETURN FOR
SELECT lh.company,
lh.EventName,
lh.CustId,
-- snipped a few lines
WHERE (lh.ListType = 1)
AND (lh.Company = IN_COMPANY )
AND (lh.IsDeleted = 0 )
AND ( (IN_EVENT_MONTH is NULL)
Or (lh.EventMonth = IN_EVENT_MONTH)
)
AND ( (IN_REGISTRANT_FIRST_NAME is NULL)
Or (lh.fname like wFirstName)
Or (lc.fname like wFirstName)
)

Hope this helps!

Charles

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of DebbieKelemen
Sent: Wednesday, July 30, 2008 6:35 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Performance Question

Here is the "actual" code in the stored procedure: Any help would be
appreciated. I'm not sure about using a CLOB - however, the web guys

use

it
everywhere.

BEGIN
DECLARE STMT CLOB ;
DECLARE C1 CURSOR WITH RETURN FOR SQLSTMT ;

SET STMT = 'SELECT lh.company,
lh.EventName,
lh.CustId,
lc.FName as CoRegFname,
lc.LName as CoRegLName,
lh.ListId,
lh.EventDate,
lh.Message,
lh.EventCity,
lh.EventState,
lh.EventOccasion,
lh.Fname,
lh.Lname,
lh.CampaignID,
CWMPINH.GETEMAILADDRESS(CAST (LH.COMPANY AS CHAR(3)) , CAST(LH.CUSTID AS
INT)) AS EMAILADDRESS
FROM CWMPWEB.Listheader lh
LEFT OUTER JOIN CWMPWEB.ListCoReg lc on lh.Company = lc.Company AND
lh.CustId = lc.ParentCustId AND lh.ListId = lc.ListId
WHERE lh.ListType = 1
AND lh.Company = ' || IN_COMPANY || '
AND lh.IsDeleted = 0' ;

IF IN_REGISTRANT_FIRST_NAME <> '' THEN
SET STMT = STMT || ' AND (lower(replace(lh.Fname,''
'','''')) like lower(replace(''' || IN_REGISTRANT_FIRST_NAME || '%'', ''
'',
'''')) OR lower(replace(lc.Fname,'' '','''')) like lower(replace(''' ||
IN_REGISTRANT_FIRST_NAME || '%'', '' '', '''')))' ;
END IF ;

IF IN_REGISTRANT_LAST_NAME <> '' THEN
SET STMT = STMT || ' AND (lower(replace(lh.Lname,''
'','''')) like lower(replace(''' || IN_REGISTRANT_LAST_NAME || '%'', ''
'',
'''')) OR lower(replace(lc.Lname,'' '','''')) like lower(replace(''' ||
IN_REGISTRANT_LAST_NAME || '%'', '' '', '''')))' ;
END IF ;

IF IN_EVENT_CITY <> '' THEN
SET STMT = STMT || ' AND lower(replace(EventCity,''
'','''')) like lower(replace(''' || IN_EVENT_CITY || '%'', '' '',

''''))'

;
END IF ;

IF IN_EVENT_STATE <> '' THEN
SET STMT = STMT || ' AND lower(replace(EventState,''
'','''')) like lower(replace(''' || IN_EVENT_STATE || '%'', '' '',

''''))'

;
END IF ;

IF IN_EVENT_MONTH > 0 THEN
SET STMT = STMT || ' AND lh.EventMonth = ' || IN_EVENT_MONTH
||'' ;
END IF ;

IF IN_EVENT_YEAR > 0 THEN
SET STMT = STMT || ' AND lh.EventYear = ' || IN_EVENT_YEAR
||'';
END IF ;

SET STMT = STMT || ' ORDER By lh.Lname' ;

PREPARE SQLSTMT FROM STMT ;
OPEN C1 ;
SET RESULT SETS CURSOR C1 ;
END

--
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.
--
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.