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.