CODEWITHSUNDEEP
sql-serverquery-performance
Dan Schubel
Dan Schubel

Reputation: 279

SQL Server Poor Query performance on production db

The query below performs poorly on a production database but seems fine on a development database. All the tables involved are identical, same columns, datatype, indexes, etc. The production database has slightly more records (maybe +10%). In SSMS the query takes 25-30 seconds to return any results, <1 sec on the dev copy.

The query is generated by a web based crm application via php code. (SuiteCrm) The production and dev database are on the same server. The dev copy was created by restoring a backup of the production db. I do see differences between the two execution plans but I don't understand why they would differ if the tables are identical. (I don't have any experience with execution plans)

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

I've noticed that the query performance is fine if I remove

SELECT TOP (@topcount) * FROM

or remove

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

or remove

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

Something else that concerns me is if I try to run the database tuning advisor on the production db the DTA crashes every time, even if I select one small table. There is a maintenance plan with Reorganize Index that runs nightly. I checked everything I can think of and can't find any differences between the two databases. What could be causing the poor performance and why only on the one database?

Upvotes: 0

Views: 110

Answers (1)

HLGEM
HLGEM

Reputation: 96600

First that code is horrendous. This is a prime example of why you should not generate sql code. If you really need all those LTRIM, RTRIM functions for instance, then your database design needs work too. And you have the same OR condition in there multiple times. 

(((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%' )

Plus what is going on with your database design if this is even something you want to check:

  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 

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

After all very few email addresses have spaces in them.

OR conditions generally slow down performance and by adding a bunch of them that will never affect the result you are creating performance problems.

if you are passing in the full name of this person why are you using like?

If you have both a first and last name field what is the liklihood that this name even exists properly inthat field? I can't even be sure you are getting the correct results, but I woudl find it highly unlikely.

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

this makes more sense to me

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

On the other hand if you are stuck with this code for reasons beyond your control, I would suspect that the difference between prod and dev comes down to two things. First statistics may be out of date (this is different from indexing).

Second, some people develop on dev databases that are considerably smaller than the prod ones. That is always a bad idea. Code that runs fine on a small dataset is often poorly performing on a large one.

Upvotes: 1

Related Questions