Медленный запрос, вызванный параметрами, но почему?

У меня есть 3 версии запроса, которые в конечном итоге возвращают те же результаты.

Один из них становится значительно медленнее при добавлении дополнительного внутреннего соединения в относительно маленькую таблицу AND, где переменные параметра используются внутри предложения where.

План выполнения очень отличается для быстрых и медленных запросов (приведенных ниже каждого запроса).

Я хочу понять, почему это произошло и как его предотвратить.

Этот запрос принимает < 1 секунда. Он не имеет дополнительного внутреннего соединения, но использует переменные параметра в предложении where.

    declare @start datetime = '20120115'
    declare @end datetime = '20120116'

    select distinct sups.campaignid 
    from tblSupporterMainDetails sups
    inner join tblCallLogs calls on sups.supporterid = calls.supporterid
    where calls.callEnd between @start and @end

  |--Parallelism(Gather Streams)
       |--Sort(DISTINCT ORDER BY:([sups].[campaignID] ASC))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([sups].[campaignID]))
                 |--Hash Match(Partial Aggregate, HASH:([sups].[campaignID]))
                      |--Hash Match(Inner Join, HASH:([calls].[supporterID])=([sups].[supporterID]))
                           |--Bitmap(HASH:([calls].[supporterID]), DEFINE:([Bitmap1004]))
                           |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([calls].[supporterID]))
                           |         |--Index Seek(OBJECT:([GOGEN].[dbo].[tblCallLogs].[IX_tblCallLogs_callend_supporterid] AS [calls]), SEEK:([calls].[callEnd] >= '2012-01-15 00:00:00.000' AND [calls].[callEnd] <= '2012-01-16 00:00:00.000') ORDERED FORWARD)
                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([sups].[supporterID]))
                                |--Index Scan(OBJECT:([GOGEN].[dbo].[tblSupporterMainDetails].[AUTOGEN_IX_tblSupporterMainDetails_campaignID] AS [sups]),  WHERE:(PROBE([Bitmap1004],[GOGEN].[dbo].[tblSupporterMainDetails].[supporterID] as [sups].[supporterID],N'[IN ROW]')))

Этот запрос принимает < 1 секунда. Он имеет дополнительное внутреннее соединение. BUT использует константы параметров в предложении where.

    select distinct camps.campaignid 
    from tblCampaigns camps
    inner join tblSupporterMainDetails sups on camps.campaignid = sups.campaignid
    inner join tblCallLogs calls on sups.supporterid = calls.supporterid
    where calls.callEnd between '20120115' and '20120116'

  |--Parallelism(Gather Streams)
       |--Hash Match(Right Semi Join, HASH:([sups].[campaignID])=([camps].[campaignID]))
            |--Bitmap(HASH:([sups].[campaignID]), DEFINE:([Bitmap1007]))
            |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([sups].[campaignID]))
            |         |--Hash Match(Partial Aggregate, HASH:([sups].[campaignID]))
            |              |--Hash Match(Inner Join, HASH:([calls].[supporterID])=([sups].[supporterID]))
            |                   |--Bitmap(HASH:([calls].[supporterID]), DEFINE:([Bitmap1006]))
            |                   |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([calls].[supporterID]))
            |                   |         |--Index Seek(OBJECT:([GOGEN].[dbo].[tblCallLogs].[IX_tblCallLogs_callend_supporterid] AS [calls]), SEEK:([calls].[callEnd] >= '2012-01-15 00:00:00.000' AND [calls].[callEnd] <= '2012-01-16 00:00:00.000') ORDERED FORWARD)
            |                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([sups].[supporterID]))
            |                        |--Index Scan(OBJECT:([GOGEN].[dbo].[tblSupporterMainDetails].[AUTOGEN_IX_tblSupporterMainDetails_campaignID] AS [sups]),  WHERE:(PROBE([Bitmap1006],[GOGEN].[dbo].[tblSupporterMainDetails].[supporterID] as [sups].[supporterID],N'[IN ROW]')))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([camps].[campaignID]))
                 |--Index Scan(OBJECT:([GOGEN].[dbo].[tblCampaigns].[IX_tblCampaigns_isActive] AS [camps]),  WHERE:(PROBE([Bitmap1007],[GOGEN].[dbo].[tblCampaigns].[campaignID] as [camps].[campaignID],N'[IN ROW]')))

