Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Question Performane issue in Multiple Call to Procedure

    Hi,

    We have scenario such that a DB2 stored procedure is required to be called from JDBC multiple times at a time.This procedure inserts data in a table. So we have following options here:-

    - Call Stored Procedure multiple times from Java end for each Insert
    - Call stored procedure once by passing all values at once and then running a loop in procedure.

    Which option is better in terms of Performance and design.
    Any help will be appreciated.

    Thanks,
    Neha

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The answer is the ever popular: It depends.

    Because the time needed to consolidate the values in your java code and split the consolidated form back to the individual values within the DB2 stored procedure will sometimes be less than the cost of repeated calls to the procedure, the answer can vary.

    If you are running from an application server over a Gigabit connection, I'd guess that the multiple procedure calls will be faster. If you are running from a fat client app on a cell phone, I'd guess that the single gather/call/split/return will be faster.

    From the design perspective alone, the simpler answer with multiple procedure calls is better. That simplicity may not serve the application/user needs so the added complexity to get better performance may be the better answer from the overall perspective.

    Without more insight into your environment, all I can do is point you to the items I see as being "interesting" in this case. There is no way I can give specific help.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Thinking about this...if you have a stored procedure with input parameters then this design is used for a single insert most often, and to include multiple inserts would be a string parameter which would take time to disassemble for single inserts, so I'd suspect the time would be increased porpotinate to the number of inserted rows. Probably best to do multiple calls to the sproc with one inserted row at a time. Unless the sproc is replaced with a bcp procedure and table data is loaded for another process. Just a thought.

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    You could write it both ways, then determine the number of transactions that cause one method to be quicker than the the other, then count the transactions and choose the appropriate method.

Tags for this Thread

Posting Permissions

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