RE: SQL Performance Question -- MIDRANGE-L

Hope it helps.

My experience has been that the .NET, Java, and SQL guys are good with SQL, its just that techniques
that work fine when dealing with 100,000 row tables fall down when dealing with 10,000,000 row ones.

Dynamic statements are a big part of that. Seem to be quite popular on the dark side <grin>. But for
any DB, static is better.
"You use dynamic sql only when there is quite simply NO WAY to do it statically." from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5243903111288

"When developing code you should always consider using static SQL. Dynamic SQL only becomes a
possible solution if you can find no way to use static SQL." from (free registration required)
http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicvsstatic2/623/

HTH,

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: Thursday, July 31, 2008 3:39 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Performance Question

Thanks Charles!

To answer your question - I am the first to admit I am really naïve when
it
comes to SQL. Our web guys who know .NET, Java, SQL, etc. needed to
access
data on the iSeries. They couldn't wait for it to be developed for them.
So since, they knew SQL the head of our department let them go at it.

Now I'm stuck solving their performance issues.

I would love to be able to get up to speed fast on SQL and performance.
However, with ever other duty I have it takes time.

I appreciate all the help you guys have given me.

Debbie

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Thursday, July 31, 2008 7: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 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.