SQL Server 2008查询计划警告“No Join Predicate”，查询需要很长时间

Here is my SQL

这是我的SQL

select DISTINCT d.* from Device d, Company c1 
WHERE 

EXISTS ( select * from (SELECT * FROM [dbo].[Split] ('HENNEPIN COUNTY MN', ',')) as s     
        WHERE c1.CompanyName like '%' + s.items + '%' AND d.CustomerID  c1.CompanyID)
OR  
EXISTS ( select * from (SELECT * FROM [dbo].[Split] ('HENNEPIN COUNTY MN', ',')) as s     
        WHERE d.CustomerName like '%' + s.items + '%')

In sort, it is misinterpreting the OR as a Cartesian product, you can read here: http://www.scarydba.com/2009/09/15/no-join-predicate/

在排序方面,它将OR误解为笛卡尔积,你可以在这里阅读:http://www.scarydba.com/2009/09/15/no-join-predicate/

Is there any way I can avoid this?

有什么方法可以避免这种情况吗?

1 个解决方案

#1

You're saying

select DISTINCT d.* from Device d, Company c1

But you're not joining Device and Company in any way. As a result, you'll get every record from Device paired to every record from Company and get the number of records in Device times the number of records in Company returned.

但是你没有以任何方式加入设备和公司。因此,您将从设备配对到公司的每条记录获得每条记录,并获取设备中的记录数乘以公司返回的记录数。

Since you're only returning the fields from Device, no wonder you need the DISTINCT and no wonder it's taking so long.

由于你只是从Device返回字段,难怪你需要DISTINCT并且难怪它花了这么长时间。

If that's what you really do want, you can get rid of that message by using the CROSS JOIN operation:

如果这是你真正想要的,你可以通过使用CROSS JOIN操作来消除该消息:

select d.*
from   Device d
   CROSS JOIN Company c1

See here for more about the CROSS JOIN operation.

有关CROSS JOIN操作的更多信息,请参见此处。

But we can probably make this faster. Try this:

但我们可以加快速度。尝试这个:

 ;
 WITH s AS (
    SELECT *
    FROM   [dbo].[Split] ('HENNEPIN COUNTY MN', ',') s
 )  
 SELECT  d.*
 FROM    Device d
 WHERE   EXISTS ( 
            select * 
            from   s
                JOIN Companies c1
                   ON c1.CompanyName like '%' + s.items + '%' 
           where d.CustomerID = c1.CompanyID
        )
    OR  EXISTS (
           select  *
           from    S
           where   d.CustomerName like '%' + s.items + '%'
        )

That could probably be further improved, but given that I don't know for certain what problem this query is trying to solve, that's the best I can do for now.

这可能会进一步改善,但鉴于我不确定这个查询试图解决什么问题,这是我现在能做的最好的事情。

#1