Я не решил эту проблему с помощью хранимой процедуры, но мы решили превзойти SP и просто выполнить простой OL SQL
См. схему расширенной таблицы ниже
Изменить 2: обновить индекс (чтобы больше не использовать actieGroep)
NB. SQL Server 2005 Enterprise 9.00.4035.00
NB2. Кажется, связано с http://www.sqlservercentral.com/Forums/Topic781451-338-1.aspx
У меня есть два индекса на таблице:
- Кластеризованный индекс PK для statistiekId
- Некластеризованный индекс для foreignId
И у меня есть следующий фрагмент кода:
DECLARE @fid BIGINT
SET @fid = 873926
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
Это выполняется так, как должно; он указывает на правильный индекс, и все, что он делает, - это сканирование индекса.
Теперь я создаю хранимую процедуру:
ALTER PROCEDURE MyProcedure (@fid BIGINT)
AS BEGIN
SELECT foreignId
FROM STAT_Statistieken
WHERE foreignId = @fid
END
Запуск:
EXEC MyProcedure @fid = 873926
Теперь он запускает сканирование индексов кластеризованного индекса в моем индексе PK! Wtf продолжается?
Итак, я изменил SP на
SELECT foreignId
FROM STAT_Statistieken
WITH (INDEX(IX_STAT_Statistieken_2))
WHERE foreignId = @fid
И теперь это дает: процессор запросов не смог создать план запроса из-за подсказок, определенных в этом запросе. Повторите запрос без указания подсказок и без использования SET FORCEPLAN. Хотя такая же функция работает так же, как и при выполнении этого непосредственно.
Дополнительная информация: полная схема, которая может воспроизвести это поведение (английские имена в комментариях)
Таблица
CREATE TABLE [dbo].[STAT_Statistieken](
[statistiekId] [bigint] IDENTITY(1,1) NOT NULL,
[foreignId] [bigint] NOT NULL,
[datum] [datetime] NOT NULL, --date
[websiteId] [int] NOT NULL,
[actieId] [int] NOT NULL, --actionId
[objectSoortId] [int] NOT NULL, --kindOfObjectId
[aantal] [bigint] NOT NULL, --count
[secondaryId] [int] NOT NULL DEFAULT ((0)),
[dagnummer] AS (datediff(day,CONVERT([datetime],'2009-01-01 00:00:00.000',(121)),[datum])) PERSISTED, --daynumber
[actieGroep] AS (substring(CONVERT([varchar](4),[actieId],0),(1),(1))) PERSISTED,
CONSTRAINT [STAT_Statistieken_PK] PRIMARY KEY CLUSTERED --actionGroup
(
[statistiekId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Индекс
CREATE NONCLUSTERED INDEX [IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId] ON [dbo].[STAT_Statistieken]
(
[foreignId] ASC,
[dagnummer] ASC,
[actieId] ASC,
[secondaryId] ASC
)WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]
Исполнение
SET NOCOUNT ON;
DECLARE @maand INT, @jaar INT, @foreignId BIGINT
SET @maand = 9
SET @jaar = 2009
SET @foreignId = 828319
DECLARE @startDate datetime, @endDate datetime
SET @startDate = DATEADD(month, -1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
SET @endDate = DATEADD(month, 1, CONVERT(datetime,CAST(@maand AS varchar(3))+'-01-'+CAST(@jaar AS varchar(5))))
DECLARE @firstDayDezeMaand datetime
SET @firstDayDezeMaand = CONVERT(datetime, CAST(@jaar AS VARCHAR(4)) + '/' + CAST(@maand AS VARCHAR(2)) + '/1')
DECLARE @daynumberFirst int
set @daynumberFirst = DATEDIFF(day, '2009/01/01', @firstDayDezeMaand)
DECLARE @startDiff int
SET @startDiff = DATEDIFF(day, '2009/01/01', @startDate)
DECLARE @endDiff int
SET @endDiff = DATEDIFF(day, '2009/01/01', @endDate)
SELECT @foreignId AS foreignId,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevonden,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekeken,
SUM(CASE WHEN dagnummer >= @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContact,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 100 AND 199 THEN aantal ELSE 0 END) ELSE 0 END) as aantalGevondenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 200 AND 299 THEN aantal ELSE 0 END) ELSE 0 END) as aantalBekekenVorige,
SUM(CASE WHEN dagnummer < @daynumberFirst THEN (CASE WHEN actieId BETWEEN 300 AND 399 THEN aantal ELSE 0 END) ELSE 0 END) as aantalContactVorige
FROM STAT_Statistieken
WHERE
dagnummer >= @startDiff
AND dagnummer < @endDiff
AND foreignId = @foreignId
OPTION(OPTIMIZE FOR (@foreignId = 837334, @startDiff = 200, @endDiff = 300))
Статистика DBCC
Name | Updated | Rows | Rows smpl | Steps | Density | Avg. key | String index
IX_STAT_Statistieken_foreignId_dagnummer_actieId_secondaryId Oct 6 2009 3:46PM 1245058 1245058 92 0,2492834 28 NO
All Density | Avg. Length | Columns
3,227035E-06 8 foreignId
2,905271E-06 12 foreignId, dagnummer
2,623274E-06 16 foreignId, dagnummer, actieId
2,623205E-06 20 foreignId, dagnummer, actieId, secondaryId
8,031755E-07 28 foreignId, dagnummer, actieId, secondaryId, statistiekId
RANGE HI | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE ROWS
-1 0 2 0 1
1356 3563 38 1297 2,747109
8455 14300 29 6761 2,115072
И индекс используется, как показано в плане выполнения. Когда я завершу это в процедуру с помощью этих параметров:
@foreignId bigint,
@maand int, --month
@jaar int --year
И запустите его с помощью _SP_TEMP @foreignId = 873924, @maand = 9, @jaar = 2009
Он выполняет кластерное сканирование индексов!
