Benchmarking – SQL with and without index

Everybody likes when processes are running at optimal performance. SQL queries is a typical example where the answer of the two equal queries can be delivered with different speed.
Indexes on a table can have a large impact on the performance of a SQL query. I’ve done some benchmark tests running the same query on a table with and without a index.

The table structure: TestIndex (rowId PK int, productId int not null, countryId int not null). The table contains of 100000 rows.

The SQL query:
set statistics time on;
select productId, count(productId) name, min(countryId)
from testindex T2
where productId > 29500000 and productId < 30000000 AND productId IN
(
select productId from testindex
group by productId having count(productId) < 2
)
AND
productId IN
(
select productId from testindex
group by productId having count(productId) < 3
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
group by productid, name, countryId
order by productId desc
set statistics time off;


The index was created like this:
CREATE NONCLUSTERED INDEX testIndexOne
ON testindex (productId)

Benchmark results

As you can see in the benchmark results above there is a huge different in both CPU- and elapsed time for running the exactly the same query. The only difference was an added index on one of the columns in the table that was queried.