Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread MasterDetail or Filtering?
Tue, Apr 24 2007 5:50 AMPermanent Link

"Santy Concepción"
Hi!

I'm using DMISAm 4.25 and Delphi 7

With MasterDetail relation, I can't index the Detail table with a secondary
index, because the master/detail relation disappears.
So I'm thinking about make the relation filtering Detail table on the
AfterScroll event of Master table.

Is it a good idea? Is Filtering slower than Master/Detail relations? Will it
improve the speed on C/S or filesharing mode or will it be slower?

Thanks!

Tue, Apr 24 2007 6:13 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Santy

>With MasterDetail relation, I can't index the Detail table with a secondary
>index, because the master/detail relation disappears.
>So I'm thinking about make the relation filtering Detail table on the
>AfterScroll event of Master table.

There's no reason why you shouldn't have secondary indices. You can only use one in the master/detail relationship if the first field is the one that satisfies the link but there's nothing stopping you.

>Is it a good idea? Is Filtering slower than Master/Detail relations? Will it
>improve the speed on C/S or filesharing mode or will it be slower?

It is slower. IIRC Master/Detail does the equivalent of setting a range which is about as fast as you can get for DBISAM (also explains the need for the first field in the index to be the link).

Using SQL might well be faster overall for C/S since whist the select might be a bit slower there is less data to be transferred. For fileserver its not going to have that advantage.

Roy Lambert
Tue, Apr 24 2007 7:46 AMPermanent Link

adam
The key to speed is to SELECT from your detail table on as "clean" an index as possible.
Ideally an Integer index on a single field.

i.e. if you ask

SELECT * FROM Orders WHERE CustomerNum = 1234

and
- there is an index on CustomerNum
- CustomerNum is a simple Integer

DBISAM will be very, very fast.

--

How you build the APP is up to you.

Personally, I use the AfterScroll of my "Master" Query to trigger SQL of the above form on
my "Child" Query. This is very fast and reliable. It is also easy to extend if you get
more complex relationships.


Wed, Apr 25 2007 3:38 AMPermanent Link

Chris Erdal
adam <adam@nospamplease.fmfoods.co.uk> wrote in
news:FFA9B0B4-49F2-4494-8701-C2A7EF9A060E@news.elevatesoft.com:

> Personally, I use the AfterScroll of my "Master" Query to trigger SQL
> of the above form on my "Child" Query. This is very fast and reliable.
> It is also easy to extend if you get more complex relationships.

I set a timer in the AfterScroll, and have the timer re-open the Child
query after about 300 ms. That allows faster master grid scrolling.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)
Wed, Apr 25 2007 5:07 AMPermanent Link

"Santy Concepción"
Hi, Roy...

Thanks for your answer.

My DetailTable has various indexes. The first field of that table is the
link for MasterTable.
DetailTable has other indexes. There is not Primary index. All of them are
single indexes.

If can make the master/detail 'link', but if I change the index in runtime,
the 'link' disappears and no data is shown.
What am I doing wrong?


"Roy Lambert" <roy.lambert@skynet.co.uk> escribió en el mensaje
news:1F6078E0-8FFA-4A90-82A3-10EF59AE0EFE@news.elevatesoft.com...
> Santy
>
>>With MasterDetail relation, I can't index the Detail table with a
>>secondary
>>index, because the master/detail relation disappears.
>>So I'm thinking about make the relation filtering Detail table on the
>>AfterScroll event of Master table.
>
> There's no reason why you shouldn't have secondary indices. You can only
> use one in the master/detail relationship if the first field is the one
> that satisfies the link but there's nothing stopping you.
>
>>Is it a good idea? Is Filtering slower than Master/Detail relations? Will
>>it
>>improve the speed on C/S or filesharing mode or will it be slower?
>
> It is slower. IIRC Master/Detail does the equivalent of setting a range
> which is about as fast as you can get for DBISAM (also explains the need
> for the first field in the index to be the link).
>
> Using SQL might well be faster overall for C/S since whist the select
> might be a bit slower there is less data to be transferred. For fileserver
> its not going to have that advantage.
>
> Roy Lambert
>

Wed, Apr 25 2007 6:10 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Santy


Can you mail me something or post to the binaries so I can have a look?

Roy Lambert
Wed, Apr 25 2007 8:48 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Santy,

<< Is it a good idea? Is Filtering slower than Master/Detail relations? >>

Filters are slower than ranges, but only in the sense that a Porsche might
be slower than a Ferrari. Smiley If the master-detail link is for visual
purposes, or is simply a one-off execution, then you won't notice the
difference at all.  The only time you might notice a difference is if you
execute the filter thousands of times and evaluate the total time difference
in aggregate.

<< Will it improve the speed on C/S or filesharing mode or will it be
slower? >>

You probably won't notice a whole lot of difference either way compared to a
range.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Apr 25 2007 11:21 AMPermanent Link

Chris Erdal
"Santy Concepción" <santyweb@hotmail.com> wrote in
news:4F78C6CD-BC26-44E0-88C7-7CEE61C8C790@news.elevatesoft.com:

> Hi, Roy...
>
> Thanks for your answer.
>
> My DetailTable has various indexes. The first field of that table is
> the link for MasterTable.
> DetailTable has other indexes. There is not Primary index. All of them
> are single indexes.
>
> If can make the master/detail 'link', but if I change the index in
> runtime, the 'link' disappears and no data is shown.
> What am I doing wrong?
>

If you make compound indexes for these other indexes with the master-link
field first and the other field second then you shouldn't lose the link
when you re-open on another index.

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 3 + EDB 1.00 build 6)
Image