Subject Streaming Records with an "Insert Into Select From" statement
Author lec_sas
I have an issue regarding sql statements in the following format:
(Please note that I am using the embeded fbclient.dll and all my
code is in ansi C).

Insert into <Table and field Criteria>
Select <Select criteria>
Where <Where Clause>

This statement when executed will be taking several hours every time
it is executed and the Where claus is defined by users so it will
almost never be consistent. The Size of the data will also never be
consistent. What I wished to do was create a framework that would
provide feedback to the user about the progress of the sql
execution. This feed back would include the # of records found, the
rate the records are being found so the user can cancel and modify
the where claus if it is going to slow etc....

The only way I could find to allow this kind of feedback is to
Execute the Select and Insert statements seperately. I create a
producer-Consumer model with two threads, 1 thread executing update
statements (1 record at a time), the other executing the select
statement (fetching 1 record at a time). The buffer I used for this
producer-consumer is simply 300 XSQLDA structures that are built
after the select statement is prepared. The fetch call would take
XSQLDA[x] as a parameter and then the update execute statement would
take XSQLDA[x] as an input parameter.(next would be XSQLDA[x+1]
etc.) This would work since the numer of fields in the select
statement matches the number of parameters in the update statement.
"Select Type, Name, Model from Car"
"Insert into request (type, name, Model) values (?,?,?)"
Since I am using the parameters here, I dont have to make a call
to "prepare" for every call to "execute".

My question is even executing at this low of a level, what kinds of
problems can this cause with regards to performance, also would
there be better ways to implement this idea using other features of
the database.

Thank You
Darin Amos