Этот запрос занимает 2 минуты. Он имеет дополнительное внутреннее соединение И он использует переменные параметра в предложении where.

    declare @start datetime = '20120115'
    declare @end datetime = '20120116'

    select distinct camps.campaignid 
    from tblCampaigns camps
    inner join tblSupporterMainDetails sups on camps.campaignid = sups.campaignid
    inner join tblCallLogs calls on sups.supporterid = calls.supporterid
    where calls.callEnd between @start and @end

  |--Nested Loops(Inner Join, OUTER REFERENCES:([camps].[campaignID]))
       |--Index Scan(OBJECT:([GOGEN].[dbo].[tblCampaigns].[IX_tblCampaigns_isActive] AS [camps]))
       |--Top(TOP EXPRESSION:((1)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([calls].[callID], [Expr1007]) OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([sups].[supporterID], [Expr1006]) WITH UNORDERED PREFETCH)
                 |    |--Index Seek(OBJECT:([GOGEN].[dbo].[tblSupporterMainDetails].[AUTOGEN_IX_tblSupporterMainDetails_campaignID] AS [sups]), SEEK:([sups].[campaignID]=[GOGEN].[dbo].[tblCampaigns].[campaignID] as [camps].[campaignID]) ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([GOGEN].[dbo].[tblCallLogs].[IX_tblCallLogs_supporterID_closingCall] AS [calls]), SEEK:([calls].[supporterID]=[GOGEN].[dbo].[tblSupporterMainDetails].[supporterID] as [sups].[supporterID]) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([GOGEN].[dbo].[tblCallLogs].[AUTOGEN_PK_tblCallLogs] AS [calls]), SEEK:([calls].[callID]=[GOGEN].[dbo].[tblCallLogs].[callID] as [calls].[callID]),  WHERE:([GOGEN].[dbo].[tblCallLogs].[callEnd] as [calls].[callEnd]>=[@s2] AND [GOGEN].[dbo].[tblCallLogs].[callEnd] as [calls].[callEnd]<=[@e2]) LOOKUP ORDERED FORWARD)

Примечания:

Я полагаю, что медленность вызвана Clustered Index Seek на tblCallLogs, однако я не знаю, почему SQL Server выбрал бы этот план выполнения.
Должен ли я использовать подсказку оптимизатора запроса? Мне нужно было и я не хочу сказать SQL Server, как выполнять свою работу...
Проблема, по-видимому, вызвана комбинацией факторов - дополнительных переменных join AND.
Может ли план выполнения пытаться повторно использовать "плохой" план, когда он находит переменные для запроса?
В реальной жизни мне придется использовать переменные параметра. Константы бесполезны! Таким образом, эта проблема может существовать во многих моих запросах/хранимых процедурах!
Я обновил индексы и обновил статистику по tblCampaigns и tblSupporterMainDetails. Это не имело никакого эффекта.
Обе таблицы имеют кластерные индексы для первичного ключа (целое число идентификаторов).
Индексирован индексный столбец campaignid.
Все запросы используют одни и те же значения параметров - погода используется как переменная или константа.

Число записей в таблицах:

tblSupporterMainDetails = 12,561,900
tblCallLogs = 27,242,224
tblCampaigns = 756

UPDATE:

Я также перестроил индексы и обновил статистику по tblCallLogs. Нет эффекта.
Я очистил кэш плана выполнения с помощью DBCC FREEPROCCACHE
tblCallLogs.callEnd - это datetime.

Схемы задействованных столбцов:

tblCampaign.campaignid int not null
tblSupporterMainDetails.campaignid int not null
tblSupporterMainDetails.supporterid int not null
tblCallLogs.supporterid int not null
tblCallLogs.callEnd datetime not null

Индексы:

ОБНОВЛЕНИЕ 2: После добавления индекса в tblCallLogs.supporterId - с включением столбца: callEnd
"Медленный" запрос ускорился до 40 секунд. обновленный план выполнения:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([camps].[campaignID]))
   |--Index Scan(OBJECT:([GOGEN].[dbo].[tblCampaigns].[IX_tblCampaigns_isActive] AS [camps]))
   |--Top(TOP EXPRESSION:((1)))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([sups].[supporterID], [Expr1006]) WITH UNORDERED PREFETCH)
             |--Index Seek(OBJECT:([GOGEN].[dbo].[tblSupporterMainDetails].[AUTOGEN_IX_tblSupporterMainDetails_campaignID] AS [sups]), SEEK:([sups].[campaignID]=[GOGEN].[dbo].[tblCampaigns].[campaignID] as [camps].[campaignID]) ORDERED FORWARD)
             |--Index Seek(OBJECT:([GOGEN].[dbo].[tblCallLogs].[IX_tblCallLogs_supporterid_callend] AS [calls]), SEEK:([calls].[supporterID]=[GOGEN].[dbo].[tblSupporterMainDetails].[supporterID] as [sups].[supporterID]),  WHERE:([GOGEN].[dbo].[tblCallLogs].[callEnd] as [calls].[callEnd]>=[@s2] AND [GOGEN].[dbo].[tblCallLogs].[callEnd] as [calls].[callEnd]<=[@e2]) ORDERED FORWARD)

РЕШЕНИЕ:

Дополнительное соединение фактически не вызывало проблему напрямую, однако, очевидно, это изменило утверждение, так что сервер sql провел для него другой план выполнения.
Добавлением ВАРИАНТ (RECOMPILE) к концу медленного заявления я смог получить ожидаемую высокую производительность. то есть < 1 секунда. Я до сих пор не уверен, что именно это решение сработало - почему он не смыл всю работу плана? это классический случай флюирования параметров? Я обновлю это сообщение, когда узнаю точный ответ - или пока кто-нибудь не даст ясный ответ. Спасибо как @LievenKeersmaekers, так и @JNK за помощь до сих пор...

Примечания:

Число записей в таблицах:

Схемы задействованных столбцов:

Индексы:

РЕШЕНИЕ:

Ответ 1