SQL Server Performance

Sql Updates

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by gerben, Jun 6, 2006.

  1. gerben New Member

    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.
  2. smy New Member

    what kind of update statements you run? number of tables? any indexes in the tables?
  3. gerben New Member

    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
  4. smy New Member

    so your updating of data is from the other table (Rf_country) over to (FLOWFILE)?
  5. gerben New Member

    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.
  6. smy New Member

    ok... rf table is small..but what about FLOWFILE table?
  7. gerben New Member

    That one is about 60 GB (55 mil records)
  8. smy New Member

    any indexes on that table?
  9. gerben New Member

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

    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..
  11. waqar Member

    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. *~

Share This Page