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

CPU time no index CPU time with index Elapsed time no index Elapsed time with index
94 0 7006 60
15 0 6267 30
31 0 6000 61
47 0 6218 32
235 0 6276 31

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.

Comparison async, sync and delegates

Made a time execution comparison between a usual syncronous, asynchronous and a threaded method call with delegates.

//Declare delegate
public delegate void DelGetList(List<int> lista, string tName);

//Create delegate handler
DelGetList delReporter = ReportList;

//Init method
private void Init()
{
TestThreads();
//TestAsynchronous();
//TestSynchronous();
}

private void TestThreads()
{
//Timestamp (will be read at invoked method)
dtStart = DateTime.Now;

//Call main method
Thread t1 = new Thread(new ThreadStart(GetIntsInNewThread));
Thread t2 = new Thread(new ThreadStart(GetIntsInNewThread));
Thread t3 = new Thread(new ThreadStart(GetIntsInNewThread));
var threads = new List<Thread>() { t1, t2, t3 };
foreach (var t in threads)
t.Start();
}
private async void TestAsynchronous()
{
//Timestamp
dtStart = DateTime.Now;

//Call main method
var tasks = new List<Task<List<int>>>();
tasks.Add(TestAsync());
tasks.Add(TestAsync());
tasks.Add(TestAsync());
var test = await Task.WhenAll(tasks);

//Print
System.Console.WriteLine(“Asynchronous, elapsed MS: ” + (DateTime.Now – dtStart).Milliseconds);
}
private void TestSynchronous()
{
//Timestamp
dtStart = DateTime.Now;

//Call main method
var a1 = GetIntsSync();
var a2 = GetIntsSync();
var a3 = GetIntsSync();

//Print
System.Console.WriteLine(“Synchronous, elapsed MS: ” + (DateTime.Now – dtStart).Milliseconds);
}

private List<int> GetIntsSync()
{
List<int> ret = new List<int>();
for (int i = 0; i < 10000000; i++)
{
if (i % 2 == 0 && i / 3 == 1)
ret.Add(i);
}
return ret;
}
private void GetIntsInNewThread()
{
List<int> ret = new List<int>();
for (int i = 0; i < 10000000; i++)
{
if (i % 2 == 0 && i / 3 == 1)
ret.Add(i);
}

delReporter.Invoke(ret, Thread.CurrentThread.Name);
}

private Task<List<int>> TestAsync()
{
return Task.Run(() =>
{
List<int> ret = new List<int>();
for (int i = 0; i < 10000000; i++)
{
if (i % 2 == 0 && i / 3 == 1)
ret.Add(i);
}
return ret;
});
}

//Execution time (3 tests)
Syncronous, elapsed MS: 225
Syncronous, elapsed MS: 223
Syncronous, elapsed MS: 223
Syncronous, elapsed MS: 231
Syncronous, elapsed MS: 231

Asyncronous, elapsed MS: 135
Asyncronous, elapsed MS: 154
Asyncronous, elapsed MS: 149
Asyncronous, elapsed MS: 134
Asyncronous, elapsed MS: 131

Threads, elapsed MS: 134
Threads, elapsed MS: 167
Threads, elapsed MS: 134
Threads, elapsed MS: 147
Threads, elapsed MS: 164