如何加快返回范围内的空值和日期的查询 [英] How to speed up query that returns nulls and dates within a range

问题描述

好的，这是您的另一个查询优化。我知道这个存储过程会加入很多表。它的目的是搜索在线应用程序。所以他们转到页面，他们有2个字段为Age。他们想要0到10天之间的所有数据。为年龄加入的表必须是左连接，因为有时没有记录但我们想要包括基表中的所有记录。所以我需要找出像Getdate（）& amp; Getdate（） - 10.然后我不得不说给我0和0之间的所有记录。 10.工作正常，但如果你没有进入年龄，你应该得到所有记录，包括日期栏中有空的记录。所以我必须设置一个范围，然后说OR IS NULL。或者我必须在where子句中使用一个函数来替换null - no !!，或者我应该能够使用case语句。我不确定最好的办法是什么。如果我使用case语句它会在8秒内运行，但它不包括Null。如果我使用OR。它使运行时间增加了一倍多。


请看我的例子。

Okay here is another piece of query optimization for you guys. I have this stored procedure as you know which joins many tables. It''s purpose is to be a search for an online application. So they go to the page and they have 2 fields for Age. They want all data between 0 and 10 days old. The table that joins for age has to be a left join because there are sometimes no records but we want to include all records from the base table. So I need to figure out the aged dates like Getdate() & Getdate() - 10. Then I have to say give me all the records that between 0 & 10. That works fine, but if you don''t enter an Age you should get all records including those with a Null in the date column. So I have to set a range and then say, "OR IS NULL". Or I have to use a function in the where clause to replace the null - no!!, or I should be able to use a case statement. I am not sure what the best way is to handle this. If I use a case statement it runs in 8 seconds but it does not include the Nulls. If I use an "OR". It more than doubles the run time.

Please see my example.

展开 | 选择 | Wrap | 行号

推荐答案

由于这是一个特定的查询，我已将其移至自己的线程。


您可以使用ISNULL（）函数将空值更改为GETDATE（）。如果因为它在其中一个字段上运行而太慢，那么尝试一个联合所有的方法。


在附注中，case语句并不是你做的认为它确实如此。 null不= null。对于null比较，您必须使用IS NULL。你不能使用NULL = NULL。

Since this is about a specific query, I''ve moved it to its own thread.

You can use the the ISNULL() function to change the null values to GETDATE(). If that''s too slow because it operates on one of the fields, then try a union all approach.

On a side note, the case statement doesn''t do what you think it does. A null does not = null. For null comparions, you have to use IS NULL. You can''t use NULL = NULL.

谢谢。那不行。我不确定这里到底发生了什么。 CASE是否以与IN语句相同的方式排除空值？


我觉得我不明白这是如何在内部发挥作用的。


好​​吧，我完全正在努力积极地做这件事。这是因为我必须在谓词的左侧使用isNull，但这是一次性能灾难吗？

Thanks. That doesn''t work. I am not sure what''s going here. Does CASE exclude nulls in the same way that an IN statement would?

I feel I am not understanding how this is working internally.

Okay I am totally struggling with being positive with this thing. It''s because I have to use the isNull on the left side of the predicate, but is that a performance disaster?

这不是CASE语句'不包括空值。由于你试图比较它们，它被排除在外。你误解了NULL意味着什么。 NULL并不意味着没有值。 NULL可以是任何东西。两个NULL不太可能相等。
NULL = NULL 将始终返回FALSE。这不是一个真实的陈述。你的CASE语句基本上是在对NULL进行比较。


我不称之为灾难本身。但由于需要进行计算，性能肯定会受到影响。你尝试过UNION ALL方法吗？你没有说你是否这样做。

It''s not the CASE statement that''s excluding NULLs. It''s being excluded because of how you''re trying to compare them. You misunderstand what NULL means. NULL doesn''t mean that there''s an absence of a value. A NULL can be anything. It is unlikely for two NULLs to be equal.
NULL = NULL will always return FALSE. This is not a true statement. Your CASE statement is basically doing that comparison on NULLs.

I wouldn''t call it a disaster per se. But performance will definitely suffer because of the calculation that needs to be done. Did you try the UNION ALL method? You didn''t say whether or not you did.

这篇关于如何加快返回范围内的空值和日期的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！