Board index » delphi » Referential Integrity - Paradox Tables - Team B

Referential Integrity - Paradox Tables - Team B

Ever since upgrading from D1 to D3 my application has suffered from a lack
of speed - well over the past few days some interesting fact unveiled
themselves thanks to some further investigation and help from John Trenton
and Reastlack
1)  Degradation in speed occurs if not all pas files are recompiled -
delete *.dcu and build all
2)  Degradation in speed also occurs if Local Share is set to TRUE in the
BDE - even though you are not using a network. The degradation in my app
varies from 50 - 80% depending on what I am doing.
3)  My app uses referential integrity rather extensively (52 tables, 36 val
files 4mb exe and 1.9mb DLL). My app is an mdi app and I create the bulk of
my forms when required and destroy them on completion, during the creation
of the forms I activate the tables I require - in some instances this may
be 1 table but in other it could be up to 10, depending on what is
required. In some of my form I have a Master, Master-Detail, and
Master-Detail-Detail requirement. By deleting the val files I had a 300%
improvement when opening the exact same form when the val files were in
place versus when the weren't and that was with local share set to FALSE.
With it set to TRUE and across the network the improvement was around 700%.
Once the form was open and the tables created the speed difference related
to the removal of val files was negligible. my D1 app did not use ref integ
as much as when I improved it in D3

Here are the problems
1) I need some form of referential integrity
2) I need to run my app across the network so I must have Local Share set
to true

The best way to get my speed back is to eliminate Referential Integrity
Does anyone have a surefire way of being able to eliminate the uses of Ref
Int in Paradox but still give me the result I need (eg if I edit my
Customer ID - which is my primary index -  from JOE BLOGGS to JON HANcock
it needs to rattle its way through all transactions etc relating to JOE
BLOGGS and readdress them to JON HANcock - dont forget this needs to work
across a network so using something that locks a table is out)

Help would be appreciated and Team B and INPRISE if you are watching were
you aware of this and what can be done about it. I can recall reading
somewhere that Bill Todd (Team B) doesn't like using Ref Int but I think it
was in relation to problems repairing any corruption to val files

--
Don Cooper
skyli...@ozemail.com.au

 

Re:Referential Integrity - Paradox Tables - Team B


Quote
>The best way to get my speed back is to eliminate Referential Integrity
>Does anyone have a surefire way of being able to eliminate the uses of Ref
>Int in Paradox but still give me the result I need (eg if I edit my
>Customer ID - which is my primary index -  from JOE BLOGGS to JON HANcock
>it needs to rattle its way through all transactions etc relating to JOE
>BLOGGS and readdress them to JON HANcock - dont forget this needs to work
>across a network so using something that locks a table is out)

You can handle this in code.  You need to do your checking in the Master tables
beforePost event.   The detail tables linked by the masterSource property will
still reflect the value of the original linking key value.   You can iterate
throug each of the detail tables checking for ones that have at leas one record.
Check if the Master key matches the detail key.  If it doesn't you can loop
through the detail records changing the key and posting it until the detail
table has no records Eof and Bof will be true.

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Re:Referential Integrity - Paradox Tables - Team B


Here's a thought.
I don't know exactly how ref integrity works but follow me on this
For referential integrity to work fast and effectively an index on the
relevant field is required (which when setting up ref integ this occurs)
and the relevant tables must be open (and in the same directory, according
to what I have read)
Lets say for argument sake I have a Customer table and 4 different
transaction tables that as part of the transaction requires the Customer ID
- When editing the Customers on my customer form I only open the Customer
table and because I don't need access to my transaction tables for this
operation I dont have them on my customer form and I don't open them
anywhere else.
If I edit my Customer ID, for Ref Integ to work I assume the transaction
tables must be open for them to change - I don't open the tables in my
application so the BDE must do it automatically. Therefore when I load my
customer form even though I am only opening 1 table the BDE opens all other
relevant tables in the background so that it can maintain referential
integrity - that why my application is soooo sllloooowwwww in opening forms
with the .val files in existence - What an overhead but logically
necessary.

Any comments ?

 Don Cooper
 skyli...@ozemail.com.au

Re:Referential Integrity - Paradox Tables - Team B


Quote
Don Cooper wrote:
> /...........
> relevant tables in the background so that it can maintain referential
> integrity - that why my application is soooo sllloooowwwww in opening forms
> with the .val files in existence - What an overhead but logically
> necessary.

> Any comments ?

I guess that the overhead of Referential Intergrity is unavoidable. If
you want integrity in your data you have to pay the price of processor
time checking all when you edit or insert data. I'm sure that if you use
a SQL server as Interbase or MS SQL server you could have better
perforfomance because maybe of better algoritms and a better data model
than Pdox. In my case we are developing an app basically for managing
client accounts, and selling and we are going to develop it in SQL by
this reason.

Cheers

--
Jose Sebastian Battig

Important:

Please, when replying to me delete the "STOPSPAM-"
part of the "reply to" e-mail address.

Por favor cuando me contestes usando "reply" borra
la parte de la direccion que dice "STOPSPAM-".

  vcard.vcf
< 1K Download

Other Threads