SQL Server Compact 4.0空库中带IN条件的SELECT查询极慢如何优化？

问题背景 #

我有一个存储User实体（Guid类型Id、string类型Name、int类型Age）的空SQL Server Compact 4.0数据库，表结构如下：

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]);

使用.NET Framework 4.7.2控制台应用，通过ADO.NET（Microsoft.SqlServer.Compact 4.0.8876.1 NuGet包）查询。简单SELECT * FROM Users查询耗时<50ms，但生成3000个GUID用SELECT * FROM Users WHERE Id IN ('Id1', 'Id2', ...)查询时，耗时约4000ms。数据库是空的，Id作为主键已有索引。

代码示例 #

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;
        }
    }
}

优化方案 #

1. 改用参数化查询，避免字符串拼接开销 #

直接拼接GUID字符串到IN子句会导致SQL Server Compact花费大量时间解析超长SQL语句，同时存在SQL注入风险。改用参数化查询，让数据库直接处理Guid类型参数，大幅降低解析成本。

修改后的GetUsersFilteredById方法：

private static List<(Guid Id, string Name, int Age)> GetUsersFilteredById()
{
    var filterIds = Enumerable.Range(0, 3000).Select(_ => Guid.NewGuid()).ToList();
    var users = new List<(Guid Id, string Name, int Age)>();
    
    using (var conn = new SqlCeConnection(ConnectionString))
    {
        conn.Open();
        using (var command = conn.CreateCommand())
        {
            // 生成参数占位符
            var paramPlaceholders = string.Join(", ", filterIds.Select((_, index) => $"@Id{index}"));
            command.CommandText = $"SELECT * FROM Users WHERE Id IN ({paramPlaceholders})";
            
            // 添加参数
            for (int i = 0; i < filterIds.Count; i++)
            {
                var param = command.CreateParameter();
                param.ParameterName = $"@Id{i}";
                param.SqlDbType = SqlDbType.UniqueIdentifier;
                param.Value = filterIds[i];
                command.Parameters.Add(param);
            }
            
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    users.Add((reader.GetGuid(0), reader.GetString(1), reader.GetInt32(2)));
                }
            }
        }
    }
    return users;
}

2. 使用临时表+JOIN查询处理超大量参数 #

SQL Server Compact对IN子句的参数数量有隐性限制，当参数超过一定数量后性能会急剧下降。创建临时表存储要查询的GUID，再通过JOIN关联查询，能有效提升性能。

示例代码：

private static List<(Guid Id, string Name, int Age)> GetUsersFilteredByIdWithTempTable()
{
    var filterIds = Enumerable.Range(0, 3000).Select(_ => Guid.NewGuid()).ToList();
    var users = new List<(Guid Id, string Name, int Age)>();
    
    using (var conn = new SqlCeConnection(ConnectionString))
    {
        conn.Open();
        using (var transaction = conn.BeginTransaction())
        {
            try
            {
                // 创建临时表
                using (var createTempCmd = conn.CreateCommand())
                {
                    createTempCmd.Transaction = transaction;
                    createTempCmd.CommandText = "CREATE TABLE #TempIds (Id uniqueidentifier NOT NULL PRIMARY KEY)";
                    createTempCmd.ExecuteNonQuery();
                }
                
                // 批量插入GUID到临时表
                using (var bulkCopy = new SqlCeBulkCopy(conn, SqlCeBulkCopyOptions.Default, transaction))
                {
                    bulkCopy.DestinationTableName = "#TempIds";
                    bulkCopy.ColumnMappings.Add("Id", "Id");
                    
                    // 构造DataTable
                    var tempTable = new DataTable();
                    tempTable.Columns.Add("Id", typeof(Guid));
                    foreach (var id in filterIds)
                    {
                        tempTable.Rows.Add(id);
                    }
                    
                    bulkCopy.WriteToServer(tempTable);
                }
                
                // 通过JOIN查询
                using (var queryCmd = conn.CreateCommand())
                {
                    queryCmd.Transaction = transaction;
                    queryCmd.CommandText = @"
                        SELECT u.* 
                        FROM Users u
                        INNER JOIN #TempIds t ON u.Id = t.Id";
                    
                    using (var reader = queryCmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            users.Add((reader.GetGuid(0), reader.GetString(1), reader.GetInt32(2)));
                        }
                    }
                }
                
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
    return users;
}

额外优化建议 #

• 复用数据库连接：避免频繁打开关闭连接，可通过连接池或线程安全的单例模式复用连接，减少连接开销。
• 调整连接字符串配置：根据需求设置Max Database Size（默认128MB）、Case Sensitive=False等参数，减少不必要的性能损耗。

内容的提问来源于stack exchange，提问作者bairog