Filters slowing access to DB depending on value. Why?

[SercoSteve]

Can anyone think of any reason why filtering a field within a substantial DB with a particular value causes access to that DB to grind to a halt (very slow update to Form Table Frames). Other filter values that return a similar number of matches do not seem to slow access at all.

Thanks in Advance

Steve

 

[Langley]

Filters have a LOT of overhead. Consider using setRange instead. Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

SercoSteve,

Expanding on Langley's answer...

Filters are essentially queries run in the background. To determine the next record to display, e.g. the next one that matches the filter, Paradox has to run a query each time.

Suppose, for example, you have a tableframe grid that displays 20 records. Paradox will need to run 20 queries to populate that initially and then will need to run additional queries to fill that as you navigate through it.

While queries are, by and large, great tools, they have a certain amount of overhead associated with them. Paradox needs to fetch the entire table from wherever it's stored, scan through each record, copy matching ones to a temporary table, sort the results, and then clean up when finished.

If you have a lot of records or a very large table structure, this can lead to a temendous amount of network traffic.

Filters are slightly different. Because they require indexes, Paradox no longer has to:

1. Copy the entire table; it can copy just the matching index values.

2. Scan every record. Indexes are already sorted, so Paradox can simply ignore the records out of range and jump straight to the matches.

3. Sort the final results (see the previous note).

So, while filters require more setup work and are more restrictive in the criteria they allow, they provide a temendous performance benefit--especially when the tables are stored on a server, rather than a single-user machine.

Even if you can use filters for part of the process, you can save yourself some overhead. however, this may mean rethinking your overall strategy.

hope this helps...

-- Lance