复杂慢sql语句如何优化

lilinew 2010-10-19 01:22:41

环境 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语句该如何优化
？

...全文

193 点赞收藏 10

写回复

10 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

lilinew 2010-10-19

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

回复赞

lilinew 2010-10-19

我的优化发现表变量不适合这种复杂的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 2010-10-19

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次的？？？？？

回复赞