CODEWITHSUNDEEP
c#sql-serverindexingsql-server-2012large-data
Sebastian Ptak
Sebastian Ptak

Reputation: 45

Frequently repopulated large tables indexes

I've got a set of data (2M+ rows) that i need to repopulate every couple of minutes. The data also has to be accessed during that time, so we have this solution:

When the view points to Table_First we:

Do you think this is a good design, or could we do something better?

I'm thinking that after each rebuild of the indexes the statistics get lost and every query that selects data from TableView is being recompiled. Should i worry about it?

Another this is that the rebuild actually sometimes takes more time than the data insert.

As an alternative we could just insert the rows to a temp table (or a persistent table) and just update the data using MERGE with INSERT,UPDATE and DELETE.

The table looks like this:

CREATE TABLE [dbo].[Table_First](
[GroupId] [int] NOT NULL,
[ItemId] [int] NOT NULL,
[SKU] [nvarchar](255) NOT NULL,
[PropertyId] [int] NOT NULL,
[StringValue] [nvarchar](500) NULL,
[DecimalValue] [float] NULL,
[PropertyValueId] [int] NULL) ON [PRIMARY]

CREATE CLUSTERED INDEX [IX_Index1] ON [dbo].[Table_First]
(
    [GroupId] ASC,
    [PropertyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [IX_Index2] ON [dbo].[Table_First]
(
    [SKU] ASC,
    [PropertyId] ASC
)
INCLUDE ([PropertyValueId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

The data inside the table is unfortunately not unique.

Upvotes: 1

Views: 53

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7712

Your solution might be viable, though it would depend on many aspects, such as your reading load. The main bottlenecks here are:

  • Sch-M lock placed on the view by the ALTER VIEW statement, during which no other session can access it. If you can live with it, fine;
  • Alteration of the view will lead to recompilation of all queries that use it. If your queries are few, it might be tolerable.

However, there are some alternatives.

You can enable a read committed snapshot isolation (RCSI) in the database where the table is resided. After that, all described above will be performed by SQL Server itself, behind the curtains.

You can employ table partitioning to do the bidding. By switching a new section in and the old section out, it will be essentially the same as your original plan, though there might be some notable differences. However, not every edition of SQL Server supports table partitioning.

I didn't have much play with it myself, but starting from SQL Server 2014, there is a nice feature, namely in-memory tables. They provide reduced lock contention and disk footprint, so it might be worth investigating.

Upvotes: 1

Related Questions