Board index » off-topic » BDE performance

BDE performance


2004-09-20 08:42:13 PM
off-topic10
A query SELECT is very rapid, a query UPDATE also. Nevertheless, a query
UPDATE with a subquery SELECT is very slow. Why?
Is it possible to change the way of updating the tables without he executes
the subquery for each record?
 
 

Re:BDE performance

It is impossible to answer database questions when you do not tell us
what database you are using.
It is impossible to answer questions about a SQL statement when you do
not show us the SQL statement.
--
Bill (TeamB)
TeamB cannot answer questions received via email
Jesus Cabrero wrote:
Quote
A query SELECT is very rapid, a query UPDATE also. Nevertheless, a
query UPDATE with a subquery SELECT is very slow. Why?
Is it possible to change the way of updating the tables without he
executes the subquery for each record?
 

Re:BDE performance

Sorry...
I work with Paradox 5 for Windows and BDE 5.11..
"Jesus Cabrero" < XXXX@XXXXX.COM >escribi?en el mensaje
Quote
A query SELECT is very rapid, a query UPDATE also. Nevertheless, a query
UPDATE with a subquery SELECT is very slow. Why?
Is it possible to change the way of updating the tables without he
executes
the subquery for each record?


 

{smallsort}

Re:BDE performance

That answers one of my two questions. What is the SQL statement that is
slow?
--
Bill (TeamB)
TeamB cannot answer questions received via email
Jesus Cabrero wrote:
Quote
I work with Paradox 5 for Windows and BDE 5.11..
 

Re:BDE performance

We use TXQuery, its a replacement for TQuery with much better performance.
You can get it here:
www.sigmap.com/txquery.htm
Timothy Armstrong
"Jesus Cabrero" < XXXX@XXXXX.COM >wrote in message
Quote
A query SELECT is very rapid, a query UPDATE also. Nevertheless, a query
UPDATE with a subquery SELECT is very slow. Why?
Is it possible to change the way of updating the tables without he
executes
the subquery for each record?


 

Re:BDE performance

SELECT SUM(Units) FOR Table2 WHERE Table1.Code=Table2.Code is very fast (1
second)
UPDATE Table1 SET Total=0 is very fast (1 second)
UPDATE Table1 SET Total=(SELECT SUM(Units) FOR Table2 WHERE
Table1.Code=Table2.Code)
is very slow (90 seconds for same 1000 records in Table1 and 10000 records
in Table2, but SubQuery as 1000 records or less).
"Bill Todd" < XXXX@XXXXX.COM >escribi?en el mensaje
Quote
That answers one of my two questions. What is the SQL statement that is
slow?

--
Bill (TeamB)
TeamB cannot answer questions received via email

Jesus Cabrero wrote:

>I work with Paradox 5 for Windows and BDE 5.11..
 

Re:BDE performance

Jesus Cabrero wrote:
Quote
SELECT SUM(Units) FOR Table2 WHERE Table1.Code=Table2.Code is very
fast (1 second)

UPDATE Table1 SET Total=0 is very fast (1 second)

UPDATE Table1 SET Total=(SELECT SUM(Units) FOR Table2 WHERE
Table1.Code=Table2.Code)
is very slow (90 seconds for same 1000 records in Table1 and 10000
records in Table2, but SubQuery as 1000 records or less).
This UPDATE is slow because it uses a correlated subquery. The SELECT
must be executed once for each row in table1.
What database are you using?
--
Bill (TeamB)
TeamB cannot answer questions received via email
 

Re:BDE performance

Jesus Cabrero wrote:
Quote
SELECT SUM(Units) FOR Table2 WHERE Table1.Code=Table2.Code is very
fast (1 second)

UPDATE Table1 SET Total=0 is very fast (1 second)

UPDATE Table1 SET Total=(SELECT SUM(Units) FOR Table2 WHERE
Table1.Code=Table2.Code)
is very slow (90 seconds for same 1000 records in Table1 and 10000
records in Table2, but SubQuery as 1000 records or less).
This is a correlated subquery. That is, the subquery must be executed
once for each row in table1. That is why it is slow.
Quote

"Bill Todd" < XXXX@XXXXX.COM >escribis en el mensaje
news:41542ad0$ XXXX@XXXXX.COM ...
>That answers one of my two questions. What is the SQL statement
>that is slow?
>
>--
>Bill (TeamB)
>TeamB cannot answer questions received via email
>
>Jesus Cabrero wrote:
>
>>I work with Paradox 5 for Windows and BDE 5.11..
 

Re:BDE performance

This it is my conclusion, but why he(she) does not execute the subquery of
Table2 and updates Table1 with the result? It be that it to be suggested to
whom it(he,she) should correspond(fit).
 

Re:BDE performance

It is not possible to execute the subselect once because the value of
Table1.Code in the WHERE clause of the select is different for each row
in table1.
--
Bill (TeamB)
TeamB cannot answer questions received via email
Jesus Cabrero wrote:
Quote
This it is my conclusion, but why he(she) does not execute the
subquery of Table2 and updates Table1 with the result? It be that it
to be suggested to whom it(he,she) should correspond(fit).
 

Re:BDE performance

Hi all.
We are installing a new software in our network whose connection in Oracle
Database is done by BDE.
When BDE is connecting by network, it makes BROADCAST to find the Oracle
Database?
I tested the software in a point to point network and it works without
problem. When I tried to connect in bigger network, the performance was very
very bad.
Can anybody give me a hint, please?
Thanks a lot,
Patricia Sousa