sql - 查询在客户端应用程序中需要很长时间，但在 SQL Server Management Studio 中很快

Question

我正在开发一个存储图像和相关元数据的应用程序。我在使用 NHibernate 执行某个查询时遇到了问题。该查询花费的时间太长(在我的机器上大约为 31 秒)，尽管在 SQL Server Management Studio 中执行相同的查询只需要几分之一秒。

我已将问题简化并提取到一个小型测试应用程序中:

实体:

标签，由Id(字符串，标签值本身)组成

public class Tag
{
    public virtual string Id { get; set; }
}

Image，由Id(int)、Name(字符串)和Tags(多对多，Tag实例集)组成

public class Image
{
    private Iesi.Collections.Generic.ISet<Tag> tags = new HashedSet<Tag>();

    public virtual int Id { get; set; }

    public virtual string Name { get; set; }

    public virtual IEnumerable<Tag> Tags
    {
        get { return tags; }
    }

    public virtual void AddTag(Tag tag)
    {
        tags.Add(tag);
    }
}

我正在使用具有以下映射的“按代码映射”:

public class TagMapping : ClassMapping<Tag>
{
    public TagMapping()
    {
        Id(x => x.Id, map => map.Generator(Generators.Assigned));
    }
}

public class ImageMapping : ClassMapping<Image>
{
    public ImageMapping()
    {
        Id(x => x.Id, map => map.Generator(Generators.Native));
        Property(x => x.Name);
        Set(x => x.Tags, 
            map => map.Access(Accessor.Field),
            map => map.ManyToMany(m2m => { }));
    }
}

NHibernate/数据库配置如下所示:

  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
      <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
      <property name="connection.connection_string_name">PrimaryDatabase</property>
        <property name="format_sql">true</property>
    </session-factory>
  </hibernate-configuration>
  <connectionStrings>
    <add name="PrimaryDatabase" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=PerfTest;Integrated Security=True" />
  </connectionStrings>

我想实现以下查询:给我名称包含特定字符串或任何标签包含特定字符串的所有图像。为了找到后者，我使用了一个子查询，它为我提供了所有带有匹配标签的图像的 ID。所以最后的搜索条件是:图像的名称包含特定的字符串，或者它的 ID 是子查询返回的 ID 之一。

这是执行查询的代码:

var term = "abc";
var mode = MatchMode.Anywhere;

var imagesWithMatchingTag = QueryOver.Of<Image>()
    .JoinQueryOver<Tag>(x => x.Tags)
    .WhereRestrictionOn(x => x.Id).IsLike(term, mode)
    .Select(x => x.Id);

var qry = session.QueryOver<Image>()
    .Where( Restrictions.On<Image>(x => x.Name).IsLike(term, mode) ||
            Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingTag))
    .List();

我运行此查询的测试数据库(DBMS:SQL Server 2008 Express R2)是专门为此测试创建的，不包含任何其他内容。我用随机数据填充它:10.000 个图像(表格图像)、4.000 个标签(表格标签)和大约 200.000 个图像和标签之间的关联(表格标签)，即。每个图像有大约 20 个相关的标签。数据库

SQL NHibernate 声称使用的是:

SELECT
    this_.Id as Id1_0_,
    this_.Name as Name1_0_
FROM
    Image this_
WHERE
    (
        this_.Name like @p0
        or this_.Id in (
            SELECT
                this_0_.Id as y0_
            FROM
                Image this_0_
            inner join
                Tags tags3_
                    on this_0_.Id=tags3_.image_key
            inner join
                Tag tag1_
                    on tags3_.elt=tag1_.Id
            WHERE
                tag1_.Id like @p1
        )
    );
@p0 = '%abc%' [Type: String (4000)], @p1 = '%abc%' [Type: String (4000)]

鉴于我正在创建的查询，这看起来很合理。

如果我使用 NHibernate 运行这个查询，查询需要大约 30+ 秒( NHibernate.AdoNet.AbstractBatcher - ExecuteReader took 32964 ms )并返回 98 个实体。

但是，如果我直接在 Sql Server Management Studio 中执行等效查询:

DECLARE @p0 nvarchar(4000)
DECLARE @p1 nvarchar(4000)

SET @p0 = '%abc%'
SET @p1 = '%abc%'    

SELECT
    this_.Id as Id1_0_,
    this_.Name as Name1_0_
FROM
    Image this_
WHERE
    (
        this_.Name like @p0
        or this_.Id in (
            SELECT
                this_0_.Id as y0_
            FROM
                Image this_0_
            inner join
                Tags tags3_
                    on this_0_.Id=tags3_.image_key
            inner join
                Tag tag1_
                    on tags3_.elt=tag1_.Id
            WHERE
                tag1_.Id like @p1
        )
    );

查询时间远少于一秒(并返回 98 个结果)。

进一步实验:

