stored procedure vs. prepared statement
This is a multi-part message in MIME format. ------=_NextPart_000_003C_01BFA865.97F8EE20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 ------=_NextPart_000_003C_01BFA865.97F8EE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN"> <HTML> <HEAD> <META content=3Dtext/html;charset=3Diso-8859-1 = http-equiv=3DContent-Type> <META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT color=3D#000000 size=3D2>We're running INFORMIX-OnLine = Version=20 7.12.UC1.</FONT></DIV> <DIV><FONT color=3D#000000 size=3D2></FONT> </DIV> <DIV><FONT size=3D2>We're using Informix as a back-end to our web site = and wish to=20 improve performance between our code and the database. We're = currently=20 using Statement objects to perform all queries. I have read, = however, that=20 it is smarter to use PreparedStatements which can be compiled once and = re-used=20 without the database having to recompile/re-evaluate the = statement. Does=20 it make sense to use a PreparedStatement for every SQL statement we're = executing=20 (they're all very basic queries, updates, and inserts -- nothing too=20 fancy). I've also read about Stored Procedures however, and this = has=20 confused me a great deal! When is it correct to use a = PreparedStatement=20 and when is it best to use a Stored Procedure? I read somewhere = that=20 Stored Procedures aren't necessarily a performance boost and are mostly = used to=20 encapsulate a few queries that logically fit together. Is this=20 correct? Does it make sense to implement Stored Procedures to = improve=20 performance with Informix?</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>Any help or insight is greatly appreciated! = Thanks in=20 advance.</FONT></DIV> <DIV> </DIV> <DIV><FONT color=3D#000000 size=3D2>Kevin = MacClay</FONT></DIV></BODY></HTML> ------=_NextPart_000_003C_01BFA865.97F8EE20--
I doubt that prepared statements would help you much, since statements are prepared once during a run of an application. If you are using a web page, every person will have to prepare the statement, not just one. It is a good idea to use stored procedures, since if code changes, it's much easier to change the stored procedure than to change client code, even for a web page. For example, you have a query that figures out the cost of a product. The product's cost could get computed in several different places in your web site, but the code is always the same. Having a stored procedure do it in all places means that if the algorithm changes, all you have to do is change the procedure and not the code. Stored procedure are compiled, but I've never seen much performance improvement from using them over straight SQL. To improve the performance of the queries involved, take a look at setting PDQPRIORITY to 1, and having somebody 'in the know' take a look at the SQLs being executed. It's very possible that you can get some increase in performance by redesigning the statements. Also, take a look at the configuration of the engine. You could probably post it and I or someone else here could help you with tuning. > > 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 > > ------=_NextPart_000_003C_01BFA865.97F8EE20 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN"> > <HTML> > <HEAD> > > <META content=3Dtext/html;charset=3Diso-8859-1 = > http-equiv=3DContent-Type> > <META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR> > </HEAD> > <BODY bgColor=3D#ffffff> > <DIV><FONT color=3D#000000 size=3D2>We're running INFORMIX-OnLine = > Version=20 > 7.12.UC1.</FONT></DIV> > <DIV><FONT color=3D#000000 size=3D2></FONT> </DIV> > <DIV><FONT size=3D2>We're using Informix as a back-end to our web site = > and wish to=20 > improve performance between our code and the database. We're = > currently=20 > using Statement objects to perform all queries. I have read, = > however, that=20 > it is smarter to use PreparedStatements which can be compiled once and = > re-used=20 > without the database having to recompile/re-evaluate the = > statement. Does=20 > it make sense to use a PreparedStatement for every SQL statement we're = > executing=20 > (they're all very basic queries, updates, and inserts -- nothing too=20 > fancy). I've also read about Stored Procedures however, and this = > has=20 > confused me a great deal! When is it correct to use a = > PreparedStatement=20 > and when is it best to use a Stored Procedure? I read somewhere = > that=20 > Stored Procedures aren't necessarily a performance boost and are mostly = > used to=20 > encapsulate a few queries that logically fit together. Is this=20 > correct? Does it make sense to implement Stored Procedures to = > improve=20 > performance with Informix?</FONT></DIV> > <DIV><FONT size=3D2></FONT> </DIV> > <DIV><FONT size=3D2>Any help or insight is greatly appreciated! = > Thanks in=20 > advance.</FONT></DIV> > <DIV> </DIV> > <DIV><FONT color=3D#000000 size=3D2>Kevin = > MacClay</FONT></DIV></BODY></HTML> > > ------=_NextPart_000_003C_01BFA865.97F8EE20-- > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
Kevin Macclay wrote:We're running INFORMIX-OnLine Version 7.12.UC1. 1) Please do not post HTML/MIME to this list/newsgroup, many readers use the mail gateway and do not use HTML or MIME enabled mail readers. 2) I STRONGLY suggest upgrading your server to AT LEAST 7.31 or even as far as 9.20 for stability, safety, features, and performance reasons. Now to your question: > > 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 = > I don't do ODBC and other high level database APIs for religious reasons (I religiously avoid doing so in favor of ESQL/C ;-) but I assume that a Statement object is just a way of sending SQL text to the database engine. In that case if you will be executing the statement more than once during the course of the application, yes a prepared statement that you repeatedly invoke will perform better as it will only have to optimized once and the query plan will be reused over and over. Except for the simplest single table queries statement preparation takes a significant portion of a statement's execution time. > 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 = SELECT, DELETE, and UPDATE will benefit greatly. INSERTS will only benefit if you are using an insert cursor to PUT rows to a buffer and then insert them in a batch. Otherwise there is no benefit to preparing INSERT statements. > > 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? = > Prior to 7.31 and 9.20 Informix had not statement cache that attempted to share the query plans developed for one user with other users of substantially similar statements. The latest versions have implemented this important feature. Without the statement cache any complex query likely to be executed repeatedly by many users without preparing first would gain greatly from the stored query plan that is saved when a stored procedure is compiled as well as from the stored procedure cache that is part of all IDS 7/8/9 versions. If each user is preparing statements and reusing them repeatedly this benefit is limited. The primary benefit of SPL is to can common multi-statement SQL and very complex SQL that could not be put into a VIEW. > 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. Art S. Kagel