Serial Number Performance and DB Locking

[Former Member]

Hi All,

Can anyone assist with an issue we are having with our SBO2007 PL 42...

There are 8 million Serial Numbers in the system with an average of 1 million Serial Numbers per Item (8 Items). - Table OSRI

There are 40 milion Transactions in the system with and aveage of 5 Transactions per Serial Number.

When the Serial Number Selection Screen opens in a Marketing Document, it slows the entire organisation down and it seems like there are Locks in the Database that are causing this.

Can anyone help me understand why this occurs and if there is any way of overcomming this "Serious" Issue?

Thanks.

[Former Member · Accepted solution]

HI

can you tell ur system config?

[Former Member · Accepted solution]

Hi kambadasan

We have a High Spec Server for both SQL and TServer. Both are Dual Xeon Quad Core 16gb Memory with Gigabit LAN and Raid 5 for SQL Data Files and Log on Seperate IO....etc.

It definately is not the Hardware Limitation as the system performed reasonabily well when the Serial Number count was low. Now that it has grown, it seems, after some analysis an user responses, that the System Freezes and the users, across the company, wait for upto a minute for the system to respond when trying to sell a Serial Number Item.

The Serial Number Items are managed On Every Transaction. Even the Creation of the Serial Numbers when recieved on a GRPO causes the entire system to hault. We traced it to the DB Locking while the process finishes for both Recieving and Selling a Serial Number Item.

We need a solution or work-around to this issue as it is causing serious frustrations for users. If anyone has any suggestions, please assist.

Thanks

[Former Member]

Hi,

I am afraid you need to upgrade to 8.8 and perform a data archive in order to speed up.

How big size is your database now?

Thanks,

Gordon

[Former Member]

Hi

@ Gordon,

The DB Size is 45gig.

Does the 8.8 version "Archiving" allow for Serial Number archiving / reduction? I did not see this in any documentation or material.

@ All....

Furthermore, I have seen that SBO 8.8 Serial Number Tables have been changed / modified. Has this improved the Serial Number Performance issue in the SBO Application?

Thanks