SELECT TOP 20 a.ID, a.WB, b.GroupID
FROM tbl_WBInfo a INNER JOIN
tbl_WBGroupLink c ON a.WB = c.WBID INNER JOIN
tbl_GroupInfo b ON c.GroupID = b.GroupID
WHERE (a.WB NOT IN
(SELECT WBIDCode
FROM Sed_SendedHumanWBInfo
WHERE TaskID = 8 AND SendType = 1 OR
(SendType = 0 AND AddTime > '2008-3-26 23:45:21'))) AND
(b.KeyTypeID = '36')帮看看 tbl_WBGroupLink 这个表里有160万条记录,tbl_GroupInfo里有8万条,tbl_WBInfo里有110万,SendedHumanWBInfo 这个里现在有2260条 运行这条SQL CPU 很高,我的是2.8 P4双核的 台式的,运行10秒钟都没出来结果,直接超时,我修改成:SELECT TOP 20 a.ID, a.WB, b.GroupID
FROM tbl_WBInfo a INNER JOIN
tbl_WBGroupLink c ON a.WB = c.WBID INNER JOIN
tbl_GroupInfo b ON c.GroupID = b.GroupID
WHERE (b.KeyTypeID = '36') AND (NOT EXISTS
(SELECT 1
FROM Sed_SendedHumanWBInfo f
WHERE a.WB = f.WBIDCode AND TaskID = 8 AND SendType = 1 AND
f.SendType = 1 AND f.AddTime < '2008-3-26 23:45:21'))
这样了还是不行!和上面那条表现一样,帮优化下或有什么建议谢谢!
FROM tbl_WBInfo a INNER JOIN
tbl_WBGroupLink c ON a.WB = c.WBID INNER JOIN
tbl_GroupInfo b ON c.GroupID = b.GroupID
WHERE (a.WB NOT IN
(SELECT WBIDCode
FROM Sed_SendedHumanWBInfo
WHERE TaskID = 8 AND SendType = 1 OR
(SendType = 0 AND AddTime > '2008-3-26 23:45:21'))) AND
(b.KeyTypeID = '36')帮看看 tbl_WBGroupLink 这个表里有160万条记录,tbl_GroupInfo里有8万条,tbl_WBInfo里有110万,SendedHumanWBInfo 这个里现在有2260条 运行这条SQL CPU 很高,我的是2.8 P4双核的 台式的,运行10秒钟都没出来结果,直接超时,我修改成:SELECT TOP 20 a.ID, a.WB, b.GroupID
FROM tbl_WBInfo a INNER JOIN
tbl_WBGroupLink c ON a.WB = c.WBID INNER JOIN
tbl_GroupInfo b ON c.GroupID = b.GroupID
WHERE (b.KeyTypeID = '36') AND (NOT EXISTS
(SELECT 1
FROM Sed_SendedHumanWBInfo f
WHERE a.WB = f.WBIDCode AND TaskID = 8 AND SendType = 1 AND
f.SendType = 1 AND f.AddTime < '2008-3-26 23:45:21'))
这样了还是不行!和上面那条表现一样,帮优化下或有什么建议谢谢!
建立索引吧。
FROM tbl_WBInfo a
INNER JOIN tbl_WBGroupLink c
ON a.WB = c.WBID
INNER JOIN tbl_GroupInfo b
ON c.GroupID = b.GroupID
WHERE (a.WB NOT IN
(SELECT WBIDCode
FROM Sed_SendedHumanWBInfo
WHERE TaskID = 8 AND SendType = 1 OR
(SendType = 0 AND AddTime > '2008-3-26 23:45:21')
)
)
AND (b.KeyTypeID = '36')--Comment:确认KeyTypeID不是int?否则改为b.KeyTypeID = 36
我觉得这个SQL语句不存在什么性能问题,主要的考虑方向可以是你的索引建立的情况,你看看查询计划有没有走索引的路线。要是索引建立了,还是性能地下,可以考虑重建索引,使用以下的SQL语句:alter index all on tableName rebuild with (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
UPDATE STATISTICS tableName建立好索引以后,应该会对性能有提升。
--
效率上not exists要比not in 高些
2.应当指定锁提示(为表指定 with (nolock)),因为有可能你的表被锁住了
3.这点最重要,你至少需要在AddTime\SendType\KeyTypeID上建立索引,至于分别建立,还是建立联合索引,需要看执行计划了
4.确定KeyTypeID字段是否是整型(int)还是字符型(char/varchar),如果是int,千万别用字符型b.KeyTypeID = '36'而用
b.KeyTypeID = 36
5.按照上述方法,一般可以解决你的问题,试试看
--try
SELECT TOP 20 a.ID, a.WB, b.GroupID
FROM (select * from tbl_WBInfo
WHERE (NOT EXISTS
(SELECT 1
FROM Sed_SendedHumanWBInfo f
WHERE a.WB = f.WBIDCode AND TaskID = 8 AND SendType = 1 AND
f.SendType = 1 AND f.AddTime < '2008-3-26 23:45:21')) )
a INNER JOIN
tbl_WBGroupLink c ON a.WB = c.WBID INNER JOIN
(select * from tbl_GroupInfo where b.KeyTypeID = '36')
b ON c.GroupID = b.GroupID 建立索引吧