Results 1 to 5 of 5

Thread: sp question...

  1. #1
    Join Date
    Apr 2004
    Location
    Texas
    Posts
    4

    Unanswered: sp question...

    How often can I trigger the same sp. I want to submit a salesorder and have my sp trigger other sp's, the whole process taking anywhere from 30 seconds to 3 minutes per salesorder, depending on how many aspects the order touches in our accounting system. Basically, in a hypothetical situation, can I have 30 people submit the same sp at the exact same time? If that does occur, what type of performance issues will I encounter if any?

    Thanks...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't think of any hard and fast limit.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Easy to test...

    start up sql profiler first

    Create 30 bat files with osql and put the execution of them all in 1 bat file and execute that

    they'll all launch and thread out...1 spid per....

    BUT a transaction that takes 30 seconds to 3 minutes?

    I'd have a look at the design...that is waaaaaaaaaaaaaaaaaay too long
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2004
    Location
    Texas
    Posts
    4
    I would definately agree with you. The reason why the time differs is because of how many things it has to touch in the accounting software. Depending on whether or not any particular line item is serialized, has custom cost tiers, etc...can go from triggering 10 sps to 50 sps...thus the time difference...and yes I know that that seems like a non issue with sql, but when companies write software in poor ways, then we have to find ways around it. Which is why I am trying some work arounds...I will give it a shot and thanks for the input...I will try the batch thing later...Thanks again...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The only other thing I would suggest is to "break up" the tables in to logically grouped "categories" and put them on their own physical devices to reduce contention and/or creat partitioned views for the same reason...still that's a lot of processing in 1 transaction....I flip out when IO have to run a batch process that takes 5 minutes for 1000's of "cases"

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •