Re: Performance problem on client/server

Cosmo <simond@informix.com> wrote: :Billy Wheeler wrote: :> :> In reply to: :> > OPEN select_records_cur using id # id was previously gotten :> > IF SQLCA.SQLCODE = 0 THEN :> > FETCH select_records_cur INTO f_record :> > ....... :> > What I am concerned about is whether or not :> > the above code results in the entire table being loaded to the client :> > and the SQL statement executed on the client. :> :> Not by itself. If you run this statement for one record, it should fetch :> one record and only one record should go down the network. Of course, if you :> run it 60000 times, it will send 60000 records down the network! :-) :Nope. Sorry, but there's more to it than that. There are a couple of :points :to consider here. (Probably only the second applies in this case) :Firstly the OPEN/FETCH of a cursor may do a lot more processing than you :think. The engine may have to process all the data before it can return :any rows at all - EG ORDER BY/MAX/MIN etc. Just fetching a single row :may still be expensive if a lot of work has to be done. This does apply, but isn't a network issue so this part will run with exactly the same speed over a network as directly on the db server. His select will return every row with the given table_id in the order of invoice_date. The engine will do this ordering before any rows are returned. At least on the latest engines I *think* this doesn't require any temporary tables *if* there is an index on table_id, invoice_date in that order. Even if it does however, there will be no difference related to the network. :Secondly there is a buffered fetch system that means a single FETCH from :the front end will (if the row size is << buffer size) result in :multiple :rows being sent be the engine. This can be tuned with FET_BUF_SIZE. This might have some effect, but should it matter as much as he is seeing? May be. The database access where: rieper@ix.netcom.com (Byron Rieper) wrote: : LET f_prepare_stmt = : "SELECT from table where table_id = ? order by invoice_date" : PREPARE select_records FROM f_prepare_stmt : DECLARE select_records_cur CURSOR FOR select_records : and then later on... : OPEN select_records_cur using id # id was previously gotten : IF SQLCA.SQLCODE = 0 THEN : FETCH select_records_cur INTO f_record : ....... Just to make it clear once and for all. Everything you do here is executed by the engine and only the rows with the given table_id is returned via the network to your application - allways - in every case - there is no way anything but that could happen. The PREPARE statement sets up your statement locally inside your program. The DECLARE statement sends this to the database which "compiles" it (including making an execution plan based on the statistics available in the database at this time) and stores it as a named cursor. The OPEN adds the value given by id to the statement and makes it ready for execution. The first FETCH will start the actual execution of the select statement *inside* the engine. If a temporary table is needed for the "order by" it's created at this time. In this case every row with the given value for table_id is read from the table you select from and inserted into the temporary table. The resulting rows in the temporary table are sorted (via one of the sorting mechanisms in the engine) and the first row is returned via the network to your application. If no temporary table is required (the order by can be done via an index) the first row with the given value for table_id is simply selected directly from the table and returned via the network to your application. If there is no index on table_id the engine will do a sequential search on your table to find the right rows. This will still be executed inside the engine though, and only the appropriate rows will be returned over the network. You haven't shown it in your code example, but there have to be more fetch statements (in a loop probably) that return the other rows. (If there are only one row for each table_id (table_id is the primary key) the order by shouldn't have been there.) As these subsequent fetch statements are executed each row is simply returned via the network to your application, either read from the temporary table or directly from the original table read via the index. When the last row with the given table_id has been returned and you execute another fetch, status NOTFOUND will be set and no more data read. (Forgetting to test for this status would be a grave error that would make your program loop forever of course.) You will have to look elsewhere for your performance problems. Appart from the FET_BUF_SIZE parameter that you should look into, there may easily be any number of errors on your network that may lead to very bad performance as you see it. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company