在MS SQL 2000和2005中,给定日期时间(例如‘2008-09-25 12:34:56’),获取仅包含‘2008-09-25’的日期时间的最有效方法是什么?
我必须承认,我以前从未见过Matt展示的下浮率转换。我不得不对此进行测试。
我测试了一个纯选择(它将返回日期和时间,而这不是我们想要的),这里是统治性解决方案(floor-float),这里提到了一个普通的“天真”方案(stringconvert),而这里提到的是使用(因为我认为这是最快的)。
我在运行Win 2003 SP2服务器的测试服务器MS SQL Server 2005上测试了查询,该服务器运行的Xeon 3GHz CPU在最大内存上运行(32位,因此约为3.5 Gb)。那天晚上我在这里,所以机器几乎没有负载空转。我已经把一切都交给了我自己。
这是我的测试运行中的日志,该日志是从一个大表中选择的,该表包含从毫秒到毫秒的时间戳。这个特定的数据集包含2.5年以上的日期。该表本身有1.3亿行,所以这就是为什么我限制在前100万行中的原因。
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2 SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2 SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2 SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. (1000000 row(s) affected) Table ‘tblMeasureLogv2’. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms. (1000000 row(s) affected) Table ‘tblMeasureLogv2’. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms. (1000000 row(s) affected) Table ‘tblMeasureLogv2’. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms. (1000000 row(s) affected) Table ‘tblMeasureLogv2’. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
(1000000 row(s) affected) Table ‘tblMeasureLogv2’. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
我们在这里看到什么?
让我们专注于CPU时间(我们正在研究转换),我们可以看到我们有以下数字:
Pure-Select: 422 Floor-cast: 625 String-conv: 1953 DateAdd: 531
由此看来,DateAdd(至少在这种情况下)比现场直播方法要快一些。
在您去那里之前,我多次运行了此测试,查询的顺序已更改,结果相同。
这在我的服务器上有点奇怪吗?