Re: Prepared SQL Statements and Performance

In article <4f089t$8b5@vixc> culbin@vixa.voyager.net "Michael D. Mabin" writes: > 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 > I have often seen code where cursors are declared within loops so the same cursor is declared each time the loop executes. This certainly is *highly* inefficient as it should almost *always* be possible to declare the cursor (once!) before the loop. The only case I can think of is where the cursor which is declared depends on the data which is present in the loop - note, not that the same cursor uses different data in each iteration! DECLARE cursor1 FOREACH cursor1... DECLARE cursor2 FOREACH cursor2 END FOREACH END FOREACH # horrid!!! DECLARE cursor1 DECLARE cursor2 FOREACH cursor1 FOREACH cursor2 END FOREACH END FOREACH # better Now many folks would suggest that 'CURSOR1' should be set up so as to remove the need for 'CURSOR2' but I think that sometimes it's much easier to get the correct functionality using the nested cursors - and sometimes it's faster too! -- ============================================================================ Sally Woolrich | This mail contains my personal sally@excelsis.demon.co.uk | views not those of my employer! ============================================================================