SQL Server Performance Forum – Threads Archive

Sql Updates

Hello, I’ve got a databasese with 55 mil records. I’ve got about 30 update statements which have to run in a particular sequence. When I run a single update statement in the query analyser, it take for example 30 minutes to run. But when I run all the statements in a row, some of them will run for more that a day!! Is there anyone who can point me in the right direction? TIa,
Gerben.

what kind of update statements you run? number of tables? any indexes in the tables?

The updates are different but for example uPDATE FLOWFILE
SET cntry_coeu = Rf_country.cntry_ceu,
cntry_cocm = Rf_country.cntry_ccm,
cntry_corpu = Rf_country.cntry_crpu,
cntry_coprnt = Rf_country.cntry_cprnt,
cntry_cobu = Rf_country.cntry_cbu
FROM Rf_country
WHERE FLOWFILE.cntry_ccntry = Rf_country.ff_cocntry

so your updating of data is from the other table (Rf_country) over to (FLOWFILE)?

Yes it is, the rf table is very small btw. The main problem is that when I run the statement seperately it runs fast enough, and when there are some updates before it runs very long. The statements are located in serveral stored procedures.

ok… rf table is small..but what about FLOWFILE table?

That one is about 60 GB (55 mil records)

any indexes on that table?

I tried with and without indexes.
There are differences but not the difference between 30 minutes en a day!!

ok…I dunt really know your whole table structures..so regarding your SQL query condition
(WHERE FLOWFILE.cntry_ccntry = Rf_country.ff_cocntry), this may caused the slowdown as there may be duplicate record for the field ff_cocntry Would like to understand more on the table structure..

Hi, Have you checked estimated execution time?
Please check and let us know so assist you further. This looks amazing that you have 50 mil records and your db size is 60GB, my db size is about 40 GB and i have number of tables have 40-50 mil records. As smy mentioned, give us information about table structure to understand more about your problem. Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

]]>