DECLARE @temp0 VARCHAR(20), @temp1 VARCHAR(10), @temp2 VARCHAR(10), @temp3 VARCHAR(10), @temp4 VARCHAR(10), @temp5 VARCHAR(10) select @temp2='A' select @temp3='B' select @temp4='C' select @temp5='B'DECLARE @SDATEFROM datetime , @SDATETO datetime SELECT @SDATEFROM=GETDATE () DECLARE CUR_Xtemp CURSOR FOR SELECT FirstName FROM [AdventureWorks].[Person].[Contact] OPEN CUR_Xtemp FETCH NEXT FROM CUR_Xtemp INTO @temp0 WHILE(@@FETCH_STATUS = 0) BEGIN SELECT @temp1=Name FROM [AdventureWorks].[Production].[Product] where (@temp2='A' or exists(select * from [AdventureWorks].[Production].[Location] where (LocationID='2' or @temp3='B' ) )) FETCH NEXT FROM CUR_Xtemp INTO @temp0 END CLOSE CUR_Xtemp DEALLOCATE CUR_Xtemp SELECT @SDATETO=GETDATE () SELECT DATEDIFF1=DATEDIFF("ss",@SDATEFROM,@SDATETO)
SELECT @SDATEFROM=GETDATE () DECLARE CUR_Xtemp CURSOR FOR SELECT FirstName FROM [AdventureWorks].[Person].[Contact] OPEN CUR_Xtemp FETCH NEXT FROM CUR_Xtemp INTO @temp0 WHILE(@@FETCH_STATUS = 0) BEGIN SELECT @temp1=Name FROM [AdventureWorks].[Production].[Product] where (@temp2='A' or exists(select * from [AdventureWorks].[Production].[Location] where (LocationID='2' or @temp3='B' ) )) and (@temp4='C' or @temp5='D' ) FETCH NEXT FROM CUR_Xtemp INTO @temp0 END CLOSE CUR_Xtemp DEALLOCATE CUR_Xtemp SELECT @SDATETO=GETDATE () SELECT DATEDIFF2=DATEDIFF("ss",@SDATEFROM,@SDATETO) 这是我在sql2005自带的数据库写的测试相差20秒 麻烦各位老大费心了
sql2005自带的数据库 adventureworks
第2个查询比第1个查询多一个"and (@temp4='C' or @temp5='D' ) "的开销啊
楼主提到的是or嘛, 那你把 "and (@temp4='C' or @temp5='D' ) " 改成"or (@temp4='C' or @temp5='D' ) ""
@temp1 VARCHAR(10),
@temp2 VARCHAR(10),
@temp3 VARCHAR(10),
@temp4 VARCHAR(10),
@temp5 VARCHAR(10) select @temp2='A'
select @temp3='B'
select @temp4='C'
select @temp5='B'DECLARE
@SDATEFROM datetime ,
@SDATETO datetime SELECT @SDATEFROM=GETDATE () DECLARE CUR_Xtemp CURSOR FOR
SELECT FirstName
FROM [AdventureWorks].[Person].[Contact]
OPEN CUR_Xtemp
FETCH NEXT FROM CUR_Xtemp INTO @temp0 WHILE(@@FETCH_STATUS = 0) BEGIN SELECT @temp1=Name
FROM [AdventureWorks].[Production].[Product]
where (@temp2='A' or exists(select * from [AdventureWorks].[Production].[Location] where (LocationID='2' or @temp3='B' ) )) FETCH NEXT FROM CUR_Xtemp INTO @temp0
END CLOSE CUR_Xtemp
DEALLOCATE CUR_Xtemp SELECT @SDATETO=GETDATE () SELECT DATEDIFF1=DATEDIFF("ss",@SDATEFROM,@SDATETO)
SELECT @SDATEFROM=GETDATE () DECLARE CUR_Xtemp CURSOR FOR
SELECT FirstName
FROM [AdventureWorks].[Person].[Contact]
OPEN CUR_Xtemp
FETCH NEXT FROM CUR_Xtemp INTO @temp0 WHILE(@@FETCH_STATUS = 0) BEGIN SELECT @temp1=Name
FROM [AdventureWorks].[Production].[Product]
where (@temp2='A' or exists(select * from [AdventureWorks].[Production].[Location] where (LocationID='2' or @temp3='B' ) ))
and (@temp4='C' or @temp5='D' ) FETCH NEXT FROM CUR_Xtemp INTO @temp0
END CLOSE CUR_Xtemp
DEALLOCATE CUR_Xtemp SELECT @SDATETO=GETDATE () SELECT DATEDIFF2=DATEDIFF("ss",@SDATEFROM,@SDATETO) 这是我在sql2005自带的数据库写的测试相差20秒 麻烦各位老大费心了
后来我追踪才发现是 2005 如果有2个 or的情况下 速度会慢好多,而在2000
下去掉一个or和不去掉几乎时间不变,所以奇怪就奇怪在这里