Board index » delphi » SQL 6.5 slow with two query open same time

SQL 6.5 slow with two query open same time

I am using odbc connection.

I am using Tdatabase and create a virtual BDE alias.
all my datasets use this virtual BDE alias.
When I try o open two query, without closing the first one , First is open
normally,
When I try to open second query it hooks a while if the result of first
query huge.

what is the problem?

thanks to idea.

 

Re:SQL 6.5 slow with two query open same time


This is because the server must send the results of the first query to the
client before it can process the second.  This is an old issue and one that
does not have a simple solution.

Quote
"Hseyin Altun" <hal...@posltd.com.tr> wrote in message

news:8a7rnu$8qm5@bornews.borland.com...
Quote
> I am using odbc connection.

> I am using Tdatabase and create a virtual BDE alias.
> all my datasets use this virtual BDE alias.
> When I try o open two query, without closing the first one , First is open
> normally,
> When I try to open second query it hooks a while if the result of first
> query huge.

> what is the problem?

> thanks to idea.

Re:SQL 6.5 slow with two query open same time


Is it same in other SQL servers or MSSQL 7?

Quote
BlueGI wrote in message <8a8bsk$f...@bornews.borland.com>...
>This is because the server must send the results of the first query to the
>client before it can process the second.  This is an old issue and one that
>does not have a simple solution.

>"Hseyin Altun" <hal...@posltd.com.tr> wrote in message
>news:8a7rnu$8qm5@bornews.borland.com...
>> I am using odbc connection.

>> I am using Tdatabase and create a virtual BDE alias.
>> all my datasets use this virtual BDE alias.
>> When I try o open two query, without closing the first one , First is
open
>> normally,
>> When I try to open second query it hooks a while if the result of first
>> query huge.

>> what is the problem?

>> thanks to idea.

Re:SQL 6.5 slow with two query open same time


Huseyin,

    From what I understand, it is a "problem" with all SQL servers the BDE
supports.  What you can do is after you open the first query, issue the
FetchAll method.

Query1.Open;
Query1.FetchAll;

Also, keep the result set as small as possible.  Be very specific about what
data is returned back to the client (let the server do the work).
Another method is use multiple TDatabase components to create multiple
(independent) connections.

Good luck,
krf

Re:SQL 6.5 slow with two query open same time


No, it's a problem with MSSQL only. Works great with Interbase, SQL Anywhere
and DB2.
FetchAll downloads the whole resultset, so it will freeze your thread until
finished and if not small, it will take time.
The problem is that MSSQL client by default tries to download whole dataset
in the background, so it slows down all subsequent queries until finished.
With BDE/ODBC DSN you can't change this. Try with ADO.

--
----------------------
Regards
Robert Cerny
Remove both qwe when replying
email: robert.qwe.ce...@neosys.xrs.qwe.si

No questions via email, unless explicitly invited.

Quote
Kevin Frevert wrote in message <38cd0aaa@dnews>...
>Huseyin,

>    From what I understand, it is a "problem" with all SQL servers the BDE
>supports.  What you can do is after you open the first query, issue the
>FetchAll method.

>Query1.Open;
>Query1.FetchAll;

>Also, keep the result set as small as possible.  Be very specific about
what
>data is returned back to the client (let the server do the work).

>Another method is use multiple TDatabase components to create multiple
>(independent) connections.

>Good luck,
>krf

Other Threads