CODEWITHSUNDEEP
sql-serveroptimizationindexing
fisdelom
fisdelom

Reputation: 39

Optimize query in sql server

I have a query in Sql Server that I will like to optimize. 

SELECT user_type_name = CASE user_type_id 
                          WHEN 1 THEN 'Admin' 
                          WHEN 5 THEN 'Super Admin' 
                          WHEN 3 THEN 'Writer' 
                          WHEN 4 THEN 'Reader' 
                        END, 
       user_can_log = CASE user_inactive 
                        WHEN 1 THEN 'No' 
                        ELSE 'Yes' 
                      END, 
       (SELECT COUNT(*) 
        FROM   t_fthread 
        WHERE  fthread_creator_userid = user_id)         AS number_tickets, 
       (SELECT COUNT(*) 
        FROM   t_email 
        WHERE  email_to LIKE '%' + user_email + '%' 
                OR email_cc LIKE '%' + user_email + '%') AS number_emails, 
       * 
FROM   t_user 
       LEFT JOIN t_organisation 
         ON user_org_id = organisation_id 
WHERE  user_org_id = 42 
ORDER  BY user_last_name, 
          user_first_name

The query takes too much time to run. thanks to query analyzer, I've identified the part in the query that takes too much time, It's this section:

(select count(*) from t_email where email_to like '%'+user_email+'%' or email_cc like '%'+user_email+'%') as number_emails.

I'm trying to rewrite the query to still get the number_emails but in every case, it's still very slow.

I've tried to create the indexes, but it's impossible to create index on user_email and user_cc. Both columns are ntext types and on sql server 2000, it's not possible to create index on theses columns. I've run analyze the query with Database Engine Tune Advisor and I've run the recommendations provided by the tools.

CREATE NONCLUSTERED INDEX [_dta_index_t_fthread_15_2073058421__K5] ON [dbo].[t_fthread]
(
[fthread_creator_userid] ASC
)

CREATE STATISTICS [_dta_stat_1365579903_4_3] ON [dbo].[t_user]([user_last_name], [user_first_name])

CREATE STATISTICS [_dta_stat_1365579903_1_5] ON [dbo].[t_user]([user_id], [user_org_id])

CREATE NONCLUSTERED INDEX [_dta_index_t_user_15_1365579903__K5_K1] ON [dbo].[t_user]
(
[user_org_id] ASC,
[user_id] ASC
)

But the query still takes lot of time to finish the execution.

Upvotes: 0

Views: 275

Answers (4)

Filip Popović
Filip Popović

Reputation: 2655

Your query indicates bad design. You should either

  • normalize your database if all users from to and cc are in your database, or
  • keep a track of number of emails sent

Normalize your database

Requirement: all users from to and cc are in your database (no emails sent to email addresses out of organization)

Instead of storing emails in to and cc, create new tables and store email id as well as user_ids from to and cc.

Keep a track of number of emails sent

Add two columns (Number_To, Number_CC) in t_user table and increment them as needed (when sending emails, storing it to t_email table, ...). If You decide to go this way, watch out for concurrency, it is best to do UPDATE t_user SET Number_To = Number_To + 1 instead of selecting current Number_To value and then updating to new value.

Upvotes: 3

Rahul
Rahul

Reputation: 77876

Try this query once. Also, try = operator instead LIKE

SELECT tu.*,

(case when  user_type_id = 1 then 'Admin' 
       when user_type_id = 5 then 'Super Admin' 
       when user_type_id = 3 then 'Writer' 
       when user_type_id = 4 then  'Reader'
       else 'somethingelse' 
       end) as user_type_name, 

    (case when user_inactive = 1 then 'No'
     else 'Yes' end) as user_can_log,

    (select count(*) as number_tickets from  t_fthread where
    fthread_creator_userid=user_id()),

    (select count(*) as number_emails from  t_email where email_to like '%[email protected]%'
  or email_cc like '%[email protected]%') 

   FROM t_user tu left join t_organisation torg on tu.user_org_id=
  torg.organisation_id   where tu.user_org_id = 42  order by tu.user_last_name, 
  tu.user_first_name

Upvotes: 0

Dimi Takis
Dimi Takis

Reputation: 4949

Well, you could do the following for the email:

    SELECT COUNT(*) 
    FROM   t_email 
    WHERE  
       (
            PATINDEX('%' + user_email + '%', email_to) != 0
            OR PATINDEX('%' + user_email + '%', email_cc) != 0
       )

Upvotes: 0

vstrien
vstrien

Reputation: 2605

Is it possible to do the search using:

 email_to like user_email+'%'

, or even code all alternatives? Or, even better, store the user email (that will being searched for later) in a "cleansed" form in the database?

The wildcard in front of user_email is the evil one: textual searches starting with '%' will always be slow, because they're unpredictable, and you'll never know where in the string the search text will be found.

For example, consider the text:

[email protected]@this.text

While this is a relatively small string, the search for the text "[email protected]" will go like this

  • "k" found on position one
  • match still on position two
  • mismatch on position three

this goes two times on, and the third time the ''@'' is found. Then mismatch at position four.

Only after about 36 comparison-operations, SQL Server knows the string matches. And that is for one row only.. So try to avoid wildcards at the beginning of string comparisons.

Upvotes: 0

Related Questions