Last post Mar 11, 2019 02:44 AM by Wei Zhang
Mar 08, 2019 10:25 PM|MikeT89|LINK
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.
Mar 11, 2019 02:15 AM|Ackerly Xu|LINK
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/
Mar 11, 2019 02:44 AM|Wei Zhang|LINK
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.