RE: stored procedure vs. prepared statement
I do agree that SPL can provide advantages from both the control and network traffic standpoints. I think most of my feelings come from my familiarity with debugging facilities for C, vs. the debugging facilities I have seen for stored procedures(none). There might be some out there, but I have never had an employer who would consider buying such a beast.. I will admit I take great comfort in being able to step through my code and make sure that nothing surprising is happening. I succumb to laziness pretty easily. Will >===== Original Message From Paul Watson <PWatson@Lastminute.com> ===== >We use SPL quite a lot especially in a web environment, there are a couple >of >funnies but nothing unsurmountable. Longer term we'll probably consider >moving it to Java especially as the JVM is now in the engine, >but we're not in any hurry. > >A couple of years we were involved in a project where the front-end was >in Gupta with an Informix backend. All the SQL, without exception, was done >in SPL. This significantly reduced the network traffic, and made sure the >all >the SQL was optimised and tuned by the DBA team and not by Gupta >developers!! > >Paul Watson # >WFSoftware Ltd # Things Are Going to >Tel: +44 1436 674729 # Get a Lot Worse >Fax: +44 1436 678693 # Before Things Get Worse >www.wfsoftware.com/informix # > >-----Original Message----- >From: William Rice [mailto:ricew@operamail.com] >Sent: Monday, April 17, 2000 6:42 PM >To: Kevin Macclay; informix-list@iiug.org >Subject: RE: stored procedure vs. prepared statement > > >The first step I would probably take for both performance and reliability is >upgrade.... > >Prepared statements can be used when a program executes a given >statement multiple times during one given connection. This way the >query plan for this action is only generated once. > >To be honest, I tend to stay away from stored procedures due to >religious reasons. I have found them useful on occasion, but have >never really explored their use for a performance benefit. They probably >can provide a large performance benefit if you can cut down a large >amount of data transfer from the database to the client by using them. > >Hope this helps, >Will >>===== Original Message From "Kevin Macclay" <kmacclay@vantage.com> ===== >>We're running INFORMIX-OnLine Version 7.12.UC1. >> >>We're using Informix as a back-end to our web site and wish to improve >performance between our code and the database. We're currently using >Statement objects to perform all queries. I have read, however, that it is >smarter to use PreparedStatements which can be compiled once and >>re-used without the database having to recompile/re-evaluate the statement. > >Does it make sense to use a PreparedStatement for every SQL statement we're >executing (they're all very basic queries, updates, and inserts -- nothing >too >fancy). I've also read about Stored Procedures >>however, and this has confused me a great deal! When is it correct to use >a >PreparedStatement and when is it best to use a Stored Procedure? I read >somewhere that Stored Procedures aren't necessarily a performance boost and >are mostly used to encapsulate a few queries that logically >>fit together. Is this correct? Does it make sense to implement Stored >Procedures to improve performance with Informix? >> >>Any help or insight is greatly appreciated! Thanks in advance. >> >>Kevin MacClay > >------------------------------------------------------------ >This e-mail has been sent to you courtesy of OperaMail, as a free >service from >Opera Software, makers of the award-winning Web Browser, Opera. Visit us >at >http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free >e-mail >account is waiting at: http://www.operamail.com/ >------------------------------------------------------------ ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------
William Rice wrote: > I do agree that SPL can provide advantages from both the control > and network traffic standpoints. > > I think most of my feelings come from my familiarity > with debugging facilities for C, vs. the debugging facilities I have > seen for stored procedures(none). There might be some out there, > but I have never had an employer who would consider buying such a beast.. > > I will admit I take great comfort in being able to step through my code and > make sure that nothing surprising is happening. I succumb to laziness > pretty easily. > Hey there is nothing wrong with that! Kagel's Second Law of Programming states: Programmers are lazy and should be. Lazy programmers will always write good clean simple code that is easy to maintain because they are too lazy to do things any other way! Art S. Kagel > > Will > >===== Original Message From Paul Watson <PWatson@Lastminute.com> ===== > >We use SPL quite a lot especially in a web environment, there are a couple > >of > >funnies but nothing unsurmountable. Longer term we'll probably consider > >moving it to Java especially as the JVM is now in the engine, > >but we're not in any hurry. > > > >A couple of years we were involved in a project where the front-end was > >in Gupta with an Informix backend. All the SQL, without exception, was done > >in SPL. This significantly reduced the network traffic, and made sure the > >all > >the SQL was optimised and tuned by the DBA team and not by Gupta > >developers!! > > > >Paul Watson # > >WFSoftware Ltd # Things Are Going to > >Tel: +44 1436 674729 # Get a Lot Worse > >Fax: +44 1436 678693 # Before Things Get Worse > >www.wfsoftware.com/informix # > > > >-----Original Message----- > >From: William Rice [mailto:ricew@operamail.com] > >Sent: Monday, April 17, 2000 6:42 PM > >To: Kevin Macclay; informix-list@iiug.org > >Subject: RE: stored procedure vs. prepared statement > > > > > >The first step I would probably take for both performance and reliability is > >upgrade.... > > > >Prepared statements can be used when a program executes a given > >statement multiple times during one given connection. This way the > >query plan for this action is only generated once. > > > >To be honest, I tend to stay away from stored procedures due to > >religious reasons. I have found them useful on occasion, but have > >never really explored their use for a performance benefit. They probably > >can provide a large performance benefit if you can cut down a large > >amount of data transfer from the database to the client by using them. > > > >Hope this helps, > >Will > >>===== Original Message From "Kevin Macclay" <kmacclay@vantage.com> ===== > >>We're running INFORMIX-OnLine Version 7.12.UC1. > >> > >>We're using Informix as a back-end to our web site and wish to improve > >performance between our code and the database. We're currently using > >Statement objects to perform all queries. I have read, however, that it is > >smarter to use PreparedStatements which can be compiled once and > >>re-used without the database having to recompile/re-evaluate the statement. > > > >Does it make sense to use a PreparedStatement for every SQL statement we're > >executing (they're all very basic queries, updates, and inserts -- nothing > >too > >fancy). I've also read about Stored Procedures > >>however, and this has confused me a great deal! When is it correct to use > >a > >PreparedStatement and when is it best to use a Stored Procedure? I read > >somewhere that Stored Procedures aren't necessarily a performance boost and > >are mostly used to encapsulate a few queries that logically > >>fit together. Is this correct? Does it make sense to implement Stored > >Procedures to improve performance with Informix? > >> > >>Any help or insight is greatly appreciated! Thanks in advance. > >> > >>Kevin MacClay > > > >------------------------------------------------------------ > >This e-mail has been sent to you courtesy of OperaMail, as a free > >service from > >Opera Software, makers of the award-winning Web Browser, Opera. Visit us > >at > >http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free > >e-mail > >account is waiting at: http://www.operamail.com/ > >------------------------------------------------------------ > > ------------------------------------------------------------ > This e-mail has been sent to you courtesy of OperaMail, as a free service from > Opera Software, makers of the award-winning Web Browser, Opera. Visit us at > http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail > account is waiting at: http://www.operamail.com/ > ------------------------------------------------------------