Thread: inserting into large tables
03-18-10, 00:10 #1Registered User
- Join Date
- Mar 2010
Unanswered: inserting into large tables
Can anyone give some general strategies on how to deal with inserting into extremely large tables? We have a table of 500 Million rows, and new data that is inserted into it regularly and its slowing our site down to a craw.
We have three indexes on the table, and we know that removing two of them does speed up the inserting considerably.. as it should, but the indices are necessary to make other components run in a reasonable period of time.
We have been trying out Enterprise edition to see if we can partition the table and speed up the inserting, but to this point it hasn't worked. But perhaps we are doing it wrong. We had a clustered primary key on columns called matchID and playerID, and non-clustered indices on several other columns. We thought it made sense to partition on another column "season" as that was guaranteed to split the data according to what was historical and clearly not going to change, and what could potentially change.
We would rather not create a whole separate historical table, because of all the work reworking code in other parts of our system to consider the historical table in some situations and the non historical one in others, but perhaps this is what we have to do?
03-18-10, 08:38 #2:-)
Provided Answers: 1
- Join Date
- Jun 2003
- Toronto, Canada
I'd say in these circumstances you should not be using clustered indices at all.---
"It does not work" is not a valid problem statement.
03-18-10, 10:06 #3Registered User
- Join Date
- Jun 2004
- Long Island
I had a 1 billion row table in which I used partitioning, and did inserts as follows:
This was for a LoanPerformance application in which we had a table with MBS, ABS and Alt-A loan information at the loan level.
- Table was partitioned by period (month), keep in mind only 1024 partitions allowed per table.
- for each load (monthly data, 20MM rows each month)
- switch out period partition to work area
- drop partition indexes (were aligned to table partitions), except key for pre-delete
- pre-delete based in condition (if reload of same files)
- insert new data to partition
- rebuild indexes on partition
- switch partition back in
I found the main benefit of partitioning came when any updatestats or reorg-rebuild indexes, never had to do it at the table level, just the partition level.