Board index » delphi » Paradox trouble: lookup/calculated fields + transactions

Paradox trouble: lookup/calculated fields + transactions

Hi,

I have an interesting problem. I use Delphi 4 Pro and Paradox 7
for developing a database application. The trouble is that if
I use lookup or calculated fields with transactions the program
starts to slow down as the transaction goes on. I start a transaction
and start to add records to a table which have lookup/calculated
fields. The first record is added within no time. To add the 5th
record takes nearly a half second. To add the 10th record takes
nearly one second. To add the 20th record takes about two seconds.
And so on.  (and here Celeron 500 processor used, 128 MB RAM,
etc.) Why? What is the solution to prevent this? Local share
is false. What else can be wrong? Can I somehow restructure the
database to avoid this effect?

Here is a small example. There is a company. It sells products
which are stored in a store (store table). They are identified by
ProductNo. The sold products are stored in another table (sold
table). They are identified by AccountNo and ProductNo (complex
key).

STORE table
ProductNo - primary key
Cathegory
Type
Price

SOLD table
AccountNo - first part of primary key
ProductNo - second part of primary key
Quantity

The point is that when the user sells things he can't add
records to the sold table like "35 (AccontNo), 11 (ProductNo),
5 (Quantinty)". He probably don't know the Product's number,
and don't want to use a calculator to count sums (not to mention
that he don't want to find out the prices manually). Instead he wants
to see and add the records like this: "35 (AccountNo), 11 (ProductNo),
Camera (Cathegory), Didigtal camcorder TVR-125E (Types), $1000
(Price), 5 (Qantity), $5000 (Total Price)". For this it seems I must use
lookup/calculated fields. On the other hand he must be undo the selling
at any time. For this the most suitable way is transactions. When he
wants to cancel it (because the buyer changed his mind, or the user
accidentally add wrong record, etc.) simply call Rollback and that's all.
But these two things don't want to work together. What is the solution?

Any answer would be appreciated.
Thank you very much in advance.

Attila Ambrozai
Hungary
am...@ambro.hu

 

Re:Paradox trouble: lookup/calculated fields + transactions


I have this trouble too.
But since I use Calculated field only, my program go faster!
try to use calculated field only. for lookup use onCalculate with FindKey.

"Attila Ambrozai, Jr." <am...@ambro.hu> wrote in message
news:8j7a35$q4r1@bornews.borland.com...

Quote
> Hi,

> I have an interesting problem. I use Delphi 4 Pro and Paradox 7
> for developing a database application. The trouble is that if
> I use lookup or calculated fields with transactions the program
> starts to slow down as the transaction goes on. I start a transaction
> and start to add records to a table which have lookup/calculated
> fields. The first record is added within no time. To add the 5th
> record takes nearly a half second. To add the 10th record takes
> nearly one second. To add the 20th record takes about two seconds.
> And so on.  (and here Celeron 500 processor used, 128 MB RAM,
> etc.) Why? What is the solution to prevent this? Local share
> is false. What else can be wrong? Can I somehow restructure the
> database to avoid this effect?

> Here is a small example. There is a company. It sells products
> which are stored in a store (store table). They are identified by
> ProductNo. The sold products are stored in another table (sold
> table). They are identified by AccountNo and ProductNo (complex
> key).

> STORE table
> ProductNo - primary key
> Cathegory
> Type
> Price

> SOLD table
> AccountNo - first part of primary key
> ProductNo - second part of primary key
> Quantity

> The point is that when the user sells things he can't add
> records to the sold table like "35 (AccontNo), 11 (ProductNo),
> 5 (Quantinty)". He probably don't know the Product's number,
> and don't want to use a calculator to count sums (not to mention
> that he don't want to find out the prices manually). Instead he wants
> to see and add the records like this: "35 (AccountNo), 11 (ProductNo),
> Camera (Cathegory), Didigtal camcorder TVR-125E (Types), $1000
> (Price), 5 (Qantity), $5000 (Total Price)". For this it seems I must use
> lookup/calculated fields. On the other hand he must be undo the selling
> at any time. For this the most suitable way is transactions. When he
> wants to cancel it (because the buyer changed his mind, or the user
> accidentally add wrong record, etc.) simply call Rollback and that's all.
> But these two things don't want to work together. What is the solution?

> Any answer would be appreciated.
> Thank you very much in advance.

> Attila Ambrozai
> Hungary
> am...@ambro.hu

Re:Paradox trouble: lookup/calculated fields + transactions


On Mon, 26 Jun 2000 12:19:12 +0200, "Attila Ambrozai, Jr."

Quote
<am...@ambro.hu> wrote:
>I have an interesting problem. I use Delphi 4 Pro and Paradox 7
>for developing a database application. The trouble is that if
>I use lookup or calculated fields with transactions the program
>starts to slow down as the transaction goes on.

