Board index » off-topic » Re: Nested Dataset laoding abnormally slow

Re: Nested Dataset laoding abnormally slow


2005-03-08 05:52:05 AM
off-topic5
I have a very similar problem for a while.
What is the best method for setting up master/detail connections.
Connecting through TQueries is very good in a lot of aspects,
but how to avoid the master dataset to iterate through all the records
on open?
Joe what did you do?
Regards
Joseph Sprei írta:
Quote
I have three tables, 1. names, 2. orders, 3. items I set them up nested to
each other through tquery.

The items table is very big over a million records.
When attaching to the Tqueries directly the speed is fine, however when
setup through datasnap, the names table takes around a minute to load, when
packet record is set to 5.

I do not understand! Why does it work so slow to load 5 records from the
master table?

I hooked up dbgrids on the server module, to see what goes during load time,
and what I saw was, the master table creeps from record to record, I guess
it loads the detail data into the master, but it still should not take that
long. (5 seconds for each record).

Maybe I am pushing the cds over its limits, what are the limits? and what
database solution should I use?


 
 

Re:Re: Nested Dataset laoding abnormally slow

May be you could use dbExpress components (TSQLDataSet) instead of
TQueries. I am not sure though if this will solve the problem, but I am
sure that if the problem is the iteration of the dataset then this will
not happen with TSQLDataSet
 

Re:Re: Nested Dataset laoding abnormally slow

I'm already using dbExpress components, so that's the case.
The problem occurs when you use master/detail relationships through
parameterized queries because this way during the opening all the master
records will be iterated, so all the detail records will be fetched.
Kostas Terzides írta:
Quote
May be you could use dbExpress components (TSQLDataSet) instead of
TQueries. I am not sure though if this will solve the problem, but I am
sure that if the problem is the iteration of the dataset then this will
not happen with TSQLDataSet
 

{smallsort}

Re:Re: Nested Dataset laoding abnormally slow

Attila Fulop wrote:
Quote
I'm already using dbExpress components, so that's the case.

The problem occurs when you use master/detail relationships through
parameterized queries because this way during the opening all the master
records will be iterated, so all the detail records will be fetched.

Kostas Terzides írta:

>May be you could use dbExpress components (TSQLDataSet) instead of
>TQueries. I am not sure though if this will solve the problem, but I
>am sure that if the problem is the iteration of the dataset then this
>will not happen with TSQLDataSet
May be you can set FetchDetailsOnDemand and explicitly call FetchDetails
from client side. Again, I am not sure if this improves speed
 

Re:Re: Nested Dataset laoding abnormally slow

How many master records does the master query SELECT return?
--
Bill Todd (TeamB)
TeamB cannot answer questions received via email
Attila Fulop wrote:
Quote
The problem occurs when you use master/detail relationships through
parameterized queries because this way during the opening all the master
records will be iterated, so all the detail records will be fetched.
 

Re:Re: Nested Dataset laoding abnormally slow

~3-400 at the time
Bill Todd írta:
Quote
How many master records does the master query SELECT return?

 

Re:Re: Nested Dataset laoding abnormally slow

That's it!!
Thanks a lot!
I'm really happy. :))
I already used that option 3 years ago, but since then I simply forgot.
Thanks again,
Best regards
Kostas Terzides írta:
Quote
Attila Fulop wrote:

>I'm already using dbExpress components, so that's the case.
>
>The problem occurs when you use master/detail relationships through
>parameterized queries because this way during the opening all the
>master records will be iterated, so all the detail records will be
>fetched.
>
>Kostas Terzides írta:
>
>>May be you could use dbExpress components (TSQLDataSet) instead of
>>TQueries. I am not sure though if this will solve the problem, but I
>>am sure that if the problem is the iteration of the dataset then this
>>will not happen with TSQLDataSet

May be you can set FetchDetailsOnDemand and explicitly call FetchDetails
from client side. Again, I am not sure if this improves speed
 

Re:Re: Nested Dataset laoding abnormally slow

Either reduce the number of master records you are selecting or make the
user click a button if they want to see the detail records and only select
the details for the current master.
--
Bill Todd (TeamB)
TeamB cannot answer questions received via email
Attila Fulop wrote:
Quote
~3-400 at the time

Bill Todd írta:
>How many master records does the master query SELECT return?
>
 

Re:Re: Nested Dataset laoding abnormally slow

Thanks Bill, it might be a good idea, but imagine eg. if you'd have to
click a button at each message in your news reader in order to fetch the
body of messages. My case would be similar if I'd use this.
The solution was to add poFetchDetailsOnDemand to the provider's options
before opening the dataset, and remove it after open.
Bill Todd írta:
Quote
Either reduce the number of master records you are selecting or make the
user click a button if they want to see the detail records and only
select the details for the current master.