如果我只按名称或仅按标签搜索，即:

var qry = session.QueryOver<Image>()
    .Where( Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingTag))
    .List();

或者

var qry = session.QueryOver<Image>()
    .Where(Restrictions.On<Image>(x => x.Name).IsLike(term, mode))
    .List();

查询速度很快。

如果我在子查询中不使用 like 但完全匹配:

var imagesWithMatchingTag = QueryOver.Of<Image>()
    .JoinQueryOver<Tag>(x => x.Tags)
    .Where(x => x.Id == term)
    .Select(x => x.Id);

查询也很快。

将名称的匹配模式更改为 Exact 不会改变任何内容。

当我调试程序并在查询执行时暂停时，托管调用堆栈的顶部如下所示:

[Managed to Native Transition]   
System.Data.dll!SNINativeMethodWrapper.SNIReadSync(System.Runtime.InteropServices.SafeHandle pConn, ref System.IntPtr packet, int timeout) + 0x53 bytes  
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadSni(System.Data.Common.DbAsyncResult asyncResult, System.Data.SqlClient.TdsParserStateObject stateObj) + 0xa3 bytes   
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() + 0x24 bytes  
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadBuffer() + 0x1f bytes     
System.Data.dll!System.Data.SqlClient.TdsParserStateObject.ReadByte() + 0x46 bytes   
System.Data.dll!System.Data.SqlClient.TdsParser.Run(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj) + 0x67 bytes     
System.Data.dll!System.Data.SqlClient.SqlDataReader.ConsumeMetaData() + 0x22 bytes   
System.Data.dll!System.Data.SqlClient.SqlDataReader.MetaData.get() + 0x57 bytes  
System.Data.dll!System.Data.SqlClient.SqlCommand.FinishExecuteReader(System.Data.SqlClient.SqlDataReader ds, System.Data.SqlClient.RunBehavior runBehavior, string resetOptionsString) + 0xe1 bytes  
...

所以，我的问题是:

即使使用的 SQL 是相同的，为什么当由 NHibernate 执行时，查询的时间会如此之长？

我怎样才能摆脱差异？是否有可能导致此行为的设置？

我知道一般来说查询并不是世界上最有效的事情，但是这里让我震惊的是使用 NHibernate 和手动查询之间的区别。这里肯定发生了一些奇怪的事情。

很抱歉这篇很长的帖子，但我想尽可能多地介绍这个问题。非常感谢您的帮助!

更新 1:
我已经使用 NHProf 测试了应用程序，但没有太多附加值:NHProf 显示执行的 SQL 是

SELECT this_.Id   as Id1_0_,
       this_.Name as Name1_0_
FROM   Image this_
WHERE  (this_.Name like '%abc%' /* @p0 */
         or this_.Id in (SELECT this_0_.Id as y0_
                         FROM   Image this_0_
                                inner join Tags tags3_
                                  on this_0_.Id = tags3_.image_key
                                inner join Tag tag1_
                                  on tags3_.elt = tag1_.Id
                         WHERE  tag1_.Id like '%abc%' /* @p1 */))

这正是我之前发布的内容(因为那是 NHibernate 首先写入其日志的内容)。

这是 NHProf 的屏幕截图


警告是可以理解的，但不能解释行为。

更新 2
@surfen 建议先将子查询的结果从数据库中提取出来，然后将它们粘贴回主查询中:

var imagesWithMatchingTag = QueryOver.Of<Image>()
    .JoinQueryOver<Tag>(x => x.Tags)
    .WhereRestrictionOn(x => x.Id).IsLike(term, mode)
    .Select(x => x.Id);

var ids = imagesWithMatchingTag.GetExecutableQueryOver(session).List<int>().ToArray();

var qry = session.QueryOver<Image>()
    .Where(
            Restrictions.On<Image>(x => x.Name).IsLike(term, mode) ||
            Restrictions.On<Image>(x => x.Id).IsIn(ids))
    .List();

虽然这确实使主查询再次快速，但我宁愿不采用这种方法，因为它不适合实际应用程序中的预期用途。有趣的是，这速度要快得多。我希望子查询方法同样快，因为它不依赖于外部查询。

更新 3
这似乎与 NHibernate 无关。如果我使用普通的 ADO.NET 对象运行查询，我会得到相同的行为:

var cmdText = @"SELECT this_.Id   as Id1_0_,
                        this_.Name as Name1_0_
                FROM   Image this_
                WHERE  (this_.Name like  @p0 
                            or this_.Id in 
                        (SELECT this_0_.Id as y0_
                        FROM   Image this_0_
                            inner join Tags tags3_
                                on this_0_.Id = tags3_.image_key
                            inner join Tag tag1_
                                on tags3_.elt = tag1_.Id
                        WHERE  tag1_.Id like  @p1 ));";

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["PrimaryDatabase"].ConnectionString))
{
    con.Open();
    using (var txn = con.BeginTransaction())
    {
        using (var cmd = new SqlCommand(cmdText, con, txn))
        {
            cmd.CommandTimeout = 120;
            cmd.Parameters.AddWithValue("p0", "%abc%");
            cmd.Parameters.AddWithValue("p1", "%abc%");

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Match");
                }
            }

        }
        txn.Commit();
    }
}

