I need to speed up the insertion of row into a table

  • I am in need of some tutoring and advisement.  I am a nubee when it comes to designing processes as well as my dba expertise is sorely lacking.  My situation is that I have a table with in excess of 500 million rows and will grow by 80+ million every month through a stored proc.  The table has a primary key of 10 columns, although I could cut this down to 5 columns.

    The current process is:
    drop table tbl_tmp
    select * into tbl_tmp
    drop table tbl_mstr
    select a bunch of columns(31) into tbl_mstr from tbl_tmp inner join to a current date table
    alter table tbl_mstr add constraint [pk_tbl_mstr] primary key clustered (11 columns)

    I am doing it this way as I need to clear all of the current year’s records and preserve all previous year’s stuff (and I don’t know any better).  My initial process was to delete from where condition... current date tbl.  This method seemed to take an excessive amount of time.

    I thought of insert/merge/delete, but then I still had to run a delete against tbl_mstr.  So I figured it still had to find the stale records amongst all those millions of rows.  Again excessive time.

    The box I run this on is a VM on something… but I have 4 cpus and 16gb ram running SQL Server 2016 Standard edition.  My current process takes 65 minutes.  I have run with 2 cpus and the time is not much different, although this leaves little cpu room for other processes.

    Any advice/suggestions would be appreciated.

  • You should have a look at partitioning. IIRC as long as you have SP1 installed, you'll have access to this feature.
    It took me a while to get the hang of this feature when I first encountered it, so I have a couple of articles on it:
    Basic partitioning example
    Autoamtic Data Purging with Partition Switching

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply