select exec_sqn from a where barcode='11101072089978';a表barcode建有索引 ,查询很快, 得到结果'33562465'
select groupno from b where exec_sqn='33562465';b表exec_sqn建有索引,查询很快
select groupno from a,b where a.exec_sqn=b.exec_sqn and a.barcode='11101072089978'
两个表连接后查询就很慢了,求解释!
select groupno from b where exec_sqn='33562465';b表exec_sqn建有索引,查询很快
select groupno from a,b where a.exec_sqn=b.exec_sqn and a.barcode='11101072089978'
两个表连接后查询就很慢了,求解释!
我目前只能用连续2次查询来解决
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
GO
CREATE TABLE T1(
ID1 UNIQUEIDENTIFIER
,ID2 UNIQUEIDENTIFIER
)
GO
SET NOCOUNT ON
INSERT INTO T1
SELECT NEWID(),NEWID()
GO 10000CREATE TABLE T2(
ID2 UNIQUEIDENTIFIER
,VAL2 VARCHAR(50)
)
INSERT INTO T2
SELECT ID2,NEWID() FROM T1
GO
CREATE INDEX INX_T1_ID1 ON T1(ID1)
CREATE INDEX INX_T2_ID2 ON T2(ID2)
GO
SET NOCOUNT OFF
GO
SET SHOWPLAN_TEXT ON
GO
DECLARE @ID1 UNIQUEIDENTIFIER
SELECT TOP 1 @ID1=ID1 FROM T1 ORDER BY NEWID()
SELECT VAL2
FROM T1,T2
WHERE T1.ID2=T2.ID2
AND T1.ID1=@ID1
GO
SET SHOWPLAN_TEXT OFF
GO
/*
开始执行循环
批处理执行已完成 10000 次。
StmtText
-------------------------------------------------------------------------------
DECLARE @ID1 UNIQUEIDENTIFIER
SELECT TOP 1 @ID1=ID1 FROM T1 ORDER BY NEWID()(1 行受影响)StmtText
---------------------------------------------------------------------
|--Sort(TOP 1, ORDER BY:([Expr1004] ASC))
|--Compute Scalar(DEFINE:([Expr1004]=newid()))
|--Index Scan(OBJECT:([tempdb].[dbo].[T1].[INX_T1_ID1]))(3 行受影响)StmtText
------------------------------------------------------------------SELECT VAL2
FROM T1,T2
WHERE T1.ID2=T2.ID2
AND T1.ID1=@ID1(1 行受影响)StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1004]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T1].[ID2]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
| | |--Index Seek(OBJECT:([tempdb].[dbo].[T1].[INX_T1_ID1]), SEEK:([tempdb].[dbo].[T1].[ID1]=[@ID1]) ORDERED FORWARD)
| | |--RID Lookup(OBJECT:([tempdb].[dbo].[T1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([tempdb].[dbo].[T2].[INX_T2_ID2]), SEEK:([tempdb].[dbo].[T2].[ID2]=[tempdb].[dbo].[T1].[ID2]) ORDERED FORWARD)
|--RID Lookup(OBJECT:([tempdb].[dbo].[T2]), SEEK:([Bmk1004]=[Bmk1004]) LOOKUP ORDERED FORWARD)(7 行受影响)
*/