🚀 go-pugleaf

Frederic,
	I'm not sure how much you know about Access so I'm not going to make any
assumptions.  First, make sure that the Table in Access has a LongInteger
or Autonumber primary key (avoid textual keys!); second, make sure that the
table is indexed for ALL link fields; thirdly, if you are using a TQuery,
make sure that your SQL returns a recordset that has one of the key/indexed
fields returned within it.

Now, the way Access does it.  Access uses Rushmore Query Technology which
allows it to return (for Dynasets or modifiable sets of records) just the
primary or indexed key value that uniquely identifies a row - if one is not
available, it creates one or returns the whole row (ssllooww).  Access then
populates the visible rows on the screen while retrieving the remainder of
the rows in the background.   Access's way APPEARS to be faster to the user
but actually takes relatively longer to return the complete recordset due
to the additional processing involved.  Delphi has to get the whole thing
THEN begin to display.  The reason for this is that TQuery returns a
complete recordset based on an SQL string that may include cached editable
rows.  (At least that is my understanding) 

Access and VB get at the data through the Access Compatibility Layer which
Delphi has not, to my knowledge, been afforded uh... access to.  MS
Programs are intimately compatible with themselves.

On your master/detail form, the recordset object must be requeried,
refreshed and redisplayed which has to go through all the levels (both
Delphi objects, ODBC and Access DLL's) back to the data.

Besides tweaking your recordset objects in Delphi and making sure that
there are unique indexed keys in access, I can't think of anything else
that would make the process faster.  I'm going to have to do some research
to see if I can improve data retrieval times with Delphi using Access
databases - then I'll share my results.  :)
-- 

=======================================
codifex@airmail.NOSPAM.net
bgravitt@vha.NOSPAM.com
=======================================

Fr�d�ric GUILLIEN <fguillien@hol.fr> wrote in article
<352B6ED7.6DC@hol.fr>...
> Hi,
> 
> Opening an ACCESS table with Master/Detail links is very very slow.
> Without the M/D link, the table open correctly : it's acceptable, but
> when I set the link, it's awfully slow !
> 
> Do you know where the machine hang with this. I tried the same link with
> Access and it works well. With VB also. 
> 
> As I forced to choose Delphi to create this part of the program, I don't
> want the app to be slower than VB first module I developed last year...
> 
> I hope there is an easy solution.
> 
> -- 
> Fr�d�ric GUILLIEN
> 
> Here is my Web site. Yet all in french (sorry, time missing...)
> http://wwwperso.hol.fr/~fguillei
> 
> Show your support for the Best Windows Development Tool
> Join TDAG (The Delphi Advocacy Group)
> http://home1.gte.net/mikep/tdag.html
> 

A big thanks for your answer !

I learn a lot reading you. 

My fields are all indexed the good way (I think). 

I have made a search in the VCL source code and it appears that it is
dbiOpenCursor that is the slowest call (30 seconds). And not only with
M/D relations, but for each opening. My slowest table has many fields
(30-40) but only 3 records !

I'll study your answer a little more and try again.

Thanks again 

Fr�d�ric GUILLIEN

VHA PC Development wrote:
> 
> Frederic,
>         I'm not sure how much you know about Access so I'm not going to make any
> assumptions.  First, make sure that the Table in Access has a LongInteger
> or Autonumber primary key (avoid textual keys!); second, make sure that the
> table is indexed for ALL link fields; thirdly, if you are using a TQuery,
> make sure that your SQL returns a recordset that has one of the key/indexed
> fields returned within it.
> 
> Now, the way Access does it.  Access uses Rushmore Query Technology which
> allows it to return (for Dynasets or modifiable sets of records) just the
> primary or indexed key value that uniquely identifies a row - if one is not
> available, it creates one or returns the whole row (ssllooww).  Access then
> populates the visible rows on the screen while retrieving the remainder of
> the rows in the background.   Access's way APPEARS to be faster to the user
> but actually takes relatively longer to return the complete recordset due
> to the additional processing involved.  Delphi has to get the whole thing
> THEN begin to display.  The reason for this is that TQuery returns a
> complete recordset based on an SQL string that may include cached editable
> rows.  (At least that is my understanding)
> 
> Access and VB get at the data through the Access Compatibility Layer which
> Delphi has not, to my knowledge, been afforded uh... access to.  MS
> Programs are intimately compatible with themselves.
> 
> On your master/detail form, the recordset object must be requeried,
> refreshed and redisplayed which has to go through all the levels (both
> Delphi objects, ODBC and Access DLL's) back to the data.
> 
> Besides tweaking your recordset objects in Delphi and making sure that
> there are unique indexed keys in access, I can't think of anything else
> that would make the process faster.  I'm going to have to do some research
> to see if I can improve data retrieval times with Delphi using Access
> databases - then I'll share my results.  :)
> --
> 
> =======================================
> codifex@airmail.NOSPAM.net
> bgravitt@vha.NOSPAM.com
> =======================================
> 
> Fr�d�ric GUILLIEN <fguillien@hol.fr> wrote in article
> <352B6ED7.6DC@hol.fr>...
> > Hi,
> >
> > Opening an ACCESS table with Master/Detail links is very very slow.
> > Without the M/D link, the table open correctly : it's acceptable, but
> > when I set the link, it's awfully slow !
> >
> > Do you know where the machine hang with this. I tried the same link with
> > Access and it works well. With VB also.
> >
> > As I forced to choose Delphi to create this part of the program, I don't
> > want the app to be slower than VB first module I developed last year...
> >
> > I hope there is an easy solution.
>