SQL Server, benchmark's time! "Singleton_lookup Vs. Range_scan" and "Index Vs. Unique Index"

Hi Guys,

I know I know! It's Friday night, are you waiting for dinner, sipping a good glass of sparkling white wine and you don't know what to do?
Don't worry, I'll take care of it. 

Here is a post that will take you just 5 minutes!

 

Some time ago we talk about Singleton_lookup and Range_scan.

You can read here: 

• SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan  

and  

• SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan. Part 2 ...some other infos.
  

 

We were asked, which of the two operations is the faster?

The interesting fact is that this question allows us to answer the question as well:

We have two equal indices except that one is unique

Which of the two allows us to perform better?

 

It's benchmark time men!  ...It's benchmark time! ha ha ha!

Enjoy!

"Singleton_lookup Vs. Range_scan" & "Index Vs. Unique Index"

 

To compare the speed of the two different access modes we use a composite index.
This index will access the data with a Range Scan.

Then we modify the same index to make it also unique, in this way the access to the data will take place in Singleton lookup

We test might be also a Index Vs. Unique Index test.

We will use the same OrdTes of the previous post:

       
CREATE TABLE ORDTES
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 DOC_YEAR int,
 DOC_NUMBER int,
 CUSTOMER_NAME varchar(80)
);
 

We fill the Ordtes with 1.000.000 Rows:

WITH CTE AS (  
   SELECT 1 AS Numb  
   UNION ALL
   SELECT a.Numb + 1  
   FROM   CTE a
   WHERE  a.Numb < 10
),
 CTE2 AS (  
   SELECT 1 AS Numb  
   UNION ALL
   SELECT a.Numb + 1  
   FROM   CTE2 a
   WHERE  a.Numb < 100000
)
INSERT INTO ORDTES (DOC_YEAR, DOC_NUMBER, CUSTOMER_NAME)
SELECT 
  1999+CTE.Numb,
  CTE2.Numb,
  'customer'
FROM CTE, CTE2
OPTION (MAXRECURSION 0)

We will execute the following query that return only one record.

       
SELECT DOC_YEAR, DOC_NUMBER FROM ORDTES WHERE DOC_YEAR = 2000 AND DOC_NUMBER = 3
 

With the following index:

       
CREATE INDEX  IDX_ORDTES_DOC_YEAR_DOC_NUMBER ON ORDTES (DOC_YEAR, DOC_NUMBER)
 

Then the the following unique index:

       
CREATE UNIQUE INDEX  IDX_ORDTES_DOC_YEAR_DOC_NUMBER ON ORDTES (DOC_YEAR, DOC_NUMBER)
 

Results

With the unique index we have run the SELECT 100 times, the running time was 14 seconds.

Then we repeated the test with 200 and 400 iterations. Their respective execution times were 38 and 131 seconds.

Before each test we have cleared the cache with the command DBCC FREEPROCCACHE.

Then, we have drop the unique index and add the not unique index and repeat the same tests

Here are the results:

 

We can calculate the difference in% by doing:

 ((+13.0/100 + 41.0/200 + 142.0/400 ) - (+14.0/100 + 38.0/200 + 131.0/400 )) / ( +13.0/100 + 41.0/200 + 142.0/400 ) * 100  = 4.7 %

so,

• The Singleton look is 5% faster than the Range Scan
• Using an unique index is 5% faster than using a unique index.
  

Conclusion

Well, if you can, use an unique index!

That'all for today, happy weekend! and Stay Tuned!

Luca 

 

If you are genuinely and truly interested in one of the products that appear in advertising then click the adv from my blog to support my blog! 

 

 

 

Previous post: SQL Server, A little about composite indices and a little about the relative statistics ..