Hi there,

I have an application that manages, let's say orders. Orders are stored in a master with many detail tables. Since only the most recent orders are being worked on, and the rest needs only to be viewed, combined with the fact that the bigger a table gets the slower the access is, this led me to a set of tables for the daily work, and the same set of tables as an archive.

When an order is processed, it is moved into the archive - copied to the archive tables, deleted in the work tables.

Since this copy process needs to be finished (or otherwise there would be fractions of the same order in both sets of tables) I'm using transactions.

Copy process needs a few seconds, in that time there is a lock on the tables. And that becomes more and more of a problem - copy times increase, user count increases, chance of lock error while somebody accesses tables increases. Users tend to kill the application after a few seconds, the lock persists for a while, other users need to wait, etc. ...and this this cascades until the EDB server needs to be restartet because it's totally frozen.

Only thing I can think about that could solve that dilemma is not using transactions, verify after each operation that it really succeeded, and verify in the end that the whole process was executed without interruption. Don't like that very much.

Or I could display some message that User X blocks the system, and you have to wait for a few seconds without the application seeming frozen. Don't really know how to do it - there are literally hundreds of ways people can access the tables, I can't try-except them all, and what about DB-aware components that post without me having the possibility to try-except at all?

Someone has an idea about that?

On 3/17/2020 3:49 PM, Heiko Knuettel wrote:
> When an order is processed, it is moved into the archive - copied to the archive tables, deleted in the work tables.
>
> Since this copy process needs to be finished (or otherwise there would be fractions of the same order in both sets of tables) I'm using transactions.
>
> Copy process needs a few seconds, in that time there is a lock on the tables. And that becomes more and more of a problem - copy times increase, user count increases, chance of lock error while somebody accesses tables increases. Users tend to kill the application after a few seconds, the lock persists for a while, other users need to wait, etc. ...and this this cascades until the EDB server needs to be restartet because it's totally frozen.
>
> Only thing I can think about that could solve that dilemma is not using transactions, verify after each operation that it really succeeded, and verify in the end that the whole process was executed without interruption. Don't like that very much.
>
> Or I could display some message that User X blocks the system, and you have to wait for a few seconds without the application seeming frozen. Don't really know how to do it - there are literally hundreds of ways people can access the tables, I can't try-except them all, and what about DB-aware components that post without me having the possibility to try-except at all?
>
> Someone has an idea about that?
>

It's tough to give specific advice but some generic ideas we have done :

1. Assuming you archive 1 order at a time (per transaction) can you
check where are you seeing the slowdown exactly - is it due to waiting
on locks or is is actual archive taking long time ?

2. Can you run the archiving outside main hours (during night or such) -
it would leave records in active tables but as long as it's done every
day might be an option  ?

3a. Sounds like you're currently doing this in the application code -
moving it entirely into server might improve things and client getting
killed would have no impact. Client could still trigger it by calling
server side with order ID or such. It's bit more maintenance but might
also be lot faster.

3b. Related to previous process but you could have clients just put the
order IDs into "pending archive" table and then have a service (or
server procedure) process there from there every X minutes or such.


Raul

Heiko


Pretty much agree with what Raul's posted.

Firstly, this bit "the fact that the bigger a table gets the slower the access is" baffles me. Are you running f/s or c/s, tables, tables with filters or queries? Are you talking viewing, inserting or updating?

I'm also interested in "Orders are stored in a master with many detail tables". Do you have the right design for the application (I know it may not be possible to change).


>> Since this copy process needs to be finished (or otherwise there would be fractions of the same order in both sets of tables) I'm using transactions.

If you don't use transactions you'd have to build all your own checks (as you said) and that would almost certainly end up being slower, and more error prone.


>1. Assuming you archive 1 order at a time (per transaction) can you
>check where are you seeing the slowdown exactly - is it due to waiting
>on locks or is is actual archive taking long time ?

Yup agree with this Raul - where is the real bottleneck?

>2. Can you run the archiving outside main hours (during night or such) -
>it would leave records in active tables but as long as it's done every
>day might be an option ?

Agree with one slight mod - if such a flag doesn't already exist add a column for "pending archive" to indicate the record should be ignored as far as active stuff is concerned. If c/s use a job. Alternatively have a background thread that is kicked off when a record is archived. It would take a bit more work but could be done without using any transactions at all. As an alternative to a thread you could have a small application that runs on some machine and cycles round checking for orders to transfer. That's essentially the same as I did for my email server.

This "Or I could display some message that User X blocks the system, and you have to wait for a few seconds without the application seeming frozen. Don't really know how to do it - there are literally hundreds of ways people can access the tables, I can't try-except them all, and what about DB-aware components that post without me having the possibility to try-except at all?" I think is what makes Raul guess you're using F/S. I think you need to look at triggers. Use an AFTER UPDATE trigger and take action when the order is flagged as complete. This would work well with setting flags.

Roy