SET SHOWPLAN_TEXT on
goselect top 10 * from TxJobHd a
left join TxJobDet b on a.JobNo=b.JobNo
where a.JobNo='SP07000008'
go
--结果
|--Top(TOP EXPRESSION:((10)))
|--Nested Loops(Left Outer Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd].[PkTxJobHd] AS [a]), SEEK:([a].[JobNo]='SP07000008') ORDERED FORWARD)
| |--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd] AS [a]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))
|--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet].[PkTxJobDet] AS [b]), SEEK:([b].[JobNo]='SP07000008') ORDERED FORWARD)
|--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet] AS [b]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)select top 10 * from
(select * from TxJobHd where JobNo='SP07000008') a
left join TxJobDet b on a.JobNo=b.JobNo
go
--结果
|--Top(TOP EXPRESSION:((10)))
|--Nested Loops(Left Outer Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd].[PkTxJobHd]), SEEK:([DtradeSimpleGarment01].[dbo].[TxJobHd].[JobNo]='SP07000008') ORDERED FORWARD)
| |--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
|--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet].[PkTxJobDet] AS [b]), SEEK:([b].[JobNo]='SP07000008') ORDERED FORWARD)
|--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet] AS [b]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)以上两个语句的执行顺序分别是什么?执行计划怎么看?
我怎么看上面两个语句都是一样的~
goselect top 10 * from TxJobHd a
left join TxJobDet b on a.JobNo=b.JobNo
where a.JobNo='SP07000008'
go
--结果
|--Top(TOP EXPRESSION:((10)))
|--Nested Loops(Left Outer Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd].[PkTxJobHd] AS [a]), SEEK:([a].[JobNo]='SP07000008') ORDERED FORWARD)
| |--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd] AS [a]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))
|--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet].[PkTxJobDet] AS [b]), SEEK:([b].[JobNo]='SP07000008') ORDERED FORWARD)
|--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet] AS [b]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)select top 10 * from
(select * from TxJobHd where JobNo='SP07000008') a
left join TxJobDet b on a.JobNo=b.JobNo
go
--结果
|--Top(TOP EXPRESSION:((10)))
|--Nested Loops(Left Outer Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd].[PkTxJobHd]), SEEK:([DtradeSimpleGarment01].[dbo].[TxJobHd].[JobNo]='SP07000008') ORDERED FORWARD)
| |--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
|--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet].[PkTxJobDet] AS [b]), SEEK:([b].[JobNo]='SP07000008') ORDERED FORWARD)
|--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobDet] AS [b]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)以上两个语句的执行顺序分别是什么?执行计划怎么看?
我怎么看上面两个语句都是一样的~
如果说项看它是怎么执行的,就是以最右边最上边的标准来判断执行顺序。如你这个例子中,第一步是执行 |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd].[PkTxJobHd]), SEEK:([DtradeSimpleGarment01].[dbo].[TxJobHd].[JobNo]='SP07000008') ORDERED FORWARD)然后执行
|--RID Lookup(OBJECT:([DtradeSimpleGarment01].[dbo].[TxJobHd]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
二者再做个NL连接。。类似于这样。。如果想看各个执行的含义的话,推荐你看本书:http://www.sqlservercentral.com/articles/books/65831/
看预估的执行计划可以用:
set showplan_all on
或者
set showplan_text on看实际的执行计划用
set statistics profile on也可以在SSMS上点击按钮查看图形的执行计划。