Are you using Cached Updates?

Jan

Re:Paradox trouble: lookup/calculated fields + transactions


Thank you very much! I will try this way.

Attila

Quote
Iwan Haryadi <send2i...@telkom.net> wrote in message news:39584578@dnews...
> I have this trouble too.
> But since I use Calculated field only, my program go faster!
> try to use calculated field only. for lookup use onCalculate with FindKey.

> "Attila Ambrozai, Jr." <am...@ambro.hu> wrote in message
> news:8j7a35$q4r1@bornews.borland.com...
> > Hi,

> > I have an interesting problem. I use Delphi 4 Pro and Paradox 7
> > for developing a database application. The trouble is that if
> > I use lookup or calculated fields with transactions the program
> > starts to slow down as the transaction goes on. I start a transaction
> > and start to add records to a table which have lookup/calculated
> > fields. The first record is added within no time. To add the 5th
> > record takes nearly a half second. To add the 10th record takes
> > nearly one second. To add the 20th record takes about two seconds.
> > And so on.  (and here Celeron 500 processor used, 128 MB RAM,
> > etc.) Why? What is the solution to prevent this? Local share
> > is false. What else can be wrong? Can I somehow restructure the
> > database to avoid this effect?

> > Here is a small example. There is a company. It sells products
> > which are stored in a store (store table). They are identified by
> > ProductNo. The sold products are stored in another table (sold
> > table). They are identified by AccountNo and ProductNo (complex
> > key).

> > STORE table
> > ProductNo - primary key
> > Cathegory
> > Type
> > Price

> > SOLD table
> > AccountNo - first part of primary key
> > ProductNo - second part of primary key
> > Quantity

> > The point is that when the user sells things he can't add
> > records to the sold table like "35 (AccontNo), 11 (ProductNo),
> > 5 (Quantinty)". He probably don't know the Product's number,
> > and don't want to use a calculator to count sums (not to mention
> > that he don't want to find out the prices manually). Instead he wants
> > to see and add the records like this: "35 (AccountNo), 11 (ProductNo),
> > Camera (Cathegory), Didigtal camcorder TVR-125E (Types), $1000
> > (Price), 5 (Qantity), $5000 (Total Price)". For this it seems I must use
> > lookup/calculated fields. On the other hand he must be undo the selling
> > at any time. For this the most suitable way is transactions. When he
> > wants to cancel it (because the buyer changed his mind, or the user
> > accidentally add wrong record, etc.) simply call Rollback and that's
all.
> > But these two things don't want to work together. What is the solution?

> > Any answer would be appreciated.
> > Thank you very much in advance.

> > Attila Ambrozai
> > Hungary
> > am...@ambro.hu

Re:Paradox trouble: lookup/calculated fields + transactions


Yes, I do. But that is required for transactions, isn't it? Does the
cached updates effect on the speed of the executing of certain
database methods?

Attila

Quote
Jan Sprengers <j...@nospamplease.adm2000.be> wrote in message

news:3958555a.3604322@forums.inprise.com...
Quote
> On Mon, 26 Jun 2000 12:19:12 +0200, "Attila Ambrozai, Jr."
> <am...@ambro.hu> wrote:

> >I have an interesting problem. I use Delphi 4 Pro and Paradox 7
> >for developing a database application. The trouble is that if
> >I use lookup or calculated fields with transactions the program
> >starts to slow down as the transaction goes on.

> Are you using Cached Updates?

> Jan

Re:Paradox trouble: lookup/calculated fields + transactions


On Tue, 27 Jun 2000 22:49:04 +0200, "Attila Ambrozai, Jr."

Quote
<am...@ambro.hu> wrote:
>Yes, I do. But that is required for transactions, isn't it?

No. They're two very different things with different usages.

You use cached updates to allow the user to interactively change
several records with the option to save or cancel the changes
afterwards.  Because changes are not reflected in the database until
you apply them, this uses some kind of optimistic locking.

Transactions are used to ensure database consistency.  They are
normally not used during user interaction because the changes are
directly reflected into the DB and they keep all records locked that
take part in the transaction.  Here pessimistic locking is used.

Normally cached updates are applied in the context of a transaction to
ensure that either all updates are applied or none.

Quote
>Does the cached updates effect on the speed of the executing
>of certain database methods?

Indeed it does.  The reason is that Lookup/Locate is actually
'smarter' than FindKey and other methods that need an index.  When you
try to find a record on a dataset with Cached Updates on and you ask
Lookup/Locate to do the job, it will understand that it will not be
able to use the index because added records or records where you
changed the key will not be in the index yet.  Hence, it will use an
internal filter to be sure that both physical and cached records can
be found.

FindKey or ranges will use the index regardless the fact that cached
records will not be locatable.

HTH,

Jan

Other Threads