Board index » delphi » SLOW response to a query using D3 C/S and SQL 6.5

SLOW response to a query using D3 C/S and SQL 6.5

This is very weird:

Im building an app that executes this query in a table that has 40000
rows and 55 fields:

SELECT max(<integer field>)
FROM <table>
WHERE <char(3) field> = 'abc'

When I execute this query using ISQW_w, the response comes in 2 or 3
seconds but when I execute it using a TQuery inside a Delphi app, it
takes 3,5 MINUTES.  I even tried to put this query in a stored procedure
and access it with a TStoredProcedure component but it was no better.

Something also very bad:  When we started to develop this app, this
table used to have 20 to 200 lines.  At that time, we used TQuery
components with the property RequestLive = True and SQL = select * from
<table> and insert, post, cancel and edit methods and the app used to
have good responses.  The database is growing very fast and the bigger
it gets, the slower the app becomes. The urgent solution was using
TQuery components with SQL insert and update statements.  I refuse to
believe that I can't use the above methods in a table just because it
has 40000 lines and growing.

Thanks for all suggestions.

 

Re:SLOW response to a query using D3 C/S and SQL 6.5


The query should execute just as fast from within a TQuery as from ISQW_w
(whatever that is) as long as the BDE uses pass-thru SQL.  Have you cranked
up SQL Monitor to check what is really going on?  Make sure that RequestLive
is False because this should not be an updateable result set.  For a good
test, plunk a TDatabase and a TQuery on form (nothing else), and then
activate the query at design-time.

V/R
Russell L. Smith

Quote
Marcus Martins wrote in message <35FE695C.F04A...@nospam.hotmail.com>...
>When I execute this query using ISQW_w, the response comes in 2 or 3
>seconds but when I execute it using a TQuery inside a Delphi app, it
>takes 3,5 MINUTES.  I even tried to put this query in a stored procedure
>and access it with a TStoredProcedure component but it was no better.

Re:SLOW response to a query using D3 C/S and SQL 6.5


Hi

If ISQW_w returns a response in about 2 o 3 seconds, but the same query is
very, very slow when executed inside a Delphi application, then the reason
is very clear : The BDE tries to manage the connections to the MS-SQL
Server (as one single connection cannot be shared between multiple querys),
so when you execute your second query, and you have another active query
(for example, a SELECT * FROM BIG_TABLE ...), then the BDE, very cleverly,
READS ALL the rows from the first Query, just to free the connection, and
use it for the new query ... The developers of the BDE are autentic
"genius" !

In my experience in trying to develop Delphi applications to MS-SQL Server,
I expending more time resolving BDE related problems, than developing the
logic of the application. Its a pitty.

Try to assign the second query to a different DataBase component.

Good Luck.

Marcus Martins <marcusvic...@nospam.hotmail.com> escribi en artculo
<35FE695C.F04A...@nospam.hotmail.com>...

Quote
> This is very weird:

> Im building an app that executes this query in a table that has 40000
> rows and 55 fields:

> SELECT max(<integer field>)
> FROM <table>
> WHERE <char(3) field> = 'abc'

> When I execute this query using ISQW_w, the response comes in 2 or 3
> seconds but when I execute it using a TQuery inside a Delphi app, it
> takes 3,5 MINUTES.  I even tried to put this query in a stored procedure
> and access it with a TStoredProcedure component but it was no better.

> Something also very bad:  When we started to develop this app, this
> table used to have 20 to 200 lines.  At that time, we used TQuery
> components with the property RequestLive = True and SQL = select * from
> <table> and insert, post, cancel and edit methods and the app used to
> have good responses.  The database is growing very fast and the bigger
> it gets, the slower the app becomes. The urgent solution was using
> TQuery components with SQL insert and update statements.  I refuse to
> believe that I can't use the above methods in a table just because it
> has 40000 lines and growing.

> Thanks for all suggestions.

Re:SLOW response to a query using D3 C/S and SQL 6.5


Yes, this is a classical problem with the BDE. In an application having many
open tables its very hard to predict when you will get this problem. One
solution is of course to use several TDataBase:es but it will also add the
overhead of loggin in to SQL Server on every open. We have developed a
TQuery replacement to get around this problem. It only works with SQL Server
and does not use the BDE.  Our SQLQuery component is a TDataset descendant
and allocates connections under programmer control, you can use one common
connection for all updates, and small lookup tables (they are locally
cached) and let those realy large result sets have one connection each.

Im just developing a client application using both very small and very
large tables and it will use only one connection to the server without any
performance problems. (the program will be run by several hundreds of users
concurrently so it was important to minimize the number of connections).

If you are interested just look at http://component-store.com

Thomas Werner
Component Store Ltd.

Lluis Olle skrev i meddelandet <01bde1b5$817e2520$7f01a8c0@p200>...

Quote
>Hi

>If ISQW_w returns a response in about 2 o 3 seconds, but the same query is
>very, very slow when executed inside a Delphi application, then the reason
>is very clear : The BDE tries to manage the connections to the MS-SQL
>Server (as one single connection cannot be shared between multiple querys),
>so when you execute your second query, and you have another active query
>(for example, a SELECT * FROM BIG_TABLE ...), then the BDE, very cleverly,
>READS ALL the rows from the first Query, just to free the connection, and
>use it for the new query ... The developers of the BDE are autentic
>"genius" !

>In my experience in trying to develop Delphi applications to MS-SQL Server,
>I expending more time resolving BDE related problems, than developing the
>logic of the application. Its a pitty.

>Try to assign the second query to a different DataBase component.

>Good Luck.

Other Threads