RE: Stored Procedures vs. SQL Statements vs. CHAIN

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

To make things murkier - a stored procedure on iSeries can be a call 
to a regular program - this is an external procedure, IIRC. And that 
program can do whatever - native IO, anything they do now. You can 
even call QCMDEXC using the SQL CALL statement that is used to call 
an SP. Or a stored procedure could be almost the same, if defined as 
an SQL-type procedure, from the embedded stuff you have now - 
assuming you are using embedded and not RUNSQLSTM or QMQRY.

I think there will be little advantage to going with a stored 
procedure over embedded methods - and you add some complexity in 
development and maintenance of the application that might not justify 
the change. But if you can experiment, fun some benchmarks and let us 
know - good stuff.

Later
Vern

At 07:42 AM 5/5/2006, you wrote:

> Thanks for the great info, Charles.
>
> I've run all of my statements through the query advisor and set up the
> appropriate indexes (when it has advised I should set up an index).
> However, I'd like to optimize it if I can a bit further, so I thought
> I'd travel the stored procedure route to see if it would make a
> difference.
>
> I think a huge issue right now is our box.  We're hitting well over
> double in out interactive sessions at peak times, so the box is doing an
> awful lot of thrashing.  I'm hoping that the new box will eliminate this
> issue.
>
> Brian.
>
> -----Original Message-----
> From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx]
> Sent: Friday, May 05, 2006 8:33 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Stored Procedures vs. SQL Statements vs. CHAIN
>
> Brian,
>
> Generally speaking, an SQL statement that updates "several hundred"
> records in one shot would be faster than updating the same records using
> native I/O.
>
> IIRC, SQL is generally faster for updating when you get above 100
> records.
>
> However, you're dealing with a "series of SQL statements".
>
> If you'd have to replace the series of SQL statements with a series of
> native I/O statements, then I'd say stick with the SQL.
>
> If you could replace the series of SQL statements with a single set of
> native I/O, you could possibly find the native I/O to be faster.
>
> On the other hand, if you can use a single set of native I/O statements,
> you should also be able to use a single SQL statement.  Going back to
> the first sentence, the single SQL statement would be your fastest
> method.
>
>
> Are you not happy with the performance as it stands now?
>
> Have you made sure that the appropriate indexes are in place?
>
> HTH,
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski
> > Sent: Friday, May 05, 2006 8:17 AM
> > To: Midrange Systems Technical Discussion
> > Subject: Stored Procedures vs. SQL Statements vs. CHAIN
> >
> > Hi All,
> >
> >
> >
> > Can someone tell me in terms of speed which method would be best when
> > working with multiple records?  Right now my code uses a series of SQL
> > statements to update several hundred records in one shot.
> > I'm thinking
> > about moving these statements to a stored procedure instead, but I
> > wanted to weigh my choices against a good old CHAIN command as well.
> >
> >
> >
> > Anyone have any thoughts or comments?
> >
> >
> >
> > Also, can someone please recommend a good Redbook that
> > discusses Stored
> > Procedures on the i5?
> >
> >
> >
> > Thanks!
> >
> >
> >
> > Brian.
> >
> >
> >
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-
> >
> > Brian Piotrowski
> >
> > Specialist - I.T.
> >
> > Simcoe Parts Service, Inc.
> >
> > PH: 705-435-7814
> >
> > FX: 705-435-6746
> >
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-
> >
> >
> >
> > --
> > 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 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.