SQL Server Плохая производительность запросов в производственной базе данных

Приведенный ниже запрос плохо работает с производственной базой данных, но отлично работает с базой данных разработки. Все задействованные таблицы идентичны, одинаковые столбцы, типы данных, индексы и т. д. В рабочей базе данных немного больше записей (возможно, +10%). В SSMS запрос занимает 25-30 секунд, чтобы вернуть какие-либо результаты, ‹1 секунду на копии dev.

Запрос генерируется веб-приложением crm через php-код. (SuiteCrm) База данных производства и разработки находится на одном сервере. Копия dev была создана путем восстановления резервной копии рабочей базы данных. Я вижу различия между двумя планами выполнения, но не понимаю, почему они будут отличаться, если таблицы идентичны. (У меня нет опыта работы с планами выполнения)

SELECT TOP (@topcount) *
FROM   (SELECT contacts.id,
               LTRIM(RTRIM(ISNULL(contacts.first_name, '') + N' '
                           + ISNULL(contacts.last_name, ''))) AS NAME,
               contacts.first_name,
               contacts.last_name,
               contacts.salutation,
               accounts.NAME                                  account_name,
               jtl0.account_id                                account_id,
               contacts.title,
               contacts.phone_work,
               jt1.user_name                                  assigned_user_name,
               jt1.created_by                                 assigned_user_name_owner,
               N'Users'                                       assigned_user_name_mod,
               contacts.date_entered,
               contacts.date_modified,
               contacts.assigned_user_id,
               ROW_NUMBER()
                 OVER (
                   ORDER BY contacts.date_modified ASC)       AS row_number
        FROM   contacts
               LEFT JOIN contacts_cstm
                      ON contacts.id = contacts_cstm.id_c
               LEFT JOIN accounts_contacts jtl0
                      ON contacts.id = jtl0.contact_id
                         AND jtl0.deleted = 0
               LEFT JOIN accounts accounts
                      ON accounts.id = jtl0.account_id
                         AND accounts.deleted = 0
                         AND accounts.deleted = 0
               LEFT JOIN users jt1
                      ON contacts.assigned_user_id = jt1.id
                         AND jt1.deleted = 0
                         AND jt1.deleted = 0
        WHERE  (( ( LTRIM(RTRIM(ISNULL(contacts.first_name, ''))) LIKE N'abe krebs%'
                     OR LTRIM(RTRIM(ISNULL(contacts.first_name, ''))) LIKE N'abe krebs%' )
                   OR ( LTRIM(RTRIM(ISNULL(contacts.last_name, ''))) LIKE N'abe krebs%'
                         OR LTRIM(RTRIM(ISNULL(contacts.last_name, ''))) LIKE N'abe krebs%' )
                   OR (( contacts.phone_mobile LIKE N'abe krebs%'
                          OR contacts.phone_work LIKE N'abe krebs%'
                          OR contacts.phone_other LIKE N'abe krebs%'
                          OR contacts.phone_fax LIKE N'abe krebs%'
                          OR LTRIM(RTRIM(ISNULL(contacts.phone_mobile, '') + N' '
                                         + ISNULL(contacts.phone_work, '') + N' '
                                         + ISNULL(contacts.phone_other, '') + N' '
                                         + ISNULL(contacts.phone_fax, '') + N' '
                                         + ISNULL(contacts.assistant_phone, ''))) LIKE N'abe krebs%'
                          OR LTRIM(RTRIM(ISNULL(contacts.assistant_phone, '') + N' '
                                         + ISNULL(contacts.phone_fax, '') + N' '
                                         + ISNULL(contacts.phone_other, '') + N' '
                                         + ISNULL(contacts.phone_work, '') + N' '
                                         + ISNULL(contacts.phone_mobile, ''))) LIKE N'abe krebs%' ))
                   OR ( LTRIM(RTRIM(ISNULL(contacts.assistant, ''))) LIKE N'abe krebs%'
                         OR LTRIM(RTRIM(ISNULL(contacts.assistant, ''))) LIKE N'abe krebs%' )
                   OR ( contacts.id IN (SELECT bean_id
                                        FROM   (SELECT eabr.bean_id
                                                FROM   email_addr_bean_rel eabr
                                                       JOIN email_addresses ea
                                                         ON ( ea.id = eabr.email_address_id )
                                                WHERE  eabr.deleted = 0
                                                       AND ea.email_address LIKE N'abe krebs%') email_derived) )
                   OR ( LTRIM(RTRIM(ISNULL(accounts.NAME, ''))) LIKE N'abe krebs%'
                         OR LTRIM(RTRIM(ISNULL(accounts.NAME, ''))) LIKE N'abe krebs%' )
                   OR (( contacts.first_name LIKE N'abe krebs%'
                          OR LTRIM(RTRIM(ISNULL(contacts.first_name, '') + N' '
                                         + ISNULL(contacts.last_name, ''))) LIKE N'abe krebs%'
                          OR LTRIM(RTRIM(ISNULL(contacts.last_name, '') + N' '
                                         + ISNULL(contacts.first_name, ''))) LIKE N'abe krebs%' )) ))
               AND contacts.deleted = 0) AS a
