复杂慢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次的 ?????
回复
蓝海 2010-10-19
回复
SQLCenter 2010-10-19
(select top 0 t.ID from (----------------------------------怀疑是动态的生成的0
---------
那你要找到这个分页存储过程,优化这个存储过程才行。
回复
SQLCenter 2010-10-19
select top 0 t.ID分页用的?
----------
基本上确定是分页用的,2008优化起来就简单多了,row_number()。
回复
Mr_Nice 2010-10-19
not in (select top 0 感觉这个地方的问题比较大。
LZ用not exists看看。

另外top 0 这里好像是写错了吧。
回复
SQLCenter 2010-10-19
用cte表达式看看
回复
zsh0809 2010-10-19
这句sql相应的业务逻辑有这么复杂啊...
回复
zsh0809 2010-10-19
选中你的SQL,按CTRL + L 查看执行计划,看看有没有用到Index SCAN,
如果是table Scan则必须建立相应的Index。

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-19 01:22
社区公告
暂无公告