Re: Prepared SQL Statements and Performance

In article <4f089t$8b5@vixc> "Michael D. Mabin" <culbin@vixa.voyager.net> writes: >From: "Michael D. Mabin" <culbin@vixa.voyager.net> >Subject: Prepared SQL Statements and Performance >Date: 3 Feb 1996 18:07:57 GMT >The Informix SQL reference manuals state that using a prepared SQL statement >together with an >Execute statement makes the SQL more efficient. If this is true, then which >example is more >efficient: >declare cursor1 cursor for > select col1, col2, col3 > from tablea > where col1 = col5 >or >let l_SQL = "select col1, col2, col3 ", > "from tablea ", > "where col1 = col5" >prepare SQL_stmt from l_SQL >declare cursor1 cursor for SQL_stmt >Note that I did not use an execute in this example. Which of the aboove >examples yields the best >performance and why? The second statement should be faster: 1) How many times it is executed? The more times you execute the query, the faster the second one will be. The engine has to parse the code only once. You also get a performance increase in some cases if you declare the cursor WITH HOLD. 2) Once the statement is prepared, the engine knows what information is needed. Therefore, less overhead is required each time. Michel Behna - mbehna@promus.com AMA #453810 - DoD #1821 - Suzuki Katana 600