20,000 salers orders per day

davmac1
davmac1 Member Posts: 1,283
edited 2008-05-12 in SQL Performance
I have submitted some questions to forum members who manage large databases already.
What I am trying to find out is, does anyone have or know of sites that are running at least 20,000 sales orders per day along with shipping and the associated posting.
We have a prospect who wants to run this volume and does not want to batch the posting to run at night.
They may also run Eship or alternatively run a third party application for shipping.

I am thinking this needs to support a peak volume of one new sales order per second and one new shipment and invoice per second (at the same time).
David Machanick
http://mibuso.com/blogs/davidmachanick/

Comments

  • bbrown
    bbrown Member Posts: 3,268
    How many sales lines in the average order?
    There are no bugs - only undocumented features.
  • davmac1
    davmac1 Member Posts: 1,283
    I think there will be one item line plus a freight line.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • ara3n
    ara3n Member Posts: 9,258
    Let's do the math. Lets say they work for 10 hours at warehouse.
    10 hour * 60 min * 60sec
    36000=10*60*60

    20000/36000 = .55 transaction/sec

    in other words 1 transaction every 2 seconds.

    Can you post a sales order in two seconds?

    batch posting is the way to go.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1
    davmac1 Member Posts: 1,283
    They do not want to post a nightly batch.
    I am thinking that a dedicated workstation could run a codeunit that would do all the posting. It could post all completed sales orders in an endless loop.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • ara3n
    ara3n Member Posts: 9,258
    davmac1 wrote:
    They do not want to post a nightly batch.
    I am thinking that a dedicated workstation could run a codeunit that would do all the posting. It could post all completed sales orders in an endless loop.

    You could use job queue. There is a blog on how to use job queue for posting through just NAS.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • davmac1
    davmac1 Member Posts: 1,283
    On the Microsoft SQL Server site, it looks like they have figured out how to handle a high volume SAP site, but no info about MBS products.
    I think the volume can be done in NAV, but would like to find out if someone has actually done this level successfully with continuous posting.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • ara3n
    ara3n Member Posts: 9,258
    here is how to for job queue

    http://blogs.msdn.com/microsoft_dynamic ... v-5-0.aspx


    I've implemented high volume, but they are batch processed.

    100 K per week.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_Singleton
    David_Singleton Member Posts: 5,479
    davmac1 wrote:
    I have submitted some questions to forum members who manage large databases already.
    What I am trying to find out is, does anyone have or know of sites that are running at least 20,000 sales orders per day along with shipping and the associated posting.
    We have a prospect who wants to run this volume and does not want to batch the posting to run at night.
    They may also run Eship or alternatively run a third party application for shipping.

    I am thinking this needs to support a peak volume of one new sales order per second and one new shipment and invoice per second (at the same time).

    The posting of 20,000 sales lines a day is not a huge issue, but the fact that these are one line orders tends to suggest that they need to look at what they are trying to achieve, and do it differently. To me this rings of Retail, and this is just not the right way to do retail.

    Most importantly is your comment about eShip. If you are planning eShip, then on line posting is NOT an option. So you should decide that up front.

    Anyway to answer you actual question, yes you can post 20,000 one line orders, but be prepared for a LOT of tuning and unblocking work to get a usable system. And don't skimp on hardware. If they ask "But why do we need so many hard drives", just run away.
    David Singleton
  • David_Singleton
    David_Singleton Member Posts: 5,479
    By the way, one of my first big NAV performance tun ups was for a client that averaged 53,000 sales order lines per day. That was in ver. 1.3 and took quite some work, and a pretty impressive server to achieve, but it did work. BUT that was all over 10 years ago, before Dimensions, and reservations and value entries and application entries etc. So it was much easier then.
    David Singleton
  • Savatage
    Savatage Member Posts: 7,142
    IMHO icks-NAA on the dimensions-AA :lol:
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Nobody
    Nobody Member Posts: 93
    Just an FYI....

    I just ran a VERY VERY basic test with 5.00 SP1, SQL 2005 x64, Cronus USA database, and the Application Bench Mark Tool. I did not make any index or code changes.

    In 24 hours it was able to enter about 60000 Sales Order Lines and Post about 30000 of those at the same time as doing relatively the same volume of Purchase Orders. These were all 5 line orders. This is not to say there no issues or errors, but it was able to process this type of volume. :-)

    Server Info
    HP DL385
    SQL 2005 x64 SP2 with CUM 5 (Build 3215) TraceFlag 4119 enabled
    16 GB of RAM with 14 GB decicated to SQL
    Dual Core AMD Opteron 3.0 GHz CPU
    4 Drive RAID 0 (72 GB 10K RPM Internal SAS drives) for Data
    2 Drive RAID 0 (72 GB 10K RPM Internal SAS drives) for Logs
    4 clients were on 100 Megabit connection
    4 clients were on 1 Gigabit connection
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Nobody wrote:
    Just an FYI....

    I just ran a VERY VERY basic test with 5.00 SP1, SQL 2005 x64, Cronus USA database, and the Application Bench Mark Tool. I did not make any index or code changes.

    In 24 hours it was able to enter about 60000 Sales Order Lines and Post about 30000 of those at the same time as doing relatively the same volume of Purchase Orders. These were all 5 line orders. This is not to say there no issues or errors, but it was able to process this type of volume. :-)

    Server Info
    HP DL385
    SQL 2005 x64 SP2 with CUM 5 (Build 3215) TraceFlag 4119 enabled
    16 GB of RAM with 14 GB decicated to SQL
    Dual Core AMD Opteron 3.0 GHz CPU
    4 Drive RAID 0 (72 GB 10K RPM Internal SAS drives) for Data
    2 Drive RAID 0 (72 GB 10K RPM Internal SAS drives) for Logs
    4 clients were on 100 Megabit connection
    4 clients were on 1 Gigabit connection

    Michael, how big was this database when you started the test? It's a big difference to start with a 150MB cronus database or a real 100GB database.
    Marije Brummel | Freelance Business Central Specialist
  • Nobody
    Nobody Member Posts: 93
    It was the standard Cronus DB off the DVD. Started at 150 meg and ended at about 1 GB.

    With some index tuning and better hardware (more and faster drives) I do not see why this type of volume could not be sustanied regardless of DB size. It would take some time to run that test though, at one GB a day it would takes months to mock up a 150 Gb database and the bench mark tool can only run for 24 hours at time becuase of of the code.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Nobody wrote:
    It was the standard Cronus DB off the DVD. Started at 150 meg and ended at about 1 GB.

    With some index tuning and better hardware (more and faster drives) I do not see why this type of volume could not be sustanied regardless of DB size. It would take some time to run that test though, at one GB a day it would takes months to mock up a 150 Gb database and the bench mark tool can only run for 24 hours at time becuase of of the code.

    And that is lesson #1, the world of Cronus is VERY VERY different to what happens out there in the real world.
    David Singleton
  • Nobody
    Nobody Member Posts: 93
    That is why I prefaced this with saying it was a "VERY VERY basic test" :D
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    My experience is also that moving from a G4 server to a G5 gives up to 10 times faster performance due to the Serial SCSI and x64.

    But that is totaly off topic. :mrgreen:
    Marije Brummel | Freelance Business Central Specialist

Categories