Optimize SQL Queries by Adding Indices

performancequery-performancesql server

I'm optimizing my SQL Server database using SQL Server Management Studio Activity Monitor.

The two more expensive sql sentences are:

set @codesToPrintCount = 
    (select count(CodeId)
        from Code
        where CommissioningFlag = 255
        and AggregationLevelId = @codeLevel);

SET @code = (SELECT TOP 1 Serial
                FROM Code
                WHERE CommissioningFlag = 255
                    and AggregationLevelId = @codeLevel);

This is Code table sql script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

I don't know how I can speed up these sentences.

There are more rows in Code that table have the same value on AggregationLevelId column than columns with the value 255 on CommissioningFlag. In other words, on Code table there 1.050.000 rows with AggregationLevelId equals to @codeLevel and 32 rows or less with the value 255 in CommissioningFlag column.

Is it a good idea add two indices on this table? One for CommissioningFlag and another one for AggregationLevelId.

At this moment there are 1.100.000 rows on Code table and they get 23ms and 78ms respectably to execute.

By the way, these two sentences are in a stored procedure.

Best Answer

If you're not interested in rows that aren't 255, you could use a filtered index:

CREATE INDEX ix255 ON Code (AggregationLevelId)
INCLUDE (Serial, CodeId) 
WHERE CommissioningFlag = 255;

This means it will only index the ones that have 255, in AggregationLevelId order, including the values of Serial and CodeId because you need to count/return those.

If you cannot (or do not want to) use a filtered index, try the following (as originally suggested in a comment):

CREATE INDEX pick_a_name_for_the_index
ON Code (CommissioningFlag, AggregationLevelId) 
INCLUDE (Serial, CodeId);
Related Question