SQL Server Performance

Stored Procedure Runs Very Long

Discussion in 'SQL Server 2005 General DBA Questions' started by dvdb, Jan 22, 2008.

  1. dvdb New Member

    I think I may need help with query optimization.

    We have a customer table and an address table. Each customer can have 1 or more addresses in the address table.

    Thetask is to synchronize customer information from 2 entirely separatesystems. I have a stored procedure that reads a text file (exportedfrom the 'master' system) and updates all the customer records in thesecond system (which is in the sql server db). The queries themselveswork. The data is accurate. My issue is with how long it takes thestored procedure to run. With over 11,000 records in the text file, thestored procedure sometimes takes over 3 hours to run.

    That seems excessive to me. (and probably to those of you snickering in the back)

    As an overview: my stored procedure does the following.

    1) creates a temp table
    2) dumps the data from the text file into the temp table
    3) updates the address table with data from the temp table (records already there)
    4) inserts records into the address table from the temp table (records not already there)
    5) updates the customer table with an address id
    6) drops the temp table

    Any help/suggestions is appreciated. If you need more info, let me know.
  2. satya Moderator

  3. dvdb New Member

    Thanks for the reply. No, it's not an upgraded db.
  4. satya Moderator

    Then you must ensure that the plan is upto date on cache and continue the database optimization tasks such reindexing to keepup the performance, go through the links provided above.
  5. Madhivanan Moderator

    Are you using Cursors in your procedure?
  6. dvdb New Member

    No, I'm not using cursors. Do you recommend I learn about cursors? In general, do cursors help with performance?
  7. satya Moderator

    If you are not using them then better not to go that route in avoiding more troubles.
  8. dvdb New Member

    I broke the SP down. The only thing left in each time I ran it was thecreate temp table, bulk insert, drop temp table. (For obvious reasons.)Nothing unusual resulted. Each update and insert section by itself ranbetween 3 and 5 minutes. Putting the SP back together it ran about 17minutes.

    One note: this testing was done on my local and notthe production server. I'm expecting a long run time again on theproduction server, as there are many other databases.

    Any thoughts on this?
  9. satya Moderator

    Make sure to compile the stored procedure as soon as initiated on Producton server in order to takeup the update plan, also refer to the links above to fine tune the SP performance.
  10. dvdb New Member

    Can you elaborate on what you mean by compile the stored procedure? Or is there a good place to read about this? I will also be sure to look at the links above. Thanks.
  11. ndinakar Member

    How many records do you dump into the temp table? You might want to consider adding a couple of indexes on it to make the queries faster.
  12. dvdb New Member

    There are almost 4,000 customer records and almost 12,000 address records.
  13. ndinakar Member

    Create indexes on the temp table.. it will make a difference.

Share This Page