PREPARED inserts?

I have read that preparing SQL that is to be used repeatedly in a loop can increase the run time of a program because the overhead the optimizer must do is only done once. This makes sense for updates and deletes, but what about inserts? Is there anything to be gained by using PREPARE/EXECUTE with inserts?

You really should do some benchmarking of your own, but the benchmarking I have done in the past has suggested that there is no benefit in preparing insert statements. There's not much point in the engine having to nut out a query plan in advance (which is an over-simplified way of describing what a prepare does). And that seems to reflect in the bottom line. Alnis Bajars. alnisb@colorcorp.com.au Top Cat <cstefanick@ocsmgmt.com> wrote in message news:821e9n$hcd$1@nnrp02.primenet.com... > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts? > >

Top Cat wrote: > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts? Most definitely. PREPARE does more than generate an execution plan. It also parses and validates the statement. While there is no execution plan to be generated for the INSERT statement, there clearly are tasks related to parsing and validating (e.g Is the SQL syntax correct? does the table exist? Are the columns right? ). CPU Costs (at the Informix Engine) for a "PREPARE once-EXECUTE many" INSERT statement can reduce to 25% of costs of a plain-vanilla INSERT. In other words, you could get a 300% improvement in performance (depending on where your bottleneck in your system is). Of course, the improvement would vary depending on the structure of the table (number/type of columns, number of indexes, fragmentation). Rudy

I would guess that you won't get much performance benefit from preparing insert statements; however there are other benefits to preparing your statements. The biggest benefit is that when you prepare statements, the engine parses and deals with the SQL. When you don't prepare them, your embedded language compiler does some of the parsing, etc., for you. So as the engine gets upgraded to newer versions, if there are performance enhancements or SQL fixes, you can get the benefits without having to recompile your program if you're using prepared statements. If you're embedding the sql directly in the language, you might not get these benefits. Just my $.02 - Tom Girsch TJG Technical Services "Top Cat" <cstefanick@ocsmgmt.com> wrote in message news:821e9n$hcd$1@nnrp02.primenet.com... > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts? > >

Top Cat wrote: > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts? It depnds on how you write your statements. If you prepare: INSERT INTO SomeTable VALUES("a", 1, ...); Then you gain nothing by prepare and execute; you'd do better with EXECUTE IMMEDIATE. On the other hand, if you prepare: INSERT INTO SomeTable VALUES(?,?,...); You can then execute this statement many times with different values to be inserted. This is much more effective. Similar comments apply to UPDATE and DELETE; unless you are using placeholders (those question marks) and variables to provide the values, then you usually get minimal benefit from using PREPARE and EXECUTE. An exception would be when you really execute exactly the same statement many times. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Top Cat wrote: > > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts? There is some slight gain from PREPARing INSERT statements but it is generally minor. HOWEVER, using the PREPAREd statement to open an INSERT CURSOR and using PUT to insert rows using the cursor in a loop will make a BIG difference. As long as you do not have to FLUSH the INSERT CURSOR after every row to get back a serial number the fact of having each PUT buffered locally and then a batch of inserts flushed to the server together can make a significant difference. If you are inserting to parent and child rows and there are multiple children per parent then even if you have to flush after each parent to get the serial number for the related children being able to batch the child rows alone will speed things up. Art S. Kagel

Jonathan Leffler wrote: > Top Cat wrote: > > .... > > It depnds on how you write your statements. If you prepare: > > INSERT INTO SomeTable VALUES("a", 1, ...);> > Then you gain nothing by prepare and execute; you'd do better with EXECUTE > IMMEDIATE. > On the other hand, if you prepare: > > INSERT INTO SomeTable VALUES(?,?,...);> > You can then execute this statement many times with different values to be > inserted. This is much more effective. Similar comments apply to UPDATE and > DELETE; unless you are using placeholders (those question marks) and variables > to provide the values, then you usually get minimal benefit from using PREPARE > and EXECUTE. An exception would be when you really execute exactly the same > statement many times. I'm interested in this "exception" that you talk about in you last line. Can you explain further? > > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN > #include <disclaimer.h>

Hopefully, this isn't stretching this thread too much... I happened to be involved in performance testing of a slightly more complex nature. However, as the test bed was built to allow any combination of transaction types, I could, quite easily, carry out a comparitive study of the costs involved with the various approaches suggested. The results seemed interesting enough to post Test Results (seconds) Test Type Real time Process cpu Informix cpu Simple Insert 64.50 4.48 54.56 Prepare once, Execute many 22.57 3.85 14.51 Declare-Open once, PUT many, FLUSH many 18.66 3.96 11.41 Stored Procedure 43.47 5.20 33.60 Prepare many, Execute many 124.74 17.47 98.96 Environment Server : HP 9000/778, 1cpu, 384Mb RAM Informix : 7.31UC2 Instance : BUFFERS=20,000; Single disk. Test Details Table : Row size ~550 bytes, 16 columns Number of Indexes : One (primary key) No of rows inserted : 10,000 Parallelization : Single-user Other activity : None Number of runs : multiple (to confirm results) Test Method Step 1 : Recreate Test database Step 2 : Checkpoint instance (onmode -c) Step 3 : Initialize instance statistics (onstat -z) Step 4 : Run Test to completion (record timex results) Step 5 : Checkpoint instance Step 6 : Record "onstat -p" results Statistics gathered Output of timex (to assess costs of program runnable) Output of "onstat -p" (to assess Informix activity required to support runnable) HTH Rudy Top Cat wrote: > I have read that preparing SQL that is to be used repeatedly in a loop can > increase the run time of a program because the overhead the optimizer must > do is only done once. This makes sense for updates and deletes, but what > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > inserts?

