I have a problem when using nested querys. The problem is that rather simple
prepared querys cosume a lot of time and memory when they're excuted inside
a loop resulting from other query with many rows.

This happens only the first time the inside query is executed. I suspect
that all rows from the outer query are retrieved from server at this point,
wich is unaceptable for us.

Here's some ilustrative code (I'm using Delhi 3 with latest BDE):

procedure TForm1.Button1Click(Sender: TObject);
begin
with query1.sql do
    begin
    clear;
    add('select * from table1');
    end;
with query2.sql do
    begin
    clear;
    add('select * from table2');
    add('where prikey=:code');
    end;
query1.open;         // returns about 100k rows. Exec time=1s Memory
ussage=0
query2.prepare;      // returns 1 row each time is executed
while not query1.eof do
    begin
    query2.params[0].asstring:=query1.fields[2].asstring;
    query2.open;          // first time exec time=120s, memory=200Mb !!!
    // here I do someting with query1 and query2 results
    query2.close;
    query1.next;
    end;
query2.unprepare;
query1.close;
end;

We use same code with Interbase and Informix, both working ok.

How can i prevent this behaviour? Please, we're in a hurry!

Thank you very much!

Pablo,

Your suspicions are correct. SQL Server does not allow simultaneous open
cursors. This is just a limitation in the SQL Server design and there's
nothing you can do about it. To work around the problem, the BDE reads and
caches the first cursor when you open the second cursor.

Your only options are to try and limit the result set in the first cursor
(perhaps do the processing in smaller sets?), or to join the two queries
into one if that is possible.

Regards,
Danny.

news:3c56719d_1@dnews...