2

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 Jobs table).

The operations on the Transactions table are:

  1. BULK INSERTs (using C# SqlBulkCopy Class)
  2. UPDATEs for single rows (rows get their statuses updated)

As the 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.

UPDATE 1

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>
  • What version and edition is this? There might be other options depending on that – Tom V Dec 3 '15 at 10:17
  • @TomV I would not like to depend on some version/edition, if possible – Adi Dec 3 '15 at 10:21
  • Could you add table structure and samples of the updates you are running? – Tom V Dec 3 '15 at 10:22
  • @TomV Added some table structure – Adi Dec 3 '15 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.