Board index » delphi » cached updates + filter: bad performance !

cached updates + filter: bad performance !

Bill_T...@compuserve.com.nospam (Bill Todd (TeamB)) wrote:

Quote
>It is generally not a good idea to filter queries.  Since filters are
>applied locally the entire result set must be retrieved from the
>server to filter the records.  Try using the WHERE clause of the query
>to get just the records you want.
>Bill

Interesting ... How about the case where a stored proc is returning a
data set?   This is also true?    We're in Delphi C/S 3 against Oracle
7.3

I started off with a Query with a complex Where clause, that was a
bottleneck in the performance of the application.    I resoved the
first condition of the where clause as several Views on the server,
then resolved the next set of conditions with several StoredProcs, and
then resolved the remaining conditions in the dataset OnFilterRecord
handler.

Am just now begining to test this for performance against various
sized datasets.     My thinking is that the user will be able to
change some of the conditions of the statement ( the ones resolved in
the filter ), without the need to go back out to the server.   I a
small returned dataset, it works fine.  As a the returned dataset
grows larger, several hundred records or more, the filter processing
time becomes a problem.

Maybe it's just my lack of fluency with PL/SQL, but I find the stored
proces more work to implement, and more difficult to maintain, as the
complexity increases...  

What generalizations might one make about the use of BDE filters in a
CS environment?

Ana Maria Gonzalez
Amherst MA
a...@soupinc.com

 

Re:cached updates + filter: bad performance !


I have a very big problem !

When I use the cache update component with a TQuery object the performance
drops very fast as soon I edit a record. The more records I edit the slower
Delphi gets.
This only seems to happen when a filter is applied to the query.

For the first edit it takes 60 ms to put a record in the edit mode and
change its value in the cache. After ten or more changes it can take
seconds (2,3, 4, .. etc. )
 to edit a record !

Also to jump to a bookmark (multiselect !) gets very slow after editing a
number of records ! The same
when I scroll to my Query result.

In this way the performance is unacceptable !!

What am I doing wrong ?

Or is it the Borland BDE who messes up all ?!

Who can advise me ??? I have to filter locally, so reopening the query with
a new where clausule is not a option !

Ger Otten

Re:cached updates + filter: bad performance !


Quote
Ger Otten wrote:

> I have a very big problem !

> When I use the cache update component with a TQuery object the performance
> drops very fast as soon I edit a record. The more records I edit the slower
> Delphi gets.
> This only seems to happen when a filter is applied to the query.

> For the first edit it takes 60 ms to put a record in the edit mode and
> change its value in the cache. After ten or more changes it can take
> seconds (2,3, 4, .. etc. )
>  to edit a record !

> Also to jump to a bookmark (multiselect !) gets very slow after editing a
> number of records ! The same
> when I scroll to my Query result.

> In this way the performance is unacceptable !!

> What am I doing wrong ?

> Or is it the Borland BDE who messes up all ?!

> Who can advise me ??? I have to filter locally, so reopening the query with
> a new where clausule is not a option !

The advice, Ger, is "somehow, someway, DON'T use filters."  With this
combination, fire up SQL Monitor (if you have it) and you will probably
see the shocking cause of your performance problems.

Re:cached updates + filter: bad performance !


It is generally not a good idea to filter queries.  Since filters are
applied locally the entire result set must be retrieved from the
server to filter the records.  Try using the WHERE clause of the query
to get just the records you want.

Bill

(Sorry but TeamB cannot answer support questions received via email.)
(To send me email for any other reason remove .nospam from my address.)

Other Threads