How long does a query take

Hi everyone, Performance again. When measuring performance of your database accessed via ODBC, what is regarded as "query execution time": a) SQLPrepare + SQLExecute b) SQLPrepare + SQLExecute + first SQLFetch c) SQLPrepare + SQLExecute + all SQLFetch's I currently measure a), but found it to be a bit fast sometimes. b) is often more realistic but messes up my good stats :-), and c) is out of the question - users shouldn't complain about performance when they select millions of rows. It really boils down to how ODBC really works. Does the SQLExecute function actually retrieve all rows into memory somewhere, ie. is the query completed from Informix's point of view? /* ----------------------------------------------------- * * Willem Roos wroos@shoprite.co.za * * roosj@mweb.co.za * * 0(+27)21 980 4941 * * 0(+27)21 919 0198 * * ----------------------------------------------------- */

Willem Roos wrote: > > Hi everyone, > > Performance again. When measuring performance of your database accessed > via ODBC, what is regarded as "query execution time": > > a) SQLPrepare + SQLExecute > b) SQLPrepare + SQLExecute + first SQLFetch > c) SQLPrepare + SQLExecute + all SQLFetch's > > I currently measure a), but found it to be a bit fast sometimes. b) is > often more realistic but messes up my good stats :-), and c) is out of > the question - users shouldn't complain about performance when they > select millions of rows. > > It really boils down to how ODBC really works. Does the SQLExecute > function actually retrieve all rows into memory somewhere, ie. is the > query completed from Informix's point of view? The ODBC library itself only physically fetches one row at a time, however, the ODBC driver and the database engine can do whatever they want under the hood. Informix's FETCH handling normally only actually executes the query when the first fetch occurs, but, there is an environment variable and an equivalent global variable that causes the engine to execute the query at the time the cursor is OPENed, equivalent to the ODBC SQLExecute. Also communication protocol normally brings a full buffer of data, regardless of how many rows that is as long as a single row is smaller than the comm. buffer. The default comm buffer is 4K, but again through environment and global variables the library can request that the engine use any buffer size from 4K to 32K. The engine fills the next buffer ONLY while the application is processing the previous buffer it does not complete the entire query. Balance all this with the realization that if the query requires any sorting or temp tables the entire original query will be completed to the temp table before the first row is returned but the temp table rows will be returned as described above. So, in conclusion, you have to decide what constitutes a 'complete transaction' to you and your users and you have to determine how your ODBC driver is setting up the comm protocols with the engine. Art S. Kagel