locked
Is it better to directly update a table using a lot of joins or storing data to a temp table first? RRS feed

  • Question

  • User869176912 posted

    Hello,

    I am trying to update over 10 columns in a table that contains over a million rows. The source data are coming from multiple tables which requires me to join about 13 tables.

    My query run super slow about 1hr and 30 mins.

    My question is, would it be better to store all the columns I need into a temp table and do all my joins there or use a cte, then use the temp table or cte to update that actual table?

    I'm trying to find a way to make my query faster.

    Friday, March 8, 2019 10:25 PM

Answers

  • User-1174608757 posted

    Hi MikeT89,

    Firstly ,for you question. You could use  temp table.Temporary table should be much smaller that original source table, can be indexed easily  and can cached subset of data which you are interested in. Else , for CTE, it's help a lot with building (and testing) a query piece by piece.So both of these ways are helpful for query.

    Then how to use other ways  improve the sql performance?This is very open ended question.You could think in these aspects

    1.Depending that your table has too many data, you could just split tables into small tables depend on you database usage.For example if there exists column which has little relationships with your query,you could split them for alone table.

    2.You could add index. Indexing on table is most command answer. But heavy DML can cause index costly. you could try to have index in tables & plan to disable the index before any bulk activity & enable the index after that.

    3.Last thing which could speed up the ad-hoc query is allowing dirty reads with table hint WITH (NOLOCK).Instead of hint you can set transaction isolation level to read uncommited. I assume for ad-hoc queries dirty reads is good enough.

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2019 2:44 AM

All replies

  • User-893317190 posted

    Hi MikeT89,

    Not sure about your requirement , but if you want to improve the performance of selecting , you could add index  in the column  which appears in your select query's where clause.

    But  you had better remove the index of the column which you want to update.

    For example, if you want to execute the sql below.

    update  student set name = 'name you want to update' where age>18

    You could add index to the age column which appears in your where clause , and remove the index in the name column if it has.

    If you don't want to remove the index of name  in order to improve the performance of  querying, you should know index is not free, you should consider whether to speed up update or speed up select.

    For more information , you could refer to the link below https://www.sqlservergeeks.com/sql-server-optimizing-update-queries-for-large-data-volumes/

    Best regards,

    Ackerly Xu

    Monday, March 11, 2019 2:15 AM
  • User-1174608757 posted

    Hi MikeT89,

    Firstly ,for you question. You could use  temp table.Temporary table should be much smaller that original source table, can be indexed easily  and can cached subset of data which you are interested in. Else , for CTE, it's help a lot with building (and testing) a query piece by piece.So both of these ways are helpful for query.

    Then how to use other ways  improve the sql performance?This is very open ended question.You could think in these aspects

    1.Depending that your table has too many data, you could just split tables into small tables depend on you database usage.For example if there exists column which has little relationships with your query,you could split them for alone table.

    2.You could add index. Indexing on table is most command answer. But heavy DML can cause index costly. you could try to have index in tables & plan to disable the index before any bulk activity & enable the index after that.

    3.Last thing which could speed up the ad-hoc query is allowing dirty reads with table hint WITH (NOLOCK).Instead of hint you can set transaction isolation level to read uncommited. I assume for ad-hoc queries dirty reads is good enough.

    Best Regards

    Wei Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 11, 2019 2:44 AM