下面的语句中,T_ChatLog和T_SalePact的记录都很多,有上百万条,请问该如何优化这个SQL语句?
本意就是求出T_ChatLog表中to_id不在T_SalePact表的buyer_nick中的记录
语句是正确的,就是速度太慢,望大侠指点一二。SELECT from_id, to_id, MsgDate, ShopId, COUNT(*) AS MsgTotal
FROM dbo.T_ChatLog AS a
WHERE MsgType = 1 AND NOT EXISTS(SELECT 1 AS Expr1
FROM dbo.T_SalePact
WHERE (ShopId = a.ShopId) AND (PactSmallDate = a.MsgDate) AND (buyer_nick = a.to_id)))
GROUP BY ShopId, MsgDate, from_id, to_id
本意就是求出T_ChatLog表中to_id不在T_SalePact表的buyer_nick中的记录
语句是正确的,就是速度太慢,望大侠指点一二。SELECT from_id, to_id, MsgDate, ShopId, COUNT(*) AS MsgTotal
FROM dbo.T_ChatLog AS a
WHERE MsgType = 1 AND NOT EXISTS(SELECT 1 AS Expr1
FROM dbo.T_SalePact
WHERE (ShopId = a.ShopId) AND (PactSmallDate = a.MsgDate) AND (buyer_nick = a.to_id)))
GROUP BY ShopId, MsgDate, from_id, to_id
加个索引
也加索引
两个表对应都加!
T_ChatLog(ShopID,MsgDate,to_id,from_id)
T_SalePact(ShopID,PactSmallDate,buyer_nick)
当ShopId = a.ShopId) AND (PactSmallDate = a.MsgDate) AND (buyer_nick = a.to_id)时,
此字段可更新为1,即ZT=1查询语句可简化为:
SELECT from_id, to_id, MsgDate, ShopId, COUNT(*) AS MsgTotal
FROM dbo.T_ChatLog AS a
WHERE MsgType = 1 AND ZT<>1
GROUP BY ShopId, MsgDate, from_id, to_id
--兩個表都建
--T_SalePact
Create index IX_....... on TableName(Col1,Col2,Col3)
--T_ChatLog
....................................................INCLUDE(from_id)
or
Create index IX_....... on TableName(Col1,Col2,Col3,from_id)
SELECT from_id, to_id, MsgDate, ShopId, COUNT(*) AS MsgTotal
FROM dbo.T_ChatLog AS a
WHERE MsgType = 1 AND NOT EXISTS(SELECT top 1 'a' AS Expr1
FROM dbo.T_SalePact
WHERE (ShopId = a.ShopId) AND (PactSmallDate = a.MsgDate) AND (buyer_nick = a.to_id)))
GROUP BY ShopId, MsgDate, from_id, to_id