复杂慢sql语句如何优化
环境 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语句该如何优化
?