Question

我在Sql服务器上有一个问题,我要优化。

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
)

但是,要完成执行还有很多时间。

Answer 1

你的问询表明设计不好。或者

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

需求:所有用户进出和进入您的数据库(没有发送电子邮件给组织地址)

不要在<条码>至和<条码>上储存电子邮件,而是制作新的表格和储存电子邮件,以及用户。

Keep a track of number of emails sent

增加两栏(Number_To, Number_CC):t_user. table and increment them as need(在发送电子邮件、将其储存到t_email table,......)。如果你决定采取这种方式,看看是否一致,最好做到<代码>。 UPDATE t_user SET number_ To = 编号:To + 1 ,代替选择目前的Number_ 价值,然后更新为新的价值。

Answer 2

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?

用户(电子邮件)面前的野心是邪恶的:从百分比开始的文字搜索总是缓慢的,因为文字搜索无法预测,你永远不知道搜索文本的哪里。

例如,审议案文:

[email protected]@this.text

虽然这相对较小,但检索“[电子邮箱: Protect]将照此办理。

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

这两次,第三次发现“@”。然后,在4位职位上错配。

只有在进行了约36次比较之后,服务器才知道有线搭桥。这只是一行。因此,在进行扼杀比较时,试图避免野心。

Answer 3

您也可在电子邮件中做以下工作:

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

Answer 4

问一问。另外,<代码>=的操作人改为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

Normalize your database

Keep a track of number of emails sent

友情链接