RE: Stored Procedures vs. SQL Statements vs. CHAIN

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Brian,

Assuming you are using static embedded SQL now, I would be very
surprised if stored procedures performed any better.

With static SQL statements, you're basically already using a "stored
procedure".  The access plan is determined at compile time and stored in
the *PDM object.

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:42 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Stored Procedures vs. SQL Statements vs. CHAIN
> 
> 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.
> 
>