条件列查询的更好方法 [英] Better approach of conditional column query

问题描述

--OldQuery
SELECT oldStartDate AS column,...
FROM Table
WHERE
oldStartDate > @date,...
GROUP BY
oldStartDate,...

有一个新列 newStartDate 可以空添加到表中，数据提取必须有条件地依赖于该列

There is a new column newStartDate nullable added to the table and data extraction has to be conditionally depending on this column

--NewQuery
SELECT ISNULL(newStartDate, oldStartDate) AS column,...
FROM Table
WHERE
(    
    (newStartDate IS NULL AND oldStartDate > @date)
    OR 
    (newStartDate > @date)
),...
GROUP BY
ISNULL(newStartDate, oldStartDate),...

以上是我能想到的有条件检索的方法，但在我看来这会导致性能问题，我无法在 DEV 环境中进行评估，因为体积不够大，但产量正在粗略估计200 万条记录.想知道是否还有其他推荐的方法?

Above is the approach I can think of to retrieve conditionally however it seems to me will cause performance issue and I can't evaluate in DEV environment as volume is not big enough but production is estimating roughly 2mil records. Wondering if there is any other recommended approach instead?

推荐答案

如果你打算在 GROUP BY 子句中使用 ISNULL 你也可以在在 WHERE 子句中，因为两者都会使 newStartDate 不可 sargable，因为该列可以为空.(感谢提供链接，John Cappelletti！)

If you are going to use ISNULL in the GROUP BY clause you might as well also use in in the WHERE clause, since both would make the newStartDate un sargable, because the column is nullable. (Thanks for the link, John Cappelletti!)

假设 NewStartDate 将替换 OldStartDate，您可以做的一件事是将 newStartDate 中的所有空值更新为非oldStartDate 的空值，然后将您的 newStartDate 列更改为不可为空的列:

Assuming the NewStartDate will replace the OldStartDate, one thing you can do to is update all the null values in newStartDate to the non-null values of oldStartDate, and then alter your newStartDate column as a non-nullable column:

UPDATE TableName
SET NewStartDate = OldStartDate
WHERE NewStartDate IS NULL;
GO

ALTER TABLE TableName
    ALTER COLUMN NewStartDate date NOT NULL;
GO

那么您的查询就可以更简单和更可靠:

Then your query can be much simpler and sargable:

SELECT NewStartDate AS column,...
FROM Table
WHERE
NewStartDate > @date,...
GROUP BY
NewStartDate ,...

这篇关于条件列查询的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！