hello,
i use delphi xe2 with the current edb version.
in my application all lists bind an edb-dataset to a grid (via a tdatasource). mostly in master/detail-mode.
some lists have grown over the time so there is some annoying waiting involved for the user.
the edb-dataset is always an tedbtable since the user is allowed to edit the shown record.

how can i speed up this scenario?

i was thinking of loading data asynchronously and 'pump' it to the grid ... does anyone have experience with this? what is a good way to do this?
is this possible at all with edb? if not, a workaround would probably be to server the data in bigger portions from the edb-server? i did a simple join between 2 tables involved in a master/detail-setup in edbmgr and it also took 60-80s.
this is the result of the execution plan (the row-size seems really big):

Source Tables
-------------

t_samples (s): 17607 rows
t_sampleparameters (sp): 135658 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Joins
-----

The driver table was the t_samples (s) table

The t_samples (s) table was joined to the t_sampleparameters (sp) table with the
inner join expression:

"s"."id" = "sp"."sample__id"

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the t_sampleparameters (sp) table:

"sp"."sample__id" = "s"."id"

Index scan (T_SAMPLEPARAMETERS.FK_SAMPLES_ID-SAMPLE__ID)


Result set I/O statistics
-------------------------

Total rows visited: 135658

Row buffer manager

Max buffer size: 1020.25KB Buffer size: 308MB

Hits: 135658   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 616   written: 616MB

Index Page buffer manager

Max buffer size: 2MB Buffer size: 1.21MB

Hits: 344772   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 5   written: 2.26MB

BLOB Block buffer manager

Max buffer size: 2MB Buffer size: 43.21MB

Hits: 182704   Misses: 0   Hit ratio: 1

Reads: 0   read: 0B

Writes: 88   written: 86.41MB

================================================================================
135658 row(s) returned in 66.219 secs
================================================================================

happily looking for inspiration ...

Hendrik,

<< some lists have grown over the time so there is some annoying waiting involved for the user. >>
the edb-dataset is always an tedbtable since the user is allowed to edit the shown record.

how can i speed up this scenario? >>

To start, you should stop executing a join query across so many rows without a WHERE clause to filter the result set.  If you look at the execution plan, you'll see that EDB is having to write out a 600+MB result set to a temporary table, which is why the query is taking so long to execute.

If you can post the actual SQL that you're using, I can show you a way to execute the same query with correlated sub-queries so that the query can return a sensitive result set and be executed instantaneously.  You can see this technique here:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

Tim Young
Elevate Software
www.elevatesoft.com

thank you tim,
this is really kind. the actual setup in the application is master/detail directly on the 2 tedbtables. in my naive thinking i was trying to simulate what could be the minimum time to load these lists. after the initial loading the master/detail works reasonably well.