Reputation: 3373
I have an empty SQL Server CE 4.0 database for a User entity (guid Id, string Name and int Age): CREATE TABLE [Users] ( [Id] uniqueidentifier NOT NULL , [Name] nvarchar(255) NOT NULL , [Age] int NOT NULL ); ALTER TABLE [Users] ADD CONSTRAINT [PK_Users] PRIMARY KEY ([Id]);
I have .NET Framework 4.7.2 console app where I query this database with ADO .NET (Microsoft.SqlServer.Compact 4.0.8876.1 nuget package). Simple SELECT * FROM Users
executes in <50ms (as I expect). But if I populate a list of 3000 GUIDs and use them in a query SELECT * FROM Users WHERE Id IN ('Id1', 'Id2', ...)
- query executes ~4000ms. Again, my database is empty (there is nothing to search there) and it has index on Id (as it is PK).
How to speed up SQL Server CE? Am I missing some extra configuration?
My code sample:
using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.IO;
using System.Linq;
namespace SqlCeSelectFromEmptyDbTest
{
internal class Program
{
private const string ConnectionString = "Data Source = test.sdf";
static void Main(string[] args)
{
if (!File.Exists("test.sdf"))
{
using (var engine = new SqlCeEngine(ConnectionString))
{
engine.CreateDatabase();
}
using (var conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "CREATE TABLE Users (Id uniqueidentifier NOT NULL, Name nvarchar(255) NOT NULL, Age int NOT NULL)";
command.ExecuteNonQuery();
command.CommandText = "ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY (Id)";
command.ExecuteNonQuery();
}
}
}
//executes about 100ms
var userCount = GetUsersCount();
//executes less than 50ms
var users = GetAllUsers();
//executes about 4000ms
users = GetUsersFilteredById();
}
private static int GetUsersCount()
{
int ret = 0;
using (var conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM Users";
ret = (int)command.ExecuteScalar();
}
}
return ret;
}
private static List<(Guid Id, string Name, int Age)> GetAllUsers()
{
var users = new List<(Guid Id, string Name, int Age)>();
using (var conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = "SELECT * FROM Users";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add((reader.GetGuid(0), reader.GetString(1), reader.GetInt32(2)));
}
}
}
}
return users;
}
private static List<(Guid Id, string Name, int Age)> GetUsersFilteredById()
{
var filterIds = Enumerable.Range(0, 3000).Select(_ => Guid.NewGuid().ToString()).ToList();
var whereClause = string.Join(", ", filterIds.Select(id => $"'{id.ToString()}'"));
var users = new List<(Guid Id, string Name, int Age)>();
using (var conn = new SqlCeConnection(ConnectionString))
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = $"SELECT * FROM Users WHERE Id IN ({whereClause})";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add((reader.GetGuid(0), reader.GetString(1), reader.GetInt32(2)));
}
}
}
}
return users;
}
}
}
Upvotes: 0
Views: 80