Запрос выполняется очень долго в клиентском приложении, но быстро в SQL Server Management Studio

Я разрабатываю приложение, которое хранит изображения и связанные с ними метаданные. У меня возникают проблемы при выполнении определенного запроса с использованием NHibernate. Запрос занимает слишком много времени (на моей машине, что-то около 31 секунды ), хотя тот же запрос занимает всего долю секунды при выполнении в SQL Server Management Studio.

Я сократил и расширил проблему до небольшого тестового приложения :

. Сущности:

Тег , состоящий из строки Id (, само значение тега)

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

Изображение , состоящее из идентификатора (int ), имени (строки )и тегов (many -до -many, набор из экземпляров тега )

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>

Я хочу, чтобы следующий запрос :дал мне все изображения, имя которых содержит определенную строку или где любой тег содержит определенную строку.Чтобы найти последний, я использую подзапрос, который дает мне идентификаторы всех изображений с соответствующими тегами. Таким образом, в конце критерии поиска :изображение имеет имя, содержащее определенную строку, или его идентификатор является одним из тех, которые возвращаются подзапросом.

Вот код, выполняющий запрос:

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

Тестовая база данных (СУБД :SQL Server 2008 Express R2 ), к которой я выполняю этот запрос, была создана специально для этого теста и больше ничего не содержит. Я заполнил его случайными данными :10 000 изображений (таблица Изображение), 4000 тэгов (таблица тэг)и примерно 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:

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

запросы быстрые.

Если я использую не подобное, а точное совпадение в моем подзапросе:

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  
...

Итак, мои вопросы:

• Почему запрос намного длиннее при выполнении NHibernate, хотя используемый SQL тот же?
• Как избавиться от разницы? Есть ли настройка, которая может вызвать такое поведение?

Я знаю, что запрос в целом не самая эффективная вещь в мире,но что меня поражает здесь, так это разница между использованием 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 записал в свой журнал в первую очередь ).

Вот скриншот NNHProf

Предупреждения понятны, но не объясняют поведение.

Обновление 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();

SQL:

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

Однако теперь запрос работает немного хуже, чем версия с подзапросами. Я буду исследовать это дальше.