更新 4

查询计划(点击放大):

慢查询


快速查询


计划中肯定存在差异。

更新 5

由于 Sql Server 似乎确实将子查询视为相关，我尝试了一些不同的方法:我将与名称相关的标准移动到子查询本身:

var term = "abc";
var mode = MatchMode.Anywhere;

var imagesWithMatchingTag = QueryOver.Of<Image>()
    .JoinQueryOver<Tag>(x => x.Tags)
    .WhereRestrictionOn(x => x.Id).IsLike(term, mode)
    .Select(x => x.Id);

var imagesWithMatchingName = QueryOver.Of<Image>()
    .WhereRestrictionOn(x => x.Name).IsLike(term, mode)
    .Select(x => x.Id);

var qry = session.QueryOver<Image>()
    .Where(
      Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingName) ||        
      Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingTag) 
    ).List();

生成的 SQL:

SELECT
    this_.Id as Id1_0_,
    this_.Name as Name1_0_
FROM
    Image this_
WHERE
    (
        this_.Id in (
            SELECT
                this_0_.Id as y0_
            FROM
                Image this_0_
            inner join
                Tags tags3_
                    on this_0_.Id=tags3_.image_key
            inner join
                Tag tag1_
                    on tags3_.elt=tag1_.Id
            WHERE
                tag1_.Id like @p0
        )
        or this_.Id in (
            SELECT
                this_0_.Id as y0_
            FROM
                Image this_0_
            WHERE
                this_0_.Name like @p1
        )
    );
@p0 = '%abc%' [Type: String (4000)], @p1 = '%abc%' [Type: String (4000)]

这似乎打破了相关性，因此查询再次变得“快速”(“快速”如“目前可接受”)。查询时间从 30+ 秒减少到约 170 毫秒。仍然不是轻量级查询，但至少可以让我从这里继续。我知道一个 "like '%foo%'"永远不会超快。如果遇到最坏的情况，我仍然可以转向专门的搜索服务器(Lucene、solr)或真正的全文搜索。

更新 6
我能够重写查询以根本不使用子查询:

var qry = session.QueryOver(() => img)
    .Left.JoinQueryOver(x => x.Tags, () => tag)
    .Where(
        Restrictions.Like(Projections.Property(() => img.Name), term, mode) ||
        Restrictions.Like(Projections.Property(() => tag.Id), term, mode))
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();

查询语句:

SELECT
    this_.Id as Id1_1_,
    this_.Name as Name1_1_,
    tags3_.image_key as image1_3_,
    tag1_.Id as elt3_,
    tag1_.Id as Id0_0_
FROM
    Image this_
left outer join
    Tags tags3_
        on this_.Id=tags3_.image_key
left outer join
    Tag tag1_
        on tags3_.elt=tag1_.Id
WHERE
    (
        this_.Name like @p0
        or tag1_.Id like @p1
    );
@p0 = '%abc%' [Type: String (4000)], @p1 = '%abc%' [Type: String (4000)]

但是，该查询现在的性能比带有子查询的版本略差。我会进一步调查这个。

Answer 1

我敢打赌，这是第二个很慢的查询:

var qry = session.QueryOver<Image>()
.Where( Restrictions.On<Image>(x => x.Name).IsLike(term, mode) ||
        Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingTag))
.List();

您只为第一个查询提供了 SQL。第二个呢？你在 SQL Management Studio 下测试过吗？按照@JoachimIsaksson 的建议使用 SQL Server Profiler 来找出 NHibernate 在服务器端执行哪些查询。

这看起来你正在加载 97 image对象进入内存。他们每个人有多大？

编辑

另一个赌注是您的第一个查询为第二个查询执行广告内部查询。尝试对第一个查询执行 .List() 以将标签加载到内存中。

编辑 2

从查询计划来看，您的查询确实被称为 Correlated subquery .
您提到这些查询很快:

var qry = session.QueryOver<Image>()
.Where( Subqueries.WhereProperty<Image>(x => x.Id).In(imagesWithMatchingTag))
.List();

或者

var qry = session.QueryOver<Image>()
.Where(Restrictions.On<Image>(x => x.Name).IsLike(term, mode))
.List();

只需 UNION 它们，你应该得到与分别运行它们相同的结果。
还要确保所有连接列都有索引。

这就是 IS IN(查询)的问题——你不能确定数据库是如何执行它的(除非你以某种方式强制它使用某个计划)。也许您可以将 .In() 更改为 JoinQueryOver() 以某种方式？