Informix Timing Analysis

I was asked to put together some comparisons of various ways of running statements. I thought it might be useful to some of you also. Both of the types of problems I'll describe below involve 2 different ways of writing the same statement. You'll see that in each case there is clearly a preferable way to write the code. Single Select Statements When you need to run 1 query to get a value from the database(e.g. to lookup a value), the 2 methods are: 1) During initialization Prepare the select statement, declare the cursor Within the main code Open cursor, fetch cursor, close cursor 2) During initialization Prepare the select statement Within the main code execute the statement method #2 above is 35% faster Insertions of Multiple Records We have a number of scenarios in which 1 insert statement needs to be executed multiple times to insert values into a table. We do this by inserting question marks(?) into the statement and then running the statement passing in values for the question marks. For example, insert into tablename values (?, '?') execute statement using :program_variable1, :program_variable2 There are also two ways of doing this. 1) During initialization Prepare the insert statement Within the main code begin work execute the statement x times commit work 2) During initialization Prepare the insert statement, declare the cursor Within the main code begin work execute a PUT cursor statement x times commit work Method #2 keeps the pending row in a local buffer to be passed to the database at commit time or when the buffer fills up. It was 20% faster on average. Also, it was faster no matter how many inserts were done between the begin and commit, even if only 1. I tested using between 1 and 100 inserts. Mundane Details For all of these tests I ran 2500 iterations of the particular statements to get a reasonable average. Also, I dropped and rebuilt the database to flush whatever data was in cache. These time improvements are elapsed time on an empty system. If you've got any questions on this subject, feel free to get in touch with me. David Samson David Samson, Senior Database Researcher dsamson@ndc.co.il NDS Technologies Israel Ltd. +972 2-589-4529 PO Box 23012 Fax: +972 2-589-4578 Jerusalem, Israel