Hi everyone

I have a process that takes around 5 seconds to run. It runs inside a
transaction. The problem is that there are around 40 users accessing the
same process almost at the same time. If all 40 reach the transaction at the
same time, the last in the row will wait 40x5 seconds.

I would like to know if someone has some idea about what can I do to solve
or improve the performance at all. This 5 seconds can´t be improved because
it only uses Tables/FindKey combination and some posts, there is no SQL
involved.

One of my ideas is remove transaction and hold the updated data in memory
and if something happened (try/except block) I manually revert the data in
database.

I accept any suggestions.

Thanks.

Eduardo

Eduardo


Without knowing more about what's actually happening what about using a thread. Unless the users have to wait for the transaction to complete so they can do something else leave it wrapped in a transaction, sling it at a thread and who cares if it has to wait a few minutes

If they do have to wait and stare at the screen for those 5 seconds (x 40) I have no idea.

Roy Lambert

Eduardo,

<< I have a process that takes around 5 seconds to run. It runs inside a
transaction. The problem is that there are around 40 users accessing the
same process almost at the same time. If all 40 reach the transaction at the
same time, the last in the row will wait 40x5 seconds.

I would like to know if someone has some idea about what can I do to solve
or improve the performance at all. This 5 seconds can´t be improved because
it only uses Tables/FindKey combination and some posts, there is no SQL
involved. >>

You're really not giving much room for suggestions.  The only two options
are to improve the speed or get rid of the transaction, and there's really
no way of getting rid of the transaction and still having a transaction.

One of my ideas is remove transaction and hold the updated data in memory
and if something happened (try/except block) I manually revert the data in
database. >>

That won't work if you need proper isolation to keep one user from seeing
un-committed changes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Eduardo

A few questions -

1) are you using FastMM?
2) is it a client-server configuration?
3) is a single execution of the process significantly faster or slower
without the transaction?
4) what do you think takes up most of the processing time (eg. lookups,
updates, or some other processing)?
5) could you briefly describe what the process is doing?

Rob

Tim

The process is simple (I think). Because it is a POS system then I have two
tables, one holds the customer id, the sum of the sale, the salesman code
and other stuffs. The other are the items of the sale.

The problem happened when I have to post the sale into another table (I need
to do that) and I use a "while not eof" to perform this operation. It is
something like below:

C/S, I am using DBISAM 4,26 last build, the number of items is around 10

StartTransaction;

TSalesItems.First;
while not TSalesItems.Eof do begin
  // Save the items to another table, subtract from the quantity in stock
  TSalesItems.Next;
end;

while not TSalesItems.Eof do begin
  TSalesItems.Delete;
end;

// Save the amount of money received, change information to cashier (I don´t
know if is the correct word)
TSales.Delete

Commit;

Eduardo

Rob

> 1) are you using FastMM?
Yes. It is inside DBISAM 4.26

> 2) is it a client-server configuration?
Yes

> 3) is a single execution of the process significantly faster or slower
> without the transaction?
Not that I aware of.

> 4) what do you think takes up most of the processing time (eg. lookups,
> updates, or some other processing)?
Updated are the only "special" things because there are no lookups neither
SQL statements that could be optimized.

> 5) could you briefly describe what the process is doing?
See more details on Tim´s thread.

Eduardo

"Eduardo [HPro]" <contato@hpro.com.br> wrote in message
news:BA5A0C39-D81A-4ED8-8576-80EE4AA39675@news.elevatesoft.com...
> Tim
>
> The process is simple (I think). Because it is a POS system then I have
> two
> tables, one holds the customer id, the sum of the sale, the salesman code
> and other stuffs. The other are the items of the sale.
>
> The problem happened when I have to post the sale into another table (I
> need
> to do that) and I use a "while not eof" to perform this operation. It is
> something like below:
>
> C/S, I am using DBISAM 4,26 last build, the number of items is around 10
>
> StartTransaction;
>
> TSalesItems.First;
> while not TSalesItems.Eof do begin
>   // Save the items to another table, subtract from the quantity in stock
>   TSalesItems.Next;
> end;
>

That's possibly part of your problem. Iterating thru a table in C/S is slow.
Does the table have any lookups, etc?

> while not TSalesItems.Eof do begin
>   TSalesItems.Delete;
> end;
>

Try EmptyTable. Again, you are iterating thru the table, and updating
indexes etc one record at a time.

Robert

> // Save the amount of money received, change information to cashier (I
> don´t
> know if is the correct word)
> TSales.Delete
>
> Commit;
>
> Eduardo
>
>

Hi Eduardo

>> 1) are you using FastMM?
> Yes. It is inside DBISAM 4.26
>

If you are using earlier versions of Delphi, I believe you still need to
include it in your project.

>> 2) is it a client-server configuration?
> Yes
>

Some suggestions -

1) cached updates could really speed up your processing (Note: there is a
bug with AutoInc fields during inserts that you may need to work around)
2) RemoteReadSize will speed up sequential reads through a table (unless you
are editing every record)
3) look for processing that can be moved to the server using SQL

Rob

Rob

> If you are using earlier versions of Delphi, I believe you still need to
> include it in your project.
Yes, I have already use it.

> Some suggestions -
>
> 1) cached updates could really speed up your processing (Note: there is a
> bug with AutoInc fields during inserts that you may need to work around)
> 2) RemoteReadSize will speed up sequential reads through a table (unless
> you are editing every record)
> 3) look for processing that can be moved to the server using SQL

I really appreciate your suggestions but I noticed there was a loop used
twice and it was changed to perform the operations using just one loop. It
will increase the speed of process. I have already tried to move it to SQL
but the gain is not enough.

Thanks anyway.

Eduardo

Robert

I have noticed there were two loops used to perform different things but I
have changed the code to use just one loop and perform both things. It
certainly will increase the speed of process.

Thanks

Eduardo