提高在where子句中使用isnull的性能

There is a report whose query is using isnull in the where clause and it's causing some performance issues. Here is a very simplified example. http://www.sqlfiddle.com/#!6/79759/11

有一个报告,其查询在where子句中使用isnull,这会导致一些性能问题。这是一个非常简单的例子。 http://www.sqlfiddle.com/#!6/79759/11

The query is using a multi-valued parameter, usually just returning one or two values. (I've tried to replicate the SSRS multi-value param using a table variable in sqlfiddle)

查询使用多值参数,通常只返回一个或两个值。 (我试图使用sqlfiddle中的表变量复制SSRS多值参数)

select 
  isnull(descrL,descr) as division,
  product
from Upper
left join Lower on product = productL
where isnull(DescrL,Descr) in (@params)

There is an Upper division that has all the products. Some of the products exist on a child division. If it exists in the lower division, that is the division that should be shown.

有一个上级部门,拥有所有产品。一些产品存在于儿童部门。如果它存在于较低的分区中,那就是应该显示的分区。

The company parameter can accept the upper division, lower division, or both.

公司参数可以接受上级,下级或两者。

Any ideas on how the query can be changed for better performance?

有关如何更改查询以获得更好性能的任何想法?

2 个解决方案

#1

select  descrL as division, product
from Lower 
where DescrL like @params + '%'
union
Select descr, product
from Upper
where Descr like @params = '%'

This would be very performant. If the match is found in the top select, it should be suppressed in the bottom select since it's a union instead of a union all. Notice the like instead of in. If you do an in there is just no way to use an index on DescrL or Descr to find the results. If you do a like, indexes are used fine. You may have to adjust your application logic to make that work.

这将是非常高效的。如果在顶部选择中找到匹配,则应在底部选择中对其进行抑制,因为它是联合而不是联合全部。请注意,而不是in。如果你执行in,则无法使用DescrL或Descr上的索引来查找结果。如果你这样做,索引就可以了。您可能必须调整应用程序逻辑才能使其正常工作。

If you can't do that, then there is this solution. You'll have to add fn_split to your db. http://msdn.microsoft.com/en-us/library/aa496058(v=sql.80).aspx

如果你不能那样做,那就有这个解决方案。您必须将fn_split添加到您的数据库。 http://msdn.microsoft.com/en-us/library/aa496058(v=sql.80).aspx

    select  descrL as division, product
    from Lower l 
    join dbo.fn_Split(@params, ',') p1
        on l.DescrL = p1.value
    union
    Select descr, product
    from Upper u 
    join dbo.fn_Split(@params, ',') p2
        on u.Descr = p2.value

If you really want to eke out the very last bit of performance you can declare a table variable and only run the fn_split once in order to populate the table variable, and then make the two joins be to the table variable. So here you are also taking advantage of the index on the columns, which is the main thing you need to make the query faster. Always do 'include actual execution plan' in sql server when you run it, and look at the results and make sure you're seeing index seeks instead of table scans.

如果你真的想要完成性能的最后一点,你可以声明一个表变量,只运行fn_split一次以填充表变量,然后使两个连接成为表变量。所以在这里你也可以利用列上的索引,这是使查询更快的主要内容。在运行它时,始终在sql server中“包含实际执行计划”,并查看结果并确保您看到索引搜索而不是表扫描。

EDIT: I went over to your sqlfiddle link. Didn't see it earlier. This works. The union by itself won't suppress dupes, since you are also selecting the division, sorry. So you have to use a not in, or as I prefer, a left outer where null.

编辑:我去了你的sqlfiddle链接。没有早点看到它。这很有效。工会本身不会压制愚蠢,因为你也选择了部门,对不起。因此,您必须使用not in或者我更喜欢左侧外部为null。

select descrL as division, productL as product
    from Lower l 
    join @params p1
        on l.DescrL = p1.division
    union
    Select u.descr, u.product
    from Upper u 
    join @params p2
        on u.Descr = p2.division
    left join (select productL as product
                from Lower l2 
               join @params p3
                 on l2.DescrL = p3.division) sub1
     on u.product = sub1.product
        where sub1.product is null
order by 2

#2

I'm not sure, but would a cte work in your situation?

我不确定,但在你的情况下是否会有效?

;with BothDivs as
(
  select 
  isnull(descrL,descr) as division,
  product
  from Upper
  left join Lower on product = productL
)
select division, product
from BothDivs
where division in (@params)

#1

select  descrL as division, product
from Lower 
where DescrL like @params + '%'
union
Select descr, product
from Upper
where Descr like @params = '%'