0

In my application, I want to partition two tables, "files" and "value_text", because in the next days I have to insert 300 million rows (files) and 1.200 million rows (value_text) in these tables.

Furthermore, this tables will receive between 200.000 - 400.000 inserts every day.

I use SQL Server with Azure. Specifically, Azure SQL Database with pricing/model tier "Standard S4 (200 DTUs)".

Daily, the application has to support searches in this tables filtering by some columns (the tables schemes are at the end of the post):

  • Files: There are filters by id, cid, eid, name and created columns.
  • Value_text: There are filters by id_file, id_field and value.

In this searches I need to give priority to the most recent inserts, I mean, when I search for a register which was insert a long time ago I can assume a slow search, but for the most recent inserts, I need a fast search.

One of my reasons for using partitioning is because I need to make many non-stop inserts and I can't block those tables for a long time because the application needs to continue running. So, to solve this problem I had thought to use partition exchange/switch.

I think that "Horizontal partitioning" (sharding) is the best choice for my problem, but I have some questions:

  • How to choose the partition key for each table?
  • What partition strategy fits best with my problem? (Lookup, Range or Hash)
  • Should all the partitions supports the same number of rows? Or should I have partitions with different size?
  • How to determinate the size of each partition?

Additional info

files table:

CREATE TABLE [dbo].[files](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [cid] [bigint] NOT NULL DEFAULT ((0)),
    [eid] [bigint] NOT NULL DEFAULT ((0)),
    [year] [bigint] NOT NULL DEFAULT ((0)),
    [name] [nvarchar](255) NOT NULL DEFAULT (N''),
    [extension] [int] NOT NULL DEFAULT ((0)),
    [size] [bigint] NOT NULL DEFAULT ((0)),
    [id_doc] [bigint] NOT NULL DEFAULT ((0)),
    [created] [datetime2](7) NULL DEFAULT (getdate())
CONSTRAINT [PK_files_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [files$estructure_unique] UNIQUE NONCLUSTERED 
(
    [year] ASC,
    [name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[files]  WITH NOCHECK ADD  CONSTRAINT [FK_files_client] FOREIGN KEY([cid])
REFERENCES [dbo].[clients] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[files] CHECK CONSTRAINT [FK_files_client]
GO

value_text table:

CREATE TABLE [dbo].[value_text](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_file] [bigint] NOT NULL DEFAULT ((0)),
    [id_field] [bigint] NOT NULL DEFAULT ((0)),
    [value] [nvarchar](255) NULL DEFAULT (NULL),
    [id_doc] [bigint] NULL DEFAULT (NULL)
 CONSTRAINT [PK_value_text_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[value_text]  WITH NOCHECK ADD  CONSTRAINT [FK_valuesT_field] FOREIGN KEY([id_field])
REFERENCES [dbo].[fields] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[value_text] CHECK CONSTRAINT [FK_valuesT_field]
GO
  • Are rows inserted on a regular basis throughout the day, or in a batch process? How are they queried? Are they ever updated, or just inserted? – StrayCatDBA Mar 2 '18 at 16:24
  • @StrayCatDBA The rows will be inserted in a batch process. Each insert will have 1000 rows. They could be updated. – RuSSe Mar 5 '18 at 7:08

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.