select top 1 * from tbl1:快 select * from tbl1 :较快 select top 1 * from tbl1 ORDER BY :较慢 select * from tbl1 ORDER BY :最慢
select top 1 * from tbl1 快
再问一下各位高手,如下SQL语句的执行效率怎样?有更好的写法吗? declare @intDateType int declare @dtmfrom datetime declare @dtmto datetime if @datefrom is null and @datefrom is null then set @intDateType = 0 else set @intDateType = 1select * form tbl1 where tbl1.date >= case when @intDateType = 0 then tbl1.date when @intDateType = 1 then @dtmDateFrom end and tbl1.date <= case when @intDateType = 0 then tbl1.date when @intDateType = 1 then @dtmDateto end 关键是当@intDateType = 0,sql 相当于 select * form tbl1 where tbl1.date >= tbl1.date and tbl1.date <= tbl1.date 不知这样对执行的效率影响大不大?希望有经验的大侠来指点一下:)
有ORDER BY 当然慢多了,因为要先排序。
快
select * from tbl1 :较快
select top 1 * from tbl1 ORDER BY :较慢
select * from tbl1 ORDER BY :最慢
快
declare @intDateType int
declare @dtmfrom datetime
declare @dtmto datetime if @datefrom is null and @datefrom is null then
set @intDateType = 0
else
set @intDateType = 1select * form tbl1
where
tbl1.date >=
case
when @intDateType = 0 then tbl1.date
when @intDateType = 1 then @dtmDateFrom
end
and tbl1.date <=
case
when @intDateType = 0 then tbl1.date
when @intDateType = 1 then @dtmDateto
end
关键是当@intDateType = 0,sql 相当于
select * form tbl1
where
tbl1.date >= tbl1.date and tbl1.date <= tbl1.date
不知这样对执行的效率影响大不大?希望有经验的大侠来指点一下:)