#sql-server #datetime #indexing #casting #sql-server-2016
#sql-сервер #дата-время #индексирование #Кастинг #sql-server-2016
Вопрос:
У меня есть две таблицы, обе из которых содержат миллионы строк данных.
tbl_one:
purchasedtm DATETIME,
userid INT,
totalcost INT
tbl_two:
id BIGINT,
eventdtm DATETIME,
anothercol INT
Первая таблица имеет кластеризованный индекс в первых двух столбцах: CLUSTERED INDEX tbl_one_idx ON(purchasedtm, userid)
Второй сервер имеет первичный ключ в столбце ID, а также некластеризованный индекс в eventdtm столбце.
Я хочу запустить запрос, который ищет строки, в которых purchasedtm и eventdtm находятся в один и тот же день.
Изначально я написал свой запрос как:
WHERE CAST(tbl_one.purchasedtm AS DATE) = CAST(tbl_two.eventdtm AS DATE)
Но при этом не собирался использоваться ни один из двух индексов.
Позже я изменил свой запрос на этот:
WHERE tbl_one.purchasedtm >= CAST(tbl_two.eventdtm AS DATE)
AND tbl_one.purchasedtm < DATEADD(DAY, 1, CAST(tbl_two.eventdtm AS DATE))
Таким образом, поскольку только одна сторона сравнения обернута в функцию, другая сторона все еще может использовать ее индекс. Правильно?
У меня также есть несколько дополнительных вопросов:
- Я могу написать запрос и наоборот, то есть оставить
tbl_two.eventdtmнетронутым и обернутьtbl_one.purchasedtmвCAST(). Повлияет ли это на производительность? - Если ответ на предыдущий вопрос положительный, это потому, что
eventdtmимеет свой собственный выделенный индекс, при поискеpurcahsedtmсовпадение индексов будет только частичным? - Есть ли другие факторы, которые я могу принять во внимание, чтобы решить, какой из двух вариантов лучше? (Например, если в
tbl_oneфайле миллионы строк, а вtbl_two— миллиарды, повлияет ли это на то, какой столбец я должен привести, а какой нет?) - В целом, если вы сравните два столбца, которые оба проиндексированы, получим ли мы какую-либо производительность по сравнению с аналогичным сценарием, в котором индексируется только один из них?
- И, наконец, могу ли я выполнить свою первоначальную задачу без использования CAST?
Примечание: У меня нет возможности создавать или изменять индексы, добавлять столбцы и т.д.
Комментарии:
1.
WHERE CAST(tbl_one.purchasedtm AS DATE) = CAST(tbl_two.eventdtm AS DATE)«Но при этом не собирался использоваться ни один из двух индексов». Неправильно.CAST([column] AS date)есть ли SARGable/ ли SARGable функции в SQL Server2. В идеале идентификатором пользователя должен быть PK для таблицы 1, и у вас должна быть другая таблица со всеми покупками. Вы объединяете их в полях IT? Если это так, то изменение порядка этого кластеризованного ключа помогло бы.
3. Использует ли ваш пересмотренный запрос индекс?
4. @Larnu Я этого не знал! Очень интересно. Я прочитаю больше и обновлю свой вопрос. Но можете ли вы ответить на это в контексте, не являющемся саргируемым? (например, если я использовал ПРИВЕДЕНИЕ, которое не было SRGable, или функцию, отличную от ПРИВЕДЕНИЯ)
5. Большую часть времени применение любой функции к столбцу в вашем
WHEREсделает его недоступным для саргирования. Единственное, о чем я могу думать, на самом деле SARGable — этоCAST({column},AS date). Я точно не помню, но я думаю,CONVERT(int,DecimalColumn)что это возможно. Наиболее распространенная вещь, которую вы (я) видите, — это такие вещи, какWHERE ISNULL(MyColumn,0) = ISNULL(@MyVariable,0)которая не поддается саргированию. Что-то подобное было бы лучше написать с использованием булевой логикиWHERE (MyColumn = @MyVariable OR (MyColumn IS NULL AND @MyVariable IS NULL)).
Ответ №1:
Немного. поздно после комментария, но…
Как обсуждалось в комментариях, такой код, как CAST(DateTimeColumn AS date) , на самом деле доступен для редактирования. Роб Фарли опубликовал статью о некоторых настраиваемых и несаргируемых функциях здесь, однако я все равно расскажу о нескольких вещах.
Во-первых, применение функции к столбцу обычно делает ваш запрос недоступным для саргирования, и особенно, если это изменяет порядок значений или их упорядоченность бессмысленна. Возьмите что-то вроде:
SELECT *
FROM TABLE
WHERE RIGHT(COLUMN,5) = 'value';
Порядок значений в столбце здесь совершенно бесполезен, поскольку мы фокусируемся на правосторонних символах. К сожалению, как также обсуждает Роб:
SELECT *
FROM TABLE
WHERE LEFT(COLUMN,5) = 'value';
Это также не поддается проверке. Однако как насчет следующего?
SELECT *
FROM TABLE
WHERE Column LIKE 'value%';
Это связано с тем, что логика не применяется к столбцу, и порядок не меняется. Если значение wehre '%value%' , то это тоже было бы недопустимым.
При применении логики, которая добавляет (или вычитает) то, что вы хотите найти, вы всегда хотите применить это к буквальному значению (или функции, такой как GETDATE()`). Например, одно из этих выражений является саргируемым, а другое — нет:
Column 1 = @Variable --non-SARGable
Column = @Variable - 1 --SARGable
То же самое относится к таким вещам, как DATEADD
@DateVariable BETWEEN DateColumn AND DATEADD(DAY, 30,DateColumn) --non-SARGable
DateColumn BETWEEN DATEADD(DAY, -30, @DateVariable) AND @DateVariable --SARGable
Изменение типа данных (отличного от a date ) редко позволяет выполнить запрос. CONVERT(date,varchardate,112) не будет изменяться, даже если порядок столбцов не изменился. Преобразование decimal в int , однако, дало тот же результат, что и преобразование a datetime в a date , и сохранило сопоставимость:
CREATE TABLE testtab (n decimal(2,1) PRIMARY KEY CLUSTERED);
INSERT INTO testtab
VALUES(0.1),
(0.3),
(1.1),
(1.7),
(2.4);
GO
SELECT n
FROM testtab
WHERE CONVERT(int,n) = 2;
GO
DROP TABLE testtab;
Надеюсь, этого вам достаточно для продолжения, но я спрашиваю, хотите ли вы, чтобы я добавил что-нибудь еще.