I read the good posts here and here, but my scenario is a bit different, I cannot consider that older data becomes read-only data because any record in my
Transactions table can be subject to updates at any time.
The (short?) story is: I have a
Transactions table that should hold over 2 billions of items in it.
The data is produced by distinct jobs, each job generates its own data into the
Transactions table. One column is a foreign key:
Transactions.JobId. This table contain working datasets logically splitted between jobs (jobs are kept in a separate
The operations on the
Transactions table are:
BULK INSERTs(using C# SqlBulkCopy Class)
UPDATEsfor single rows (rows get their statuses updated)
Transactions table gets bigger, we start getting poor performance, even sql deadlocks. The hardware won't keep up with the needs, so I am thinking of splitting the table
Transactions into multiple smaller tables (let's say max of 100M records/table). Then, I can define a "covering view" to aggregate the data when I need to perform some general query. When the first
Transactions1 table is full (contains 100M records), any new jobs will be pointed to store their data into the second table
Transactions2 and so on.
Question is, do you see this as a feasable approach, is there other options? Another one would be an expensive Enterprise license for SQL Server.
PS: Some modules of my C# apps use ADO.NET, other modules make use of EF Code First and Linq2Sql to access the data.
Here is the approximative
Transactions table structure:
PK: [Id] [bigint] IDENTITY(1,1) NOT NULL, FK: [UserId] [bigint] NOT NULL, FK: [JobId] [int] NOT NULL, FK: [ItemStatusCodeId] [tinyint] NOT NULL, + other 7 columns
(indexes are created for each FK).
The column that is frequently updated is
ItemStatusCodeId which is a FK that points to another table that holds 10 records only. The update is as simple as it can get:
UPDATE Transactions SET ItemStatusCodeId = <value> WHERE Id = <key>