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);
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
Is it a good idea add two indices on this table? One for
CommissioningFlag and another one for
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.