复杂慢sql语句如何优化

[lilinew]

环境 sql2008 在线 nt2003
上周的job抓了2个慢sql语句

先看这个最慢的 50多秒
select top 100
t.*,
u.UserName as UserName,
u.Type as UserType,
u.DisplayName as DisplayName,
c.Code as CountryCode,
c.Name as CountryName,
cu.ID as UserCountryID,
cu.Alpha2Code as UserCountryCode,
cu.Name as UserCountryName
from
(
select
t.ID*(-1) as ID,
t.Ins as Ins,
CAST(t.Guid as varchar(64)) as SessionID,
t.UserID as SessionUserID,
t.DomainID as DomainID,
t.Login as Logon,
t.LastAccess as LastAccess,
t.Logout as Logout,
t.IsExpired as IsAutoLogout,
t.IP as UserIP,
null as ServerIP,
CASE when t.IsExpired=1 then -1 ELSE 0 END as Status,
t.Properties as Properties,
0 as Type,
t.CountryID as CountryID,
0 as IsLongSession,
t.Browser as Browser,
CASE WHEN t.RoleString='dLogin' THEN 1 ELSE 0 END as LoginType
from dbo.Session t --------------------------这表有20万
WITH(NOLOCK)

) t
INNER JOIN dbo.User u WITH(NOLOCK) ON t.SessionUserID=u.ID
LEFT JOIN Country c WITH(NOLOCK) ON t.CountryID=c.ID
LEFT JOIN Country cu WITH(NOLOCK) ON u.CountryID=cu.ID where (1=1 and t.Status=@Status1) and (t.DomainID=1) and t.ID not in
(select top 0 t.ID from (----------------------------------怀疑是动态的生成的0
select
t.*,
u.UserName as UserName,
u.Type as UserType,
u.DisplayName as DisplayName,
c.Alpha2Code as CountryCode,
c.Name as CountryName,
cu.ID as UserCountryID,
cu.Alpha2Code as UserCountryCode,
cu.Name as UserCountryName
from
(
select t.ID*(-1) as ID,
t.Ins as Ins,
CAST(t.Guid as varchar(64)) as SessionID,
t.UserID as SessionUserID,
t.DomainID as DomainID,
t.Login as Logon,
t.LastAccess as LastAccess,
t.Logout as Logout,
t.IsExpired as IsAutoLogout,
t.IP as UserIP,
null as ServerIP,
CASE when t.IsExpired=1 then -1 ELSE 0 END as ActiveStatus,
t.Properties as Properties,
0 as Type,
t.CountryID as CountryID,
0 as IsLongSession,
t.Browser as Browser,
CASE WHEN t.RoleString='ImpersonatedLogin' THEN 1 ELSE 0 END as LoginType
from dbo.Session t WITH(NOLOCK)
) t
INNER JOIN dbo.User u WITH(NOLOCK) ON t.SessionUserID=u.ID
LEFT JOIN Country c WITH(NOLOCK) ON t.CountryID=c.ID
LEFT JOIN Country cu WITH(NOLOCK) ON u.CountryID=cu.ID
where (1=1 and t.ActiveStatus=@sessionStatus1) and (t.DomainID=1)) t order by t.Logon desc )
order by t.Logon desc

我的优化思路
1 from dbo.Session t --------------------------这表有20万
WITH(NOLOCK) 里 放t.Status=@Status1

2 select top 0 t.ID from 这个 放到 表变量里。
这个复杂慢sql语句该如何优化
？

[「已注销」]

选中你的SQL,按CTRL + L 查看执行计划，看看有没有用到Index SCAN,
如果是table Scan则必须建立相应的Index。

[「已注销」]

这句sql相应的业务逻辑有这么复杂啊...

[SQLCenter]

用cte表达式看看

[Mr_Nice]

not in (select top 0 感觉这个地方的问题比较大。
LZ用not exists看看。

另外top 0 这里好像是写错了吧。

[SQLCenter]

select top 0 t.ID分页用的？
----------
基本上确定是分页用的，2008优化起来就简单多了，row_number()。

[SQLCenter]

(select top 0 t.ID from (----------------------------------怀疑是动态的生成的0
---------
那你要找到这个分页存储过程，优化这个存储过程才行。

[蓝海]

[lilinew]

select top 100 这里是个变量
select top 0 t.ID from 这里 0也是变量

怀疑这里有问题
INNER JOIN dbo.User u WITH(NOLOCK) ON t.SessionUserID=u.ID
LEFT JOIN Country c WITH(NOLOCK) ON t.CountryID=c.ID
LEFT JOIN Country cu WITH(NOLOCK) ON u.CountryID=cu.ID
很少看到同一个表会用到2次的 ？？？？？

[lilinew]

我的优化发现表变量不适合这种复杂的sql
但
测试 select top 0 t.ID from (
select
t.*, 在程序或者存储过程里可以判断 如果top0 出现这个sql d t.ID not in
(select top 0 t.ID from (
select 后面的条件都不要。 减少大sql的编译时间 在我本地测试发现提高6秒。 等会用存储过程再测试下 （表变量测试不适合）

[lilinew]

这个我准备放到临时表里做
之后再多个表join就ok了