Использование CASE в предложении Where для выбора различных столбцов снижает производительность.

У меня есть запрос, который должен быть динамическим для некоторых столбцов, то есть я получаю параметр, и в соответствии с его значением я решаю, какой столбец выбрать в моем предложении Where. Я реализовал этот запрос, используя выражение CASE:

(CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
>= DATEADD(mi, -@TZOffsetInMins, @sTime) 
 AND (CASE @isArrivalTime WHEN 1 THEN ArrivalTime ELSE PickedupTime END) 
 < DATEADD(mi, -@TZOffsetInMins, @fTime)

Если @isArrivalTime = 1, то выберите столбец ArrivalTime, иначе выберите столбец PickedupTime. У меня есть кластеризованный индекс на ArrivalTime и некластеризованный индекс на PickedupTime.

Я заметил, что когда я использую этот запрос (с @isArrivalTime = 1), моя производительность намного хуже по сравнению с использованием только ArrivalTime.

Может быть, оптимизатор запросов не может правильно использовать\выбирать индекс таким образом?

Я сравнил планы выполнения и заметил, что когда я использую CASE, 32% времени тратится впустую на сканирование индекса, но когда я не использовал CASE(just usedArrivalTime`) только 3% было потрачено впустую на это сканирование индекса.

Кто-нибудь знает причину этого?


sql database sql-server database-performance sql-server-2005
person Community    schedule 16.09.2012    source источник

Ответы (2)


arrow_upward
0
arrow_downward

попробуйте установить границу даты и времени:

declare @resSTime datetime
        ,@resFTime datetime

set @resSTime = DATEADD(mi, -@TZOffsetInMins, @sTime)
set @resFTime = DATEADD(mi, -@TZOffsetInMins, @fTime)

и попробуй поменять регистр на "или"

( ArrivalTime >= @resSTime 
    and ArrivalTime < @resFTime 
    and @isArrivalTime = 1 )
or ( PickedupTime >= @resSTime 
    and PickedupTime < @resFTime 
    and @isArrivalTime <> 1 )
person Xordal    schedule 18.09.2012

arrow_upward
3
arrow_downward

Разница между ними, скорее всего, связана с разным количеством строк, которые он должен сканировать. Поскольку ваш запрос является вероятным конечным результатом объединения нескольких таблиц. Имейте в виду, что сканирование — это... ну, как следует из его названия, сканирование всех строк. Вы можете проверить в плане предполагаемое количество рядов, чтобы иметь представление.

Если вы используете фильтр для динамического поля (например, с использованием регистра), индекс этого поля не может использоваться механизмом. В таком случае вам лучше использовать динамический sql для создания правильного sql, который вам нужен, и использовать exec_sql. Или используйте операторы if, чтобы напрямую запрашивать нужные вещи.

Вы можете найти это полезным для использования динамического sql, это объяснит, почему и как.

http://www.sommarskog.se/dynamic_sql.html

person Dumitrescu Bogdan    schedule 16.09.2012