Tester for the performance of SELECT WHERE IN

gistfile1.cs

using NLog;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace ConsoleApplication8
{
  class Program
	{
		static void Main(string[] args)
		{
			Logger logger = LogManager.GetLogger("tester");
			var exists = false;

			using(var ctx=new MyContext())
			{
				exists = ctx.Database.Exists();
			}

			if(!exists){
			Console.WriteLine("Generating data's");
			for (int i = 0; i < 1000; i++)
			{
				using (var ctx = new MyContext())
				{
					for (int j = 0; j < 1000; j++)
					{
						ctx.Entities.Add(new Entity());
					}
					ctx.SaveChanges();
					Console.WriteLine("Inserted rows, {0},000/1,000,000", i + 1);
				}
			}
			Console.WriteLine("Done generating data's");
			}

			Random rand = new Random();
			for (int i = 0; i <= 10000; i += 1000)
			{
				int[] selecting = Enumerable.Range(0, i!= 0 ? i : 1).Select(r => rand.Next((1000 * 1000) - 1) + 1).ToArray();

				var joined = String.Join(",", selecting);

				//using (var ctx = new MyContext())
				//{
				//	Console.WriteLine("Querying for {0} random keys (where in)", selecting.Count());
				//	var sw = Stopwatch.StartNew();

				//	var items = ctx.Entities.Where(e => selecting.Contains(e.Id)).AsNoTracking().ToArray();
				//	Console.WriteLine("Querying random keys took {0}ms", sw.ElapsedMilliseconds);
				//}

				using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=ConsoleApplication8.MyContext;Integrated Security=SSPI;"))
				{
					con.Open();
					List<int> results = new List<int>();
					logger.Info("Querying for {0} random keys (where in)", selecting.Count());
					var sw = Stopwatch.StartNew();
					using (SqlCommand command = new SqlCommand(String.Format("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Entities] AS [Extent1] WHERE [Extent1].[Id] in ({0})", joined), con))
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							results.Add(reader.GetInt32(0));
						}
					}
					logger.Info("Querying random keys took {0}ms", sw.ElapsedMilliseconds);
				}
			}

			using (SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=ConsoleApplication8.MyContext;Integrated Security=SSPI;"))
			{
				con.Open();
				List<int> results = new List<int>();
				logger.Info("Querying for keys less than 10k (where)");
				var sw = Stopwatch.StartNew();
				using (SqlCommand command = new SqlCommand("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Entities] AS [Extent1] WHERE [Extent1].[Id] < 10000", con))
				using (SqlDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						results.Add(reader.GetInt32(0));
					}
				}
				logger.Info("Querying for keys less than 10k took {0}ms", sw.ElapsedMilliseconds);
			}
			Console.ReadLine();
		}
	}


	public class MyContext : DbContext
	{
		public DbSet<Entity> Entities { get; set; }
	}

	public class Entity
	{
		public int Id { get; set; }
	}
}