Выберите обработку оператора перед предложением Where в поле зрения

У меня есть оператор, который выбирает подстроку charindex следующим образом:

SELECT SUBSTRING(StringField, 5, CHARINDEX('ABC', StringField) - 5)...
WHERE 
CHARINDEX('ABC', StringField) > 5

Когда я запускаю приведенный выше оператор в запросе на выборку, результаты возвращаются просто отлично. Когда я запускаю приведенный выше оператор в индексированном представлении, привязанном к схеме, я получаю эту ошибку:

Invalid length parameter passed to the LEFT or SUBSTRING function

Чтобы решить эту проблему, я напишу функцию для получения максимального значения CharIndex и 0, чтобы исключить возможность отрицательного значения. Но кто-нибудь знает, почему предложение where не отфильтровывает оператор select?


sql database database-indexes
person Lenci    schedule 24.02.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Поскольку порядок операций за кулисами в запросе не гарантируется.

Я предполагаю, что если вы проверите план выполнения, вы увидите, что он выполняет обе проверки параллельно - это потому, что ни одна операция не может использовать индекс!

В любом случае SQL должен загружать каждую строку этого поля в память, поэтому он обрабатывает их по обоим критериям одновременно.

Вы можете попробовать WITH (MAXDOP(1)) в качестве подсказки запроса, чтобы увидеть, предотвращает ли это появление проблемы, или вы можете сделать подзапрос, чтобы принудительно выполнить порядок выполнения:

SELECT SUBSTRING(StringField, 5, CHARINDEX('ABC', StringField) - 5)...
FROM (
      SELECT Stringfield 
      FROM Table 
      WHERE CHARINDEX('ABC', StringField) > 5
      ) as [X]

У меня была аналогичная проблема однажды, когда я проверял, является ли поле числовым, используя PATINDEX, и один из столбцов в моем представлении преобразовывал это в int - я получил ошибки преобразования, потому что движок преобразовывал каждую строку, поскольку мои фильтры не были SARGable.

person JNK    schedule 24.02.2012
comment
Между прочим, Джонатан Кехайяс сообщил, что видел случаи, когда даже подзапрос или CTE не помогали оптимизатору, а обработка могла происходить наоборот. Единственный обходной путь в этих случаях — либо определить план, который этого не делает, и использовать фиксированный план, либо сначала сбросить подзапрос в таблицу #temp, чтобы убедиться, что фильтрация выполняется первой. - person Aaron Bertrand; 24.02.2012

arrow_upward
1
arrow_downward

Это уродливый обходной путь, но вы могли бы сделать что-то вроде этого:

DECLARE @x TABLE(StringField VARCHAR(32));

INSERT @x SELECT 'ABC'
UNION ALL SELECT 'A'
UNION ALL SELECT 'AAAAAAAABC';

SELECT SUBSTRING(StringField, 5, CHARINDEX('ABC', StringField) - 5)
FROM @x
WHERE CHARINDEX('ABC', StringField) > 5;

SELECT SUBSTRING(StringField,
    CASE WHEN CHARINDEX('ABC', StringField) > 5 THEN 5 ELSE 1 END,
    CHARINDEX('ABC', StringField) - 
    CASE WHEN CHARINDEX('ABC', StringField) > 5 THEN 5 ELSE 0 END)
FROM @x
WHERE CHARINDEX('ABC', StringField) > 5;

Оба дают:

---
AAA

Но я подозреваю, что последнее, на ваш взгляд, будет разрешено. Это некрасиво, но, к сожалению, если вы сначала не сбросите отфильтрованные данные в таблицу #temp (или не попытаетесь проверить, надежно ли MAXDOP устранит проблему), вы не сможете контролировать порядок обработки.

Другая идея состоит в том, чтобы попробовать поместить вычисляемый столбец в таблицу (но я не уверен, что это поможет, если вы пытаетесь создать индексированное представление — могут возникнуть сложности). Или использовать отфильтрованный индекс с этим выражением вместо индексированного представления. Может быть несколько «решений», если мы знаем, для чего предназначено индексированное представление и какую версию SQL Server вы используете.

person Aaron Bertrand    schedule 24.02.2012
comment
(Кроме того, SQL Server достаточно умен, чтобы выполнять скалярный оператор компьютера только один раз для каждой строки, поэтому не думайте, что эти дополнительные операции с charindex имеют какое-либо влияние на план, продолжительность, ЦП и т. д.) - person Aaron Bertrand; 24.02.2012