语句是
select CAST(0 as bit)
checksta,'未发送' sta
from
(Select * from FKTransaction2 Where TransactionNO < LinkNO and LinkNO <> '' and del='0' ) a,
(Select * from FKTransaction2 Where (LinkNO <> '' and del='0') and (LinkNO < TransactionNO)) b,
FKCounterParty c
where a.TransactionNO=b.LinkNO and a.direct like '%回购' and a.CounterParty=c.CounterPartyId and
((a.TradeDate>='20090526' and a.TradeDate<='20090526') or
(b.TradeDate>='20090526' and b.TradeDate<='20090526')) and
a.account in (15,18,27,14,29,30,31,32,33,28,7,8,9,10,11,12,25,26,20,21,22,24) 执行计划不同 我想问一下同一个机器不同窗口或者相同窗口考来的一样SQL 速度(执行计划不一样)不一样
select CAST(0 as bit)
checksta,'未发送' sta
from
(Select * from FKTransaction2 Where TransactionNO < LinkNO and LinkNO <> '' and del='0' ) a,
(Select * from FKTransaction2 Where (LinkNO <> '' and del='0') and (LinkNO < TransactionNO)) b,
FKCounterParty c
where a.TransactionNO=b.LinkNO and a.direct like '%回购' and a.CounterParty=c.CounterPartyId and
((a.TradeDate>='20090526' and a.TradeDate<='20090526') or
(b.TradeDate>='20090526' and b.TradeDate<='20090526')) and
a.account in (15,18,27,14,29,30,31,32,33,28,7,8,9,10,11,12,25,26,20,21,22,24) 执行计划不同 我想问一下同一个机器不同窗口或者相同窗口考来的一样SQL 速度(执行计划不一样)不一样
dbcc freeproccache
dbcc dropcleanbuffers
2、执行计划,可能会根据数据量,软硬件等更方面因素而改变的
我想问一下同一个机器不同窗口或者相同窗口考来的一样SQL
速度(执行计划不一样)不一样不同窗口,不同数据库了?
执行计划应该不会变吧,只不过速度可能会变因为有缓存
|--Hash Match(Inner Join, HASH:([FX_GF].[dbo].[FKTransaction2].[TransactionNO])=([FX_GF].[dbo].[FKTransaction2].[LinkNO]), RESIDUAL:([FX_GF].[dbo].[FKTransaction2].[TransactionNO]=[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND ([FX_GF].[dbo].[FKTransaction2].[TradeDate]>'20090126' AND [FX_GF].[dbo].[FKTransaction2].[TradeDate]<='20091026' OR [FX_GF].[dbo].[FKTransaction2].[TradeDate]>'20090126' AND [FX_GF].[dbo].[FKTransaction2].[TradeDate]<='20091026')))
|--Hash Match(Inner Join, HASH:([FX_GF].[dbo].[FKTransaction2].[CounterParty])=([c].[CounterPartyID]), RESIDUAL:([FX_GF].[dbo].[FKTransaction2].[CounterParty]=[FX_GF].[dbo].[FKCounterParty].[CounterPartyID] as [c].[CounterPartyID]))
| |--Filter(WHERE:([Expr1013]=(24) OR [Expr1013]=(22) OR [Expr1013]=(21) OR [Expr1013]=(20) OR [Expr1013]=(26) OR [Expr1013]=(25) OR [Expr1013]=(12) OR [Expr1013]=(11) OR [Expr1013]=(10) OR [Expr1013]=(9) OR [Expr1013]=(8) OR [Expr1013]=(7) OR [Expr1013]=(28) OR [Expr1013]=(33) OR [Expr1013]=(32) OR [Expr1013]=(31) OR [Expr1013]=(30) OR [Expr1013]=(29) OR [Expr1013]=(14) OR [Expr1013]=(27) OR [Expr1013]=(18) OR [Expr1013]=(15)))
| | |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(int,[FX_GF].[dbo].[FKTransaction2].[Account],0)))
| | |--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[TransactionNO]<[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>'' AND [FX_GF].[dbo].[FKTransaction2].[Direct] like '%回购'))
| |--Table Scan(OBJECT:([FX_GF].[dbo].[FKCounterParty] AS [c]))
|--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<[FX_GF].[dbo].[FKTransaction2].[TransactionNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>''))
这个执行计划快
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([FX_GF].[dbo].[FKTransaction2].[CounterParty])=([c].[CounterPartyID]), RESIDUAL:([FX_GF].[dbo].[FKTransaction2].[CounterParty]=[FX_GF].[dbo].[FKCounterParty].[CounterPartyID] as [c].[CounterPartyID]))
|--Nested Loops(Inner Join, WHERE:([FX_GF].[dbo].[FKTransaction2].[TransactionNO]=[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND ([FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526' OR [FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526')))
| |--Sort(ORDER BY:([FX_GF].[dbo].[FKTransaction2].[CounterParty] ASC))
| | |--Filter(WHERE:([Expr1013]=(24) OR [Expr1013]=(22) OR [Expr1013]=(21) OR [Expr1013]=(20) OR [Expr1013]=(26) OR [Expr1013]=(25) OR [Expr1013]=(12) OR [Expr1013]=(11) OR [Expr1013]=(10) OR [Expr1013]=(9) OR [Expr1013]=(8) OR [Expr1013]=(7) OR [Expr1013]=(28) OR [Expr1013]=(33) OR [Expr1013]=(32) OR [Expr1013]=(31) OR [Expr1013]=(30) OR [Expr1013]=(29) OR [Expr1013]=(14) OR [Expr1013]=(27) OR [Expr1013]=(18) OR [Expr1013]=(15)))
| | |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(int,[FX_GF].[dbo].[FKTransaction2].[Account],0)))
| | |--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[TransactionNO]<[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>'' AND [FX_GF].[dbo].[FKTransaction2].[Direct] like '%回购'))
| |--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<[FX_GF].[dbo].[FKTransaction2].[TransactionNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>''))
|--Sort(ORDER BY:([c].[CounterPartyID] ASC))
|--Table Scan(OBJECT:([FX_GF].[dbo].[FKCounterParty] AS [c])) 这个执行计划遥遥无期的
查询优化器会每次尽量找最优的执行计划,但并不一定是最优的,你可以在关键字join前面指定连接,强制的让它用那一种算法连接
|--Nested Loops(Inner Join, WHERE:([FX_GF].[dbo].[FKTransaction2].[TransactionNO]=[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND ([FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526' OR [FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526'))) 22楼:
|--Hash Match(Inner Join, HASH:([FX_GF].[dbo].[FKTransaction2].[TransactionNO])=([FX_GF].[dbo].[FKTransaction2].[LinkNO]), RESIDUAL:([FX_GF].[dbo].[FKTransaction2].[TransactionNO]=[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND ([FX_GF].[dbo].[FKTransaction2].[TradeDate]>'20090126' AND [FX_GF].[dbo].[FKTransaction2].[TradeDate] <='20091026' OR [FX_GF].[dbo].[FKTransaction2].[TradeDate]>'20090126' AND [FX_GF].[dbo].[FKTransaction2].[TradeDate] <='20091026')))
我把 时间都改过成'20090526' 试过! 我才发帖子的! 一个很快用HASH 查出没数据 一个一直没结果! 我现在怀疑是MSSQL BUG
|--Compute Scalar(DEFINE:([Expr1011]=(0), [Expr1012]='未发送'))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([FX_GF].[dbo].[FKTransaction2].[CounterParty])=([c].[CounterPartyID]), RESIDUAL:([FX_GF].[dbo].[FKTransaction2].[CounterParty]=[FX_GF].[dbo].[FKCounterParty].[CounterPartyID] as [c].[CounterPartyID]))
|--Nested Loops(Inner Join, WHERE:([FX_GF].[dbo].[FKTransaction2].[TransactionNO]=[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND ([FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526' OR [FX_GF].[dbo].[FKTransaction2].[TradeDate]='20090526')))
| |--Sort(ORDER BY:([FX_GF].[dbo].[FKTransaction2].[CounterParty] ASC))
| | |--Filter(WHERE:([Expr1013]=(24) OR [Expr1013]=(22) OR [Expr1013]=(21) OR [Expr1013]=(20) OR [Expr1013]=(26) OR [Expr1013]=(25) OR [Expr1013]=(12) OR [Expr1013]=(11) OR [Expr1013]=(10) OR [Expr1013]=(9) OR [Expr1013]=(8) OR [Expr1013]=(7) OR [Expr1013]=(28) OR [Expr1013]=(33) OR [Expr1013]=(32) OR [Expr1013]=(31) OR [Expr1013]=(30) OR [Expr1013]=(29) OR [Expr1013]=(14) OR [Expr1013]=(27) OR [Expr1013]=(18) OR [Expr1013]=(15)))
| | |--Compute Scalar(DEFINE:([Expr1013]=CONVERT_IMPLICIT(int,[FX_GF].[dbo].[FKTransaction2].[Account],0)))
| | |--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[TransactionNO]<[FX_GF].[dbo].[FKTransaction2].[LinkNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>'' AND [FX_GF].[dbo].[FKTransaction2].[Direct] like '%回购'))
| |--Table Scan(OBJECT:([FX_GF].[dbo].[FKTransaction2]), WHERE:([FX_GF].[dbo].[FKTransaction2].[Del]=(0) AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<[FX_GF].[dbo].[FKTransaction2].[TransactionNO] AND [FX_GF].[dbo].[FKTransaction2].[LinkNO]<>''))
|--Sort(ORDER BY:([c].[CounterPartyID] ASC))
|--Table Scan(OBJECT:([FX_GF].[dbo].[FKCounterParty] AS [c]))这个是快的在20090526 的执行计划
|--Constant Scan
审视自己,检讨问题。
计划任务,应该不会吧,
=====================
难道你的内存,不稳定!