SQL Server Performance

looping through simple inserts/updates. PreparedStatement vs. Stored Procedure?

Discussion in 'T-SQL Performance Tuning for Developers' started by daheri, Nov 23, 2007.

  1. daheri New Member

    Hi,
    I have a Java method which receives a couple of large arrays (1000 rows). One column has a ID-number, next column a value to be stored at that ID.
    This is to be directly inserted into a similar two-column table in the database. But if the record (ID-number) exist, it must be updated.
    My first thought was to create a stored procedure to update or insert to values depending on its existence in the db. This worked fine, but is a bit slow.
    Now I am thinking to do this by prepared statements to gain performance.
    I will then create two Statements: UpdateStatement and InsertStatement.
    Then I will loop through the arrays and do a:
    try{
    //do insertstatement
    }catch(SQLException){
    //do updatestatement
    }
    This is of course not the best use of Java, but I think I will gain performance.
    What do you think?
  2. ranjitjain New Member

    Do you mean, doing round trip calls to server for executing insert/update almost 1000 times and expecting performance, then you are really missing much over here, it could impact and degrade the performance. Ad-hoc queries should be avoided compared to stored procedures.
    One option could be to pass all id's , all values in csv format and then fire one insert for records which are not present in your table and finally fire one UPDATE on records which exists.
    This all in one go in one sp with one server call.

Share This Page