UTC日期转换 [英] UTC Date Conversion

问题描述

查询以1秒为单位返回636行。但是，当我向其添加UTC日期过滤时，它需要超过20秒。哇！ UTC日期转换是否效率低下？我能做些什么来加快这个速度吗？我担心的是，我们的600排表
将很快增长到6000行...然后是什么？并且需要过滤以用于报告目的，并且不希望我们的业务用户在报表管理器门户中等待报告结果集超过一分钟。

AND CONVERT（DATE，

[InventoryTransactionsAll]。[创建]
时间区'UTC'时间区'东部标准时间'）
> ; = @StartDate_In

AND CONVERT（DATE，

[InventoryTransactionsAll]。[创建]
时间区域'UTC'时间区域'东部标准时间'）
< = @EndDate_In

FYI @StartDate_In和@EndDate_In参数定义为DATE。

感谢您的评论和任何建议都会非常感激。

解决方案

不，这不是每次转换这需要时间。当您更改规则时，优化程序决定使用不同的查询计划，而效率较低的查询计划会发生什么。这可能是一个运气不好的情况，但也可能是如果你
运行查询而不过滤InventoryTransactionsAll.Created，有一个索引覆盖查询，也就是说，它包括InventoryTransactionsAll的所有列，所以SQL Server确实需要访问数据页。但是该索引不包括
列Created，因此SQL Server无需访问数据页面，这需要更多时间。或者它可能决定一起忽略索引并采取完全不同的计划。

但这是我方的100％猜测。我不能说两个查询有多大意义，其中我只展示了其中一个的片段，而我对另一个完全不知情。

The query returns 636 rows in like 1-second. However, when I add the UTC Date filtering to it, it is taking over 20-seconds. WOW! Is the UTC Date Conversion that inefficient? Is there anything I can do to make this quicker? My fear is that our 600-row table will soon grow to be 6,000 rows...and then what? And need to filter for reporting purposes and DO NOT want our Business Users sitting in Report Manager Portal for more than a minute waiting for the report result set.

AND CONVERT(DATE,
[InventoryTransactionsAll].[Created] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') >= @StartDate_In
AND CONVERT(DATE,
[InventoryTransactionsAll].[Created] AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') <= @EndDate_In

FYI the @StartDate_In and @EndDate_In Parameters are defined as DATE.

Thanks for your review and any suggestions would be GREATLY appreciated.

解决方案

No, it is not the conversion per se that is taking time. What happens is that when you change the rules, the optimizer decides to use a different query plan, and one which is less efficient. It could be a case of bad luck, but it could also be that if you run the query without filtering InventoryTransactionsAll.Created, there is an index that covers the query, that is, it includes all columns from InventoryTransactionsAll, so that SQL Server does need to access the data pages. But that index does not include the column Created, so SQL Server no needs to get to the data pages, which takes more time. Or it may have decided to ignore the index all together and take a completely different plan.

But this is 100% speculation from my side. I cannot say much meaningful about two queries of which I have only been shown a fragment of one of them and I'm completely in the dark about the other.

这篇关于UTC日期转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！