问题描述
想象一下下面的简单查询,我得到了过去 3 天内创建的所有用户的列表,逻辑或示例并不重要
Imagine the following simple query, where I get a list of all users that were created within the last 3 days, the logic or example is not important
SELECT ... , DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) ... FROM dbo.AspNetUsers U WHERE DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3
我重复了一些代码 DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) <3,当我有更复杂的上述示例时,我不想维护两次或多次需要该逻辑.
I've repeated some code DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3 which, when I have much more complicated examples of the above I don't want to maintain twice or however many times I need that logic.
考虑到性能,应该如何处理这个问题?
How should one go about dealing with this with performance in mind?
谢谢
推荐答案
如果您考虑到性能,那么您最好在需要时重复表达式.具体来说,不要试图将它们放在用户定义的函数中.众所周知,它们会使 SQL Server 中的查询变慢.
If you have performance in mind, then you'd better repeat expressions when needed. Specifically, don't try to put them in a user-defined functions. They are known to make queries slow in SQL Server.
话虽如此,SQL Server 中至少有两种方法可以在不影响性能的情况下使查询更具可读性:
Having said that, there are at least two methods in SQL Server to make queries more readable without affecting the performance:
- CTE
- 交叉申请
CTE 使用示例:
WITH CTE AS ( SELECT ... , DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) AS CalculatedColumn ... FROM dbo.AspNetUsers U ) SELECT ... CalculatedColumn ... FROM CTE WHERE CalculatedColumn < 3 ;
使用CROSS APPLY的例子:
而不是在以下查询中重复部分公式:
Instead of repeating parts of the formula in the following query:
SELECT ColA + ColB AS ColSum ,ColA - ColB AS ColDiff ,(ColA + ColB) * (ColA - ColB) AS Result ,(ColA + ColB) * (ColA - ColB) * 100.0 AS Percentage FROM Table
你可以用CROSS APPLY这样写:
SELECT ColSum ,ColDiff ,Result ,Result * 100.0 AS Percentage FROM Table CROSS APPLY ( SELECT ColA + ColB AS ColSum ,ColA - ColB AS ColDiff ) AS A1 CROSS APPLY ( SELECT ColSum * ColDiff AS Result ) AS A2
<小时>
顺便说一下性能,
By the way, speaking about performance,
WHERE DATEDIFF(DAY, U.DateCreated, GETUTCDATE()) < 3
很糟糕,因为它不能在 DateCreated 上使用索引(因为您将列包装在一个函数中).
is terrible, because it can't use index on DateCreated (because you wrapped the column in a function).
你最好把它改写成这样
WHERE U.DateCreated > DATEADD(DAY, -3, GETUTCDATE())