Bhuvnesh (12/20/2012)
Sean Lange (12/20/2012)
pr.price <> 9999.99
Try making this a SARGable predicate by removing the <> and replacing with > OR <
pr.price < 9999.99 OR pr.price > 9999.99
new learning for me. i am straight away going to test it 🙂
Sean , i did a test btu result are not impressive or i am overlooking somthing (OR amount of data is small ? )
use AdventureWorks2008R2
GO
set NOCOUNT ON
if exists ( select 1 from sys.objects where name = 'tbl_Distance' )
drop table tbl_Distance
Go
create table tbl_Distance
(
id int not null,
distance decimal (15,3),
uniqueid nvarchar(40)
)
GO
declare @C int
set @C = 1
while (@c < = 3000)
begin
insert into tbl_Distance (id, distance,uniqueid)
select @C , @C* 123.43, newid()
insert into tbl_Distance (id, distance,uniqueid)
select @C*2 , @C* 13.3, newid()
insert into tbl_Distance (id, distance,uniqueid)
select @C*3 , @C* 1.5, newid()
end
GO
create index idx on tbl_Distance (distance)
Query
select * from tbl_Distance
where distance <> 370.290
select * from tbl_Distance
where distance < 370.290 or distance > 370.290
results :
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 326 ms.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 364 ms.
and
Table 'tbl_Distance'. Scan count 1, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Distance'. Scan count 1, logical reads 190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)