大量in参数改成临时表，反而更慢！

[随风201333]

对于大量in参数，比如下面的，还有not in如何优化呢？我缩减了in 里面的参数，实际里面有更多的参数：
select * FROM [dbo].[UserFriend] AS [Extent1]
LEFT OUTER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[friend_thUserId] = [Extent2].[id]
WHERE ([Extent1].[userId] IN (5445,87411,2373,1194,3209,51023,3101,312354,278412)) AND
( NOT ([Extent1].[friend_thUserId] IN (5445,87411,2373,1194,3209,51023,3101,312354,278412) )) AND
([Extent1].[friend_thUserId] <> @p__linq__0)

新建了个临时表，插入数据进去，不考虑插入临时表的时间，然后作join， 反而更慢了， 而且结果也不对，先帮忙看看如何改写成exists 格式，另外看看如何优化？ friend_thUserId,id,userId都有index

create table #tmp_friend ( userId int)
insert into #tmp_friend select 5445 union all select 87411。。。

SELECT ＊
FROM [dbo].[UserFriend] AS [Extent1]
LEFT OUTER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[friend_thUserId] = [Extent2].[id]
WHERE not exists ( select 1 from #tmp_friend t where [Extent1].friend_thUserId = t.userid)
and exists ( select 1 from #tmp_friend t where [Extent1].userId = t.userid)
AND
([Extent1].[friend_thUserId] <> @p__linq__0)

[LongRui888]

引用 5 楼 iamsea22 的回复:

第一个是exists 的，能拿到正确结果的

改成这样试试： create table #tmp_friend ( userId int) insert into #tmp_friend select 5445 union all select 87411。。。 SELECT * FROM [dbo].[UserFriend] AS [Extent1] LEFT hash OUTER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[friend_thUserId] = [Extent2].[id] WHERE not exists ( select 1 from #tmp_friend t where [Extent1].friend_thUserId = t.userid) and exists ( select 1 from #tmp_friend t where [Extent1].userId = t.userid) AND ([Extent1].[friend_thUserId] <> @p__linq__0)

[随风201333]

应该不用select ＊，这个再让dev 改进，但是瓶颈貌似不在这，我手工减到1列后，只是快了1秒。 对于加hash，好像不行。原来是： LEFT OUTER JOIN ，返回结果不对，改成 Join 才对。这样加hash语法报错～

[随风201333]

现在是： FROM [dbo].[UserFriend] AS [Extent1] --LEFT OUTER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[friend_thUserId] = [Extent2].[id] where 。。。 得到正确结果

[發糞塗牆]

你的瓶颈是键值查找，这是*号导致的。所以问你是不是非要用*

[發糞塗牆]

引用 9 楼 iamsea22 的回复:

现在是： FROM [dbo].[UserFriend] AS [Extent1] --LEFT OUTER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[friend_thUserId] = [Extent2].[id] where 。。。 得到正确结果

先把代码完善再看执行计划，比如指查询所需的列，然后再生成执行计划

[發糞塗牆]

改了这么多次我都不知道你现在的执行计划是怎样的了

[随风201333]

奧，明白了，谢谢。 现在我手工减到只select 1列后，快了1秒。还有其他方法吗？

[發糞塗牆]

上现在最后查询的执行计划看看

[發糞塗牆]

如果你的执行计划还有key lookup，把鼠标移到上面，看看主要是因为哪些列导致的，然后把这些列加一个非聚集索引

[随风201333]

这是最新的计划， 我只select 1列 from 。。。

[happytonice]

用表变量能快吗？

[随风201333]

表变量也一样

[發糞塗牆]

这两个列上面是否有非聚集索引？[Extent1].[friend_thUserId] ， [Extent2].[id]

[随风201333]

[Extent1].[friend_thUserId] 有非聚集， [Extent2].[id] 是聚集索引，无非聚集。 是否把friend_thUserId 改成聚集？ 但是这列有许多null 值，而且也不唯一。 还是 [Extent2].[id] 上加个非聚集？

[發糞塗牆]

1表的friend_thuserid和userid加个联合非聚集索引，2表的id暂时不用管，#tmp_friend的userid要有一个非聚集索引

[發糞塗牆]

回复请引用一下

[随风201333]

引用 21 楼 DBA_Huangzj 的回复:

1表的friend_thuserid和userid加个联合非聚集索引，2表的id暂时不用管，#tmp_friend的userid要有一个非聚集索引

谢谢，表很大，正在创建中。。。

[随风201333]

引用 23 楼 iamsea22 的回复:

[quote=引用 21 楼 DBA_Huangzj 的回复:] 1表的friend_thuserid和userid加个联合非聚集索引，2表的id暂时不用管，#tmp_friend的userid要有一个非聚集索引

谢谢，表很大，正在创建中。。。[/quote] DBA_Huangzj ，真帅呆了，老大，建好后只要1，2秒！ 那么如果select 有其他列，只要加到 include 列就行了吧？另外，friend_thuserid和userid 分别有单独索引，看来没有这个组合索引好？ 能否删除2个单独索引？ 如果某查询条件只有userid的话，能否用到此联合索引？

[發糞塗牆]

引用 24 楼 iamsea22 的回复:

[quote=引用 23 楼 iamsea22 的回复:] [quote=引用 21 楼 DBA_Huangzj 的回复:] 1表的friend_thuserid和userid加个联合非聚集索引，2表的id暂时不用管，#tmp_friend的userid要有一个非聚集索引

谢谢，表很大，正在创建中。。。[/quote] DBA_Huangzj ，真帅呆了，老大，建好后只要1，2秒！ 那么如果select 有其他列，只要加到 include 列就行了吧？另外，friend_thuserid和userid 分别有单独索引，看来没有这个组合索引好？ 能否删除2个单独索引？ 如果某查询条件只有userid的话，能否用到此联合索引？[/quote]加include基本上就可以了。绝大部分情况下我不建议单独索引，如果查询只有userid，而且组合索引的第一列就是userid，是无所谓的。不过如果userid是非第一列，那就要单独索引了

[發糞塗牆]

你引用错了，难怪我没看到，别引用自己.....

引用 24 楼 iamsea22 的回复:

[quote=引用 23 楼 iamsea22 的回复:] [quote=引用 21 楼 DBA_Huangzj 的回复:] 1表的friend_thuserid和userid加个联合非聚集索引，2表的id暂时不用管，#tmp_friend的userid要有一个非聚集索引

谢谢，表很大，正在创建中。。。[/quote] DBA_Huangzj ，真帅呆了，老大，建好后只要1，2秒！ 那么如果select 有其他列，只要加到 include 列就行了吧？另外，friend_thuserid和userid 分别有单独索引，看来没有这个组合索引好？ 能否删除2个单独索引？ 如果某查询条件只有userid的话，能否用到此联合索引？[/quote]