August 8, 2002 at 9:52 am
I have a 30 gig database with a web application front-end (Epiphany). Most of the tables involved range from 7 million to 15 million rows in size. I believe my indexes are good; however, here is the problem:
I have a table with around 5 million rows on the same server but in another database. My procedure does the following. It load the 5 million rows (or a portion thereof) into a cursor and then begins the process of seeing whether each record exists in the database and has the appropriate info in its corresponding tables. It checks each of these tables and if the record exists then it updates, if not, it inserts. It is having to perform and select (with nolock) on each of these tables to determine its existence. Problem 1: At the rate it is going (18,000 records per hour), it would take 277 hours to go through all 5 million. This process needs to be run on a regular basis and this time is no where close to practical. Problem 2: After a few minutes of running this procedure as well as others, the end users come to a grinding halt and there systems all freeze up. I have tried committing the transaction after each record as well as after groups of 10000, but the users still get locked up. All the selects use (no lock) and the inserts and updates are on a single row. It has got to where I can barely run any procs during the day without trashing users. Any ideas? Please help?
August 8, 2002 at 3:57 pm
If you can mark record to timestamp them,
Use SQL instead, something like this pseudo
select top xx rows from your table that INNER JOIN with the other database and perform updates with these. These should be changed, new, whatever records.
DELETE these records (or mark them as used, scanned, whatever)
Continue until there are no records.
The remainder of the unscanned rows are inserts.
If it bogs down the server, insert a WAITFOR statement before each loop. Play with the top xx records to find a good number.
Steve Jones
August 8, 2002 at 5:20 pm
I think you should make two temp. tables ,one is for records exists (existRecords) and other one is for not exists. (NotExistsRecords)
Join the temp. table existRecords with your table and update the records
and then join the other table notexistsRecords with your table and insert.
This makes the process faster.
I think using cursor at a very large database is not a good idea.
August 9, 2002 at 3:05 am
Have to agree a cursor sounds bad. Before you start recoding though, have you looked at the query plans to be sure the indexes are good?
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply