1

I'm looking for some recommendations when dealing with tables with large number of rows.

I have a table which has around 1.5 BILLION rows. Assuming there is a column ref_id. There could be 300K rows which belong to ref_id = 123 and after one point (this is application logic), I never need to filter for ref_id = 123.

I am thinking of periodically moving records which I identify as no longer needed into another table (let's call it archived_data). I will only ever go there in case someone really needs historical data.

Is this "moving to another table" have any significance overall in performance? Or is there any other aspect I should be pursuing like checking indexes etc?

1
  • You could have a filtered (or partial) index on a BOOLEAN column? You need to test with your own system - you could have an archived table and use triggers - only you can know the better solution when you test!
    – Vérace
    Commented Apr 7 at 9:35

2 Answers 2

0

Depending on the Hardware 1.5 Billion rows itself does not seem problematic. Archiving old data is usually a good idea.

If you're 100% sure that you do not need the data you can even dump it, compress it and save it somewhere safe (e.g. external hard-drive).

There are also some other ideas like partitioning or sharding that you can read up here: Best Practices to archive or remove old data in SQL Server 2012

Index maintenance might also improve the performance of the DB: Reorganize and Rebuild Indexes, Pros and Cons?

0

The indexes on this table are likely B-Trees. So they have a certain depth depending on the size of the key(s) and the number of rows. Reduce the number of rows and you may reduce the index depth.

Depth reduction will mean each key lookup has fewer hops to get from BTree root to leaf node. A given memory can hold a larger fraction of all index pages. These both make queries faster.

I say "may" because the depth is logarithmic in row count. So the 1.2B rows remaining may still need an index just as deep as the 1.5B rows do, only with the root node being slightly less full.

Secondly, if the archived rows are scattered throughout the table archiving them will allow each page to contain more of the useful rows. So each IO can service more queries, on average, improving performance.

Likely you will have to rebuild the table (or equivalent operation) before you see any benefit. This will require significant time and workspace on disk.

Not the answer you're looking for? Browse other questions tagged or ask your own question.