How to make Inserts , updates and deletes faster in SQL server - Microsoft Azure?
I tried to delete some records from a table with 100 millions records and it took forever. One of the reason being there was no index on the column I used in the where clause of the delete statement and other reason being the table was so big. So deleting a big chunk of the data from such big tables can take forever since the data gets written to transaction logs and the table has to scan for all the records that match the where clause condition.
How to delete records quickly from huge tables in sql server on Azure?
1) Use only the column that has index defined in the where clause condition. May be this will help find the records to be deleted quickly.
2) Delete records in small chunks using the loop below. This prevents transaction logs from growing rapidly. @@rowcount always returns the number of records affected from delete.
DELETE TOP (100000) from dbo.mytable WHERE name='dwbi'
WHILE @@rowcount > 0
BEGIN
DELETE TOP (100000) from dbo.mytable
WHERE name='dwbi'
END
3) Insert data into temp table with no index on it using the columns that need not be deleted , delete the original table, rename the temp table back to the original table and add back the index on the newly renamed temp table.
select * into tmp_mytable from mytable where name!='dwbi'
drop mytable
sp_rename 'tmp_mytable', 'mytable'
How to insert records quickly from huge tables in sql server on Azure?
My records to insert records into a huge table improved a lot after I disabled the non clustered indexes and rebuild it after the insert. The steps are below:
ALTER INDEX [IX_MYTABLE_User_SID] ON dbo.[MYTABLE] DISABLE
Insert into [dbo].[mytable] select * from [dbo].[stg_table]
ALTER INDEX [IX_MYTABLE_UserSID] ON dbo.[MYTABLE] REBUILD
How to update records quickly from huge tables in sql server on Azure?
My usual way of updating records used to be something like this:
update dbo.mytablename set name= (
select dbo.TMP_table_name.name
from TMP_table_name
where dbo.mytablename.party_sid=TMP_table_name.party_sid)
The above update statement takes a lot of time because it updates all the records even those that do not match. The easier and faster way to update is using the statement below: This will update only the matching records and it is pretty quicker.
update dbo.mytablename set
dbo.mytablename.name=dbo.TMP_table_name.name
from TMP_table_name
where dbo.mytablename.party_sid=TMP_table_name.party_sid
