Geek Answers Handbook

Overhead for single columns and multi-column indexes

Hi, I wondered what the performance overhead is like single columns and multi-column indexes in terms of inserts. So, for example, if I have 3 indexes of one column, it will be better for inserts into this table, and not with 1 index with several columns. When I talk about performance, I'm interested in raw speed.

+5
source share
4 answers

The INSERT overhead for INDEXes is usually negligible regardless of one or more columns.

If the records are not significantly different from weighting, always indicate which indexes are needed to improve the performance of your queries.

In some cases, multiple indexes with a single column improve the performance of many other queries rather than a single index with multiple columns. Most often, a single index with multiple columns improves the performance of a smaller set of queries.

A general rule is to consider query performance rather than insert performance if you cannot predict or see a specific problem with the inserts.

+4
source

, - . , . , , . , . , . . , , , . . , , .

+1

3 , , 1 .

, , , 3 , :

  • B-Tree . ( ) , 3 3 .
  • B- leaf "". 1 3 , 3 ( NULL, ). - .
  • 1 B-Tree 3 .

MS SQL Server:

CREATE TABLE ONE_INDEX (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX ONE_INDEX_IE1 ON ONE_INDEX (F1, F2, F3);

CREATE TABLE THREE_INDEXES (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX THREE_INDEXES_IE1 ON THREE_INDEXES (F1);
CREATE INDEX THREE_INDEXES_IE2 ON THREE_INDEXES (F2);
CREATE INDEX THREE_INDEXES_IE3 ON THREE_INDEXES (F3);
GO



SET NOCOUNT ON
DECLARE @t DATETIME;
DECLARE @id INT;
DECLARE @count INT;
SET @count = 100000;



PRINT 'ONE_INDEX:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO ONE_INDEX VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE ONE_INDEX SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM ONE_INDEX;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';



PRINT 'THREE_INDEXES:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO THREE_INDEXES VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE THREE_INDEXES SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM THREE_INDEXES;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';
GO



DROP TABLE ONE_INDEX;
DROP TABLE THREE_INDEXES;
GO

( MS SQL Server Express 2008 R2 ) :

ONE_INDEX:
    INSERT 100000 rows: 4173 ms
    UPDATE 100000 rows: 5530 ms
    DELETE 100000 rows: 2706 ms
THREE_INDEXES:
    INSERT 100000 rows: 6640 ms
    UPDATE 100000 rows: 10436 ms
    DELETE 100000 rows: 3516 ms

@count 1000000 :

ONE_INDEX:
    INSERT 1000000 rows: 40143 ms
    UPDATE 1000000 rows: 55796 ms
    DELETE 1000000 rows: 95576 ms
THREE_INDEXES:
    INSERT 1000000 rows: 61360 ms
    UPDATE 1000000 rows: 91766 ms
    DELETE 1000000 rows: 99500 ms

, outnumber write, SELECT INSERT, UPDATE DELETE. , - 3 .

.

+1

, , 3 , LastName, FirstName City, , City. .

0
source

More articles:


All Articles