【58分求教一个查询效率问题】，发现采用FULL OUTER JOIN很慢如何办？谢谢！！！！

[ntcw]

58分求教一个查询效率问题，发现采用FULL OUTER JOIN很慢如何办？谢谢！！！！

我们都知道两表关联采用INNER JOIN是效率最高的，但由于我的两个表Products（产品表)/
Portfolios(产品系列，产品组的意思)要求采用FULL OUTER JOIN才能获得两边所有的记录，
但是采用后虽然结果正确，但是发现查询速度很慢, 这种情况还有其他更好的办法可以取代
FULL OUTER JOIN的方法吗？


select
ProductId=isNull(a.ProductId,0),

portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),

productName=isNull(a.productName,'<Null>')


from Products a FULL OUTER JOIN Portfolios b on a.PortfolioId = b.PortfolioId

where a.UserId = @UserId Or
b.UserId = @UserId


谢谢！！！！

[ntcw]

谢谢！！！！

[pbsql]

要使速度快，应在连接及where所涉及的列上建立索引，即在Products.PortfolioId、Portfolios.PortfolioId、Products.UserId、Portfolios.UserId上建索引

[phantomMan]

索引应该建在连接字段上,如上面的 a.UserId 和 b.UserId ,但是由于楼主采用 or ,所以即使建立索引也不会用到索引,

[phantomMan]

对a,b表建立索引:a.UserId 和 b.UserId ,然后使用下面的语句看看速度是否变快

declare @UserID int
select ProductId=isNull(a.ProductId,0),
portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),
productName=isNull(a.productName,'<Null>')
from Products a FULL OUTER JOIN Portfolios b on a.PortfolioId = b.PortfolioId
where a.UserId = @UserId Or b.UserId = @UserId

Union all
select ProductId=isNull(a.ProductId,0),
portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),
productName=isNull(a.productName,'<Null>')
from Products a FULL OUTER JOIN Portfolios b on a.PortfolioId = b.PortfolioId
where b.UserId = @UserId

[phantomMan]

改一下:
对a,b表建立索引:a.UserId 和 b.UserId ,然后使用下面的语句看看速度是否变快

declare @UserID int
select ProductId=isNull(a.ProductId,0),
portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),
productName=isNull(a.productName,'<Null>')
from Products a FULL OUTER JOIN Portfolios b on a.PortfolioId = b.PortfolioId
where a.UserId = @UserId

Union all
select ProductId=isNull(a.ProductId,0),
portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),
productName=isNull(a.productName,'<Null>')
from Products a FULL OUTER JOIN Portfolios b on a.PortfolioId = b.PortfolioId
where b.UserId = @UserId

[ntcw]

索引早已建好, 这应当不是问题所在吧!

主要是采用FULL OUTER JOIN 造成查询速度下降, 如果用INNER JOIN就无问题了.

用UNION可能是个方法， 但感觉有些麻烦，不知还有无其他更好的解决办法呢？

[ntcw]

由于用了FULL OUTER JOIN , 因此条件上用了OR，才能保证两边的记录都能筛选出来，这也是一个造成速度下降的关键原因之一。

a.UserId = @UserId Or b.UserId = @UserId

[phantomMan]

我认为造成速度变慢的关键不是因为FULL OUTER JOIN ,它的确会增加记录集的个数;但是由于你采用了or,造成无法使用索引

[iamltd]

把or换一下吧

a.UserId = @UserId Or b.UserId = @UserId
改成@Userid in (a.UserId, b.UserId)

这样就可以利用索引了

[pbsql]

phantomMan的解释应该正确，楼主可以测试一下，不过要把union all改为union，要不然有重复

[phantomMan]

碰到一个怪事,怎么我现在测试,in,or等都是可以用到索引的呢?

我看了好多性能优化的,好像都说in,or是不能用到索引的,但是测试,好像都可以用

[ntcw]

测试发现下面这个最快，几呼是30-0毫秒

select
ProductId=isNull(a.ProductId,0),
portfolioId=isNull(b.portfolioId,0),
PortfolioName=isNull(b.PortfolioName,'未归类产品...'),
productName=isNull(a.productName,'<Null>')

from (
select portfolioId=0,PortfolioName='未归类产品...',[index]=0,userid=58188

union all

Select portfolioId,PortfolioName,[index],userid
From Portfolios
where userid=58188

) b LEFT OUTER JOIN Products a on B.PortfolioId = A.PortfolioId


where a.UserId=58188


但有一个奇怪问题，明明是左连接，但是当产品组有记录，没有对应产品时，竟然产品组记录丢失，真是搞不懂为什么？

[ntcw]

(
select portfolioId=0,PortfolioName='未归类产品...',[index]=0,userid=58188

union all

Select portfolioId,PortfolioName,[index],userid
From Portfolios
where userid=58188

) b
结果是：

0 未归类产品... 0 58188
38472 产品系列名称-B 99 58188
38471 产品系列名称-A 99 58188

[ntcw]

总的结果是：
53086 38471 产品系列名称-A 产品名称-系列A
53085 0 未归类产品... 产品名称- 未归类1


晕倒： 产品系列名称-B记录丢失？为什么呢？