Board index » delphi » TTable Vs TQuery With Paradox on LAN

TTable Vs TQuery With Paradox on LAN

Please feel free to correct any errors.

If I understand correctly, when a TTable is opened, all records from the
connected table are loaded to memory (or cached to the hard drive?).  If a
SetRange command is exicuted on the table, the already obtained records are
filtered according to the range.

Is one reason for Paradox being slow on a network is all records from a
table are send to the 'client' computer whenever a TTable is open?

If a Select..Where.. SQL statement is issued using a TQuery, are all
records loaded to memory, then filtered according to the where clause, or
are only the records that match the query returned across the network?

Which is faster on a local network, using TTable with SetRange, or a
properly designed TQuery with a Select..Where sql statement?

Any  words of wisdom on speeding up Paradox 5 (Delphi 1 and 2) on a local
network?

 

Re:TTable Vs TQuery With Paradox on LAN


Quote
Joe (jnodel...@spinn.com) wrote:

: Please feel free to correct any errors.

:-)

: If I understand correctly, when a TTable is opened, all records from the
: connected table are loaded to memory (or cached to the hard drive?).  If a

No. BDE reads records in larger blocks and caches in memory but not all
the table - only part you are accessing in moment. and probably indexes
are also cached.

: SetRange command is exicuted on the table, the already obtained records are
: filtered according to the range.

No. SetRange works on active indexes - it determines range of valid
records and reads only those when needed.

: Is one reason for Paradox being slow on a network is all records from a
: table are send to the 'client' computer whenever a TTable is open?

We have found paradox fast when using setrange/cancelrange.

try setting bde buffer bigger  (in Idapi.cfg, system page)
ca 1/3 of available ram for minimum buffer
ca 2/3 of available ram for maximum buffer

only thing what makes opening tables slow is referencial integrity - each
constraint opens also master table's corresponding index.

: If a Select..Where.. SQL statement is issued using a TQuery, are all
: records loaded to memory, then filtered according to the where clause, or
: are only the records that match the query returned across the network?

Local SQL can use single field, maintained (and case sensitive ?) indexes
- if any query where condition matches this kind of index only records
matching are read. if none of indexes matches then all records are
scanned.

with client-server TQuery will fetch _all_ records (and stores them in
temporary table) if RecordCount or Last is issued - otherwise it retrieves
record-by-record. not-live result against paradox tables is stored anyway
in temporary table.

: Which is faster on a local network, using TTable with SetRange, or a
: properly designed TQuery with a Select..Where sql statement?

for paradox tables setrange is faster.
for c/s tquery.

: Any  words of wisdom on speeding up Paradox 5 (Delphi 1 and 2) on a local
: network?

more file handles and big buffer for bde (idapi cfg system page(I hope))
with w3.1 file handles in autoexec must be increased.

tanel

Re:TTable Vs TQuery With Paradox on LAN


Hello Tanel,

You sad that  local SQL (against Paradox tables) creates temporary table
for non live queries. So what do you think works faster live query  
(filter against table itself) or non live (BDE copy data to temp table)
for query which return SMALL and BIG result set. (I dont need live
dataset, by the way)

You oppinion is highly appreciated
Thank you

Alex Roytman
royt...@smart.net

Quote
Tanel Hiir wrote:

> Joe (jnodel...@spinn.com) wrote:

> : Please feel free to correct any errors.

> :-)

> : If I understand correctly, when a TTable is opened, all records from the
> : connected table are loaded to memory (or cached to the hard drive?).  If a

> No. BDE reads records in larger blocks and caches in memory but not all
> the table - only part you are accessing in moment. and probably indexes
> are also cached.

> : SetRange command is exicuted on the table, the already obtained records are
> : filtered according to the range.

> No. SetRange works on active indexes - it determines range of valid
> records and reads only those when needed.

> : Is one reason for Paradox being slow on a network is all records from a
> : table are send to the 'client' computer whenever a TTable is open?

> We have found paradox fast when using setrange/cancelrange.

> try setting bde buffer bigger  (in Idapi.cfg, system page)
> ca 1/3 of available ram for minimum buffer
> ca 2/3 of available ram for maximum buffer

> only thing what makes opening tables slow is referencial integrity - each
> constraint opens also master table's corresponding index.

> : If a Select..Where.. SQL statement is issued using a TQuery, are all
> : records loaded to memory, then filtered according to the where clause, or
> : are only the records that match the query returned across the network?

> Local SQL can use single field, maintained (and case sensitive ?) indexes
> - if any query where condition matches this kind of index only records
> matching are read. if none of indexes matches then all records are
> scanned.

> with client-server TQuery will fetch _all_ records (and stores them in
> temporary table) if RecordCount or Last is issued - otherwise it retrieves
> record-by-record. not-live result against paradox tables is stored anyway
> in temporary table.

> : Which is faster on a local network, using TTable with SetRange, or a
> : properly designed TQuery with a Select..Where sql statement?

> for paradox tables setrange is faster.
> for c/s tquery.

> : Any  words of wisdom on speeding up Paradox 5 (Delphi 1 and 2) on a local
> : network?

> more file handles and big buffer for bde (idapi cfg system page(I hope))
> with w3.1 file handles in autoexec must be increased.

> tanel

Re:TTable Vs TQuery With Paradox on LAN


Quote
Alex Roytman (royt...@smart.net) wrote:

: Hello Tanel,
:
: You sad that  local SQL (against Paradox tables) creates temporary table
: for non live queries. So what do you think works faster live query  
: (filter against table itself) or non live (BDE copy data to temp table)
: for query which return SMALL and BIG result set. (I dont need live
: dataset, by the way)

huhh, I really don't know. I never tried it with paradox tables because
live query against paradox tables is very limiting -
you are not allowed to use ORDER BY, joins, SUM, MAX ...
(take look to delphi help for TQuery.RequestLive property - there should
be listed these requirements)

we have found that QBE or SQL is slow when many fields are included in
result set - we always try to make queries so that result set returns only
key value(s) (primary or some secondary key) and then link ttable or
tquery with all needed fields when displaying or printing.  it usally
works faster because BDE does not have to copy huge amount of data to
temporary table (temp table is eighter in RAM or on disk - depending how
much memory is available).

on the forms where we need extensive filtering we use also one tquery
which returns primary key value (ORDER BY can be on other field(s)) and
then link TTable component to it. then we have two navigators - one for
moving - it is linked to query and one for editing - it is linked to
ttable.  when user moves query steps to next record and table follows it.
when user inserts or delets record then we do query.close/open to refresh
it (maybe we could use live query here but we always have some meaningful
ORDER BY clause - we cannot use live query)

we also have found that QBE performs a bit faster (and does not have some
bugs that SQL does (mainly IN or multiple OR conditions)).  and with QBE
you can execute queries that in SQL need subselect statement and that is
not supported by 16 bit BDE (don't know about 32 bit one).

tanel

Other Threads