Rudy Fernandes wrote: > Jonathan Leffler wrote: > > On the other hand, if you prepare: > > > > INSERT INTO SomeTable VALUES(?,?,...);> > > > You can then execute this statement many times with different values to be > > inserted. This is much more effective. Similar comments apply to UPDATE and > > DELETE; unless you are using placeholders (those question marks) and variables > > to provide the values, then you usually get minimal benefit from using PREPARE > > and EXECUTE. An exception would be when you really execute exactly the same > > statement many times. > > I'm interested in this "exception" that you talk about in you last line. Can you > explain further? Not really. I don't have any good examples at hand. However, consider a 'ticket counter' table where you insert a row into a table with a serial column and nothing else. CREATE TABLE TicketCounter (TicketNumber SERIAL NOT NULL UNIQUE); You can then consider whether you get any benefit out of preparing: INSERT INTO TicketCounter VALUES(0) DELETE FROM TicketCounter Here, the actual statements are fixed, so preparing the insert will save some processing each time you need a new ticket number, and preparing the delete will save some processing each time you clean up the table. They have to be separately prepared and executed, of course, as otherwise you will lose the information on the inserted row number (which is the whole point of the exercise). But this is a rather extreme, and hence exceptional, situation. In general, you don't insert exactly the same data values into a customer table for every single customer who registers with your web-based store, for example -- at least, not if you wish to remain in business. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>

While I understand there is no speed advantage to preparing a statement and executing it in this scenario, we have used that syntax to do this: let sql_stmnt = "create table ", "V", tab_number using "<<<<<<<<&" prepare create_table_stmnt from sql_stmnt execute create_table_stmnt While that goal can now be accomplished in 7.30 with: execute immediate sql_stmnt this syntax is not available in rds 4.10, and the syntax execute create_table_stmnt crashes in 7.30. How can we achieve the goal of supporting 4.10 clients and 7.30 clients off of the same code base? (I've got a bad feeling about this.) (Just so there's no yelling: rds 7.30uc1 on linux 2.2.5 (rh6.0) against IDS 7.30UC7-1 on same machine, and rds 4.10ue2 on SCO openserver 5.0.5 against SE 5.00ud1 on same machine) In article <38454BCC.AE478429@earthlink.net>, Jonathan Leffler <jleffler@earthlink.net> wrote: > > > Top Cat wrote: > > > I have read that preparing SQL that is to be used repeatedly in a loop can > > increase the run time of a program because the overhead the optimizer must > > do is only done once. This makes sense for updates and deletes, but what > > about inserts? Is there anything to be gained by using PREPARE/EXECUTE with > > inserts? > > It depnds on how you write your statements. If you prepare: > > INSERT INTO SomeTable VALUES("a", 1, ...);> > Then you gain nothing by prepare and execute; you'd do better with EXECUTE > IMMEDIATE. > On the other hand, if you prepare: > > INSERT INTO SomeTable VALUES(?,?,...);> > You can then execute this statement many times with different values to be > inserted. This is much more effective. Similar comments apply to UPDATE and > DELETE; unless you are using placeholders (those question marks) and variables > to provide the values, then you usually get minimal benefit from using PREPARE > and EXECUTE. An exception would be when you really execute exactly the same > statement many times. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > > Sent via Deja.com http://www.deja.com/ Before you buy.

One possibility is for your application to check its environment to determine which version of Informix it is running against (fgl_getenv, I believe) and modify its behaviour, where required, through IF statements. Rudy rds_weasel@my-deja.com wrote: > While I understand there is no speed advantage to preparing a statement > and executing it in this scenario, we have used that syntax to do this: > let sql_stmnt = "create table ", "V", tab_number using "<<<<<<<<&" > prepare create_table_stmnt from sql_stmnt > execute create_table_stmnt > While that goal can now be accomplished in 7.30 with: > execute immediate sql_stmnt > this syntax is not available in rds 4.10, and the syntax > execute create_table_stmnt > crashes in 7.30. > How can we achieve the goal of supporting 4.10 clients and 7.30 clients > off of the same code base? (I've got a bad feeling about this.) > > (Just so there's no yelling: rds 7.30uc1 on linux 2.2.5 (rh6.0) against > IDS 7.30UC7-1 on same machine, and rds 4.10ue2 on SCO openserver 5.0.5 > against SE 5.00ud1 on same machine) >