bairog
bairog

Reputation: 3373

SQL Server Compact 4.0 SELECT query with WHERE is extremely slow on empty database

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

Answers (0)

Related Questions