Compare performance difference of T-SQL Between and '<' '>' operator?
You can check this easily enough by checking the query plans in both situations. There is no difference of which I am aware. There is a logical difference though between BETWEEN and "<" and ">"... BETWEEN is inclusive. It's equivalent to "<=" and "=>".
I was also interested in whether there is a performance difference when I used (>= and <=) compared to using the between keyword. (I come from a dotnet background and like the >= style operators).
Here is the script I used:
DECLARE
@Startdatetime datetime ,
@Diff int = 0 ,
@Addrowcount int = 1000;
SET NOCOUNT ON;
--Create a tempory table to perform our tests on
CREATE TABLE dbo.perftest( id smallint NOT NULL
IDENTITY(1 , 1)
PRIMARY KEY ,
mytext nvarchar( 50 )NOT NULL );
--Now add some sample rows
SET @Addrowcount = 1000;
WHILE(@Addrowcount > 0)
BEGIN
INSERT INTO dbo.perftest( mytext )
VALUES( 'thetext' );
SET @Addrowcount = @Addrowcount - 1;
END;
SELECT @Startdatetime = GETDATE();
-- do method 1 here
SELECT mytext
FROM dbo.perftest
WHERE(id >= 100)
AND (id <= 900);
--end method1
SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());
PRINT ':Method 1: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';
--reset start time
SELECT @Startdatetime = GETDATE();
--do method2 here
SELECT mytext
FROM dbo.perftest
WHERE id BETWEEN 100
AND 900;
--end method2
SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());
PRINT ':Method 2: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';
The results were:
Method 1: 140 ms
Method 2: 70 ms
So it appears that performance is improved by using between.
Love when folks give code to do your own testing, you need to do a larger subset / repeated test to account for indexes being loaded into memory, etc... before jumping to conclusions though. Here is the same code with a larger table and 10 iterations
DECLARE
@Startdatetime datetime ,
@Diff int = 0 ,
@Addrowcount int = 1000 ,
@ptr int = 1;
SET NOCOUNT ON;
--Create a tempory table to perform our tests on
DROP TABLE dbo.perftest
CREATE TABLE dbo.perftest( id int NOT NULL
IDENTITY(1 , 1)
PRIMARY KEY ,
mytext nvarchar( 50 )NOT NULL );
--Now add some sample rows
SET @Addrowcount = 20000;
WHILE(@Addrowcount > 0)
BEGIN
INSERT INTO dbo.perftest( mytext )
VALUES( 'thetext' );
SET @Addrowcount = @Addrowcount - 1;
END;
WHILE @ptr < 10 -- do this a few times to account for indexes being loaded into memory
BEGIN
SELECT @Startdatetime = GETDATE();
-- do method 1 here
SELECT mytext
FROM dbo.perftest
WHERE(id >= (100 + (@ptr * 1000)))
AND (id <= (500 + (@ptr * 1000)));
--end method1
SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());
PRINT ':Method 1: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';
--reset start time
SELECT @Startdatetime = GETDATE();
--do method2 here
SELECT mytext
FROM dbo.perftest
WHERE id BETWEEN (300 + (@ptr * 1000))
AND (800 + (@ptr * 1000));
--end method2
SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());
PRINT ':Method 2: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';
SET @ptr = @ptr + 1
END
Gives you a very different set of results:
--Method 1 -- 10 ms
--Method 2 -- 33 ms
--Method 1 -- 40 ms
--Method 2 -- 26 ms
--Method 1 -- 23 ms
--Method 2 -- 23 ms
--Method 1 -- 13 ms
--Method 2 -- 16 ms
--Method 1 -- 13 ms
--Method 2 -- 20 ms
--Method 1 -- 6 ms
--Method 2 -- 16 ms
--Method 1 -- 26 ms
--Method 2 -- 16 ms
--Method 1 -- 13 ms
--Method 2 -- 13 ms
--Method 1 -- 16 ms
--Method 2 -- 13 ms
I would say from this (still pretty unscientific) test, not much difference either way.
The query engine converts between into >=
and <=
(take a look at the query plan) so in practise they're identical and in theory >= <=
is faster because the engine won't have to translate. Good luck noticing a difference though.
I use between anyway, I find it reads easier
Very complex queries/nested views with numerous between comparisons might benefit from changing into >= <=
as this might potentially prevent optimisation timeouts by reducing the time spent on refactoring the query (just a theory, untested by me & I've never noticed it)