WHERE  row_number > 0

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

SELECT TOP (@topcount) * FROM

или удалить

( contacts.id IN 
    (select bean_id  
        from (SELECT eabr.bean_id ...

или удалить

        OR ( LTRIM(RTRIM(ISNULL(accounts.name,''))) LIKE N'abe krebs%' 
    OR LTRIM(RTRIM(ISNULL(accounts.name,''))) LIKE N'abe krebs%' )

Что-то еще, что меня беспокоит, это то, что если я попытаюсь запустить советник по настройке базы данных в производственной базе данных, DTA выйдет из строя каждый раз, даже если я выберу одну маленькую таблицу. Существует план обслуживания с Reorganize Index, который запускается каждую ночь. Я проверил все, что мог придумать, и не нашел различий между двумя базами данных. Что может быть причиной низкой производительности и почему только в одной базе данных?

sql-server query-performance

Dan Schubel 23.03.2015 источник

comment

Этот запрос довольно ужасен. Использование таких вещей, как LTRIM(RTRIM(ISNULL(contacts.first_name, '')), означает, что условие недопустимо, и почему оно повторяет одни и те же условия несколько раз? - Martin Smith 23.03.2015

comment

Вы также используете TOP, но ORDER BY отсутствует, поэтому у вас нет возможности узнать, какие строки будут возвращены. - Sean Lange 23.03.2015

comment

Если сделать account.name НЕ NULL и гарантировать, что данные никогда не будут вводиться как нулевые (добавьте к нему значение по умолчанию ('')), это позволит вам исключить часть этого избыточного кода. И каждый раз, когда вы используете TOP, у вас ДОЛЖЕН быть ORDER BY, иначе ваши результаты будут потенциально недетерминированными. Но да, с этим запросом много проблем с производительностью... - pmbAustin 23.03.2015

Ответы (1)

arrow_upward
1
arrow_downward

Во-первых, этот код ужасен. Это яркий пример того, почему вы не должны генерировать код sql. Если вам действительно нужны все эти функции LTRIM, RTRIM, например, то дизайн вашей базы данных тоже нуждается в доработке. И у вас есть одно и то же условие ИЛИ несколько раз.

(((LTRIM(RTRIM(ISNULL(contacts.first_name,''))) LIKE N'abe krebs%' 
    OR LTRIM(RTRIM(ISNULL(contacts.first_name,''))) LIKE N'abe krebs%' ) 
    OR ( LTRIM(RTRIM(ISNULL(contacts.last_name,''))) LIKE N'abe krebs%' 
    OR LTRIM(RTRIM(ISNULL(contacts.last_name,''))) LIKE N'abe krebs%' )

Кроме того, что происходит с вашей базой данных, если вы хотите это проверить:

  OR ( ( contacts.phone_mobile like N'abe krebs%' 
    OR contacts.phone_work like N'abe krebs%' 
    OR contacts.phone_other like N'abe krebs%' 
    OR contacts.phone_fax like N'abe krebs%'

OR ( contacts.id IN 
        (select bean_id  
            from (SELECT eabr.bean_id 
            FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) 
            WHERE eabr.deleted=0 AND ea.email_address LIKE N'abe krebs%') email_derived))

В конце концов, очень немногие адреса электронной почты содержат пробелы.

Условия ИЛИ обычно замедляют производительность, и, добавляя их кучу, которые никогда не повлияют на результат, вы создаете проблемы с производительностью.

если вы передаете полное имя этого человека, почему вы используете лайк?

Если у вас есть поля имени и фамилии, какова вероятность того, что это имя вообще существует в этом поле? Я даже не могу быть уверен, что вы получаете правильные результаты, но я считаю это маловероятным.

LTRIM(RTRIM(ISNULL(contacts.first_name,''))) LIKE N'abe krebs%' ) 
    OR ( LTRIM(RTRIM(ISNULL(contacts.last_name,''))) LIKE N'abe krebs%'

это имеет больше смысла для меня

where contacts.first_name = N'abe'
and contacts.last_name= N'krebs'

С другой стороны, если вы застряли с этим кодом по независящим от вас причинам, я подозреваю, что разница между prod и dev сводится к двум вещам. Первая статистика может быть устаревшей (это отличается от индексации).

Во-вторых, некоторые люди разрабатывают базы данных разработчиков, которые значительно меньше, чем рабочие. Это всегда плохая идея. Код, который отлично работает с небольшим набором данных, часто плохо работает с большим.

HLGEM 23.03.2015

comment

Я согласен, что это уродливый запрос. Это происходит из функции глобального поиска приложения crm. Поэтому я полагаю, что он пытается учесть тот факт, что пользователь может ввести номер телефона, адрес электронной почты, факс и т. д. и т. д. - Dan Schubel; 24.03.2015

comment

Есть лучшие способы сделать глобальный поиск. Те, которые не убьют производительность вашей системы. Конечно, это звучит как какой-то COTS (коммерческий готовый продукт), и я еще никогда не видел ни одного из тех, которые правильно запрашивали базу данных, и мне приходилось поддерживать многие из этих приложений на протяжении многих лет. Поскольку вы застряли с этим, я бы посоветовал вам обновить статистику. - HLGEM; 24.03.2015

comment

Это приложение CRM с открытым исходным кодом под названием Suite. Он был выбран отделом продаж без консультации со мной. Его установил айтишник, который с тех пор покинул компанию. Постараюсь обновить статистику и посмотреть, что получится. Все еще интересно, почему происходит сбой DTA??? - Dan Schubel; 24.03.2015

comment

Я думаю, что глобальный поиск — это глупо, но пользователи увидели разницу в производительности и говорят, что с базой данных что-то не так. - Dan Schubel; 24.03.2015

comment

Обновил статистику с помощью sp_updatestats Без изменений. Все еще не понимаю, почему запрос работает лучше в другой базе данных, которая является копией рабочей базы данных. Может есть какая-то коррупция? - Dan Schubel; 25.03.2015

comment

Может быть аппаратная проблема. Также является ли другой сервер той же операционной системой и установлен ли он на том же уровне, что и база данных и ОС? Иногда небольшая разница в окружающей среде может вызвать проблемы. - HLGEM; 25.03.2015

comment

2 базы данных находятся на одном сервере, на одном диске. База данных dev была создана путем восстановления резервной копии рабочей базы данных. Существует план обслуживания, запускаемый каждую ночь в производственной базе данных, который выполняет проверку целостности, индексы реорганизации и резервное копирование. Я также создал более чистую версию запроса; нет Rtrim, Ltrim, IsNull, удалены элементы из предложения where - та же производительность. Если я удалю имя учетной записи LIKE N'abe krebs%', то это прекрасно работает. Таблица учетных записей имеет одинаковое количество записей в двух базах данных. - Dan Schubel; 26.03.2015

comment

Я скажу пользователям, что приложение создает дерьмовые sql-запросы, но они все равно будут спрашивать, почему с другой базой данных лучше? - Dan Schubel; 26.03.2015

SQL Server Плохая производительность запросов в производственной базе данных

Ответы (1)

Похожие вопросы