Stored Procedure Runs Very Long

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.

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

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

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?

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.

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.

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

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