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.
下面的查询在生产数据库上表现不佳,但在开发数据库上看起来很好。所涉及的所有表都是相同的,相同的列,数据类型,索引等。生产数据库的记录略多(可能+ 10%)。在SSMS中,查询需要25-30秒才能返回任何结果,在开发副本上<1秒。
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)
该查询由基于Web的crm应用程序通过php代码生成。 (SuiteCrm)生产和开发数据库位于同一服务器上。通过还原生产数据库的备份来创建开发副本。我确实看到了两个执行计划之间的差异,但我不明白为什么如果表格完全相同,它们会有所不同。 (我对执行计划没有任何经验)
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?
我担心的另一件事是,如果我尝试在生产数据库上运行数据库调优顾问程序,DTA每次都会崩溃,即使我选择了一个小表。有一个维护计划与重组索引每晚运行。我检查了我能想到的一切,并且找不到两个数据库之间的任何差异。什么可能导致糟糕的性能,为什么只在一个数据库?
1 个解决方案
#1
1
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.
首先,代码是可怕的。这是为什么不应该生成sql代码的一个主要示例。如果你真的需要所有那些LTRIM,例如RTRIM函数,那么你的数据库设计也需要工作。并且你有多次相同的OR条件。
(((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.
OR条件通常会降低性能并添加一堆永远不会影响您创建性能问题的结果的条件。
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).
另一方面,如果您因为无法控制的原因而坚持使用此代码,我会怀疑prod和dev之间的区别归结为两件事。第一个统计信息可能已过期(这与索引不同)。
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.
其次,有些人在dev数据库上开发,这些数据库比prod数据库小得多。这总是一个坏主意。在小型数据集上运行良好的代码通常在大型数据集上运行不佳。
#1
1
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.
首先,代码是可怕的。这是为什么不应该生成sql代码的一个主要示例。如果你真的需要所有那些LTRIM,例如RTRIM函数,那么你的数据库设计也需要工作。并且你有多次相同的OR条件。
(((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.
OR条件通常会降低性能并添加一堆永远不会影响您创建性能问题的结果的条件。
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).
另一方面,如果您因为无法控制的原因而坚持使用此代码,我会怀疑prod和dev之间的区别归结为两件事。第一个统计信息可能已过期(这与索引不同)。
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.
其次,有些人在dev数据库上开发,这些数据库比prod数据库小得多。这总是一个坏主意。在小型数据集上运行良好的代码通常在大型数据集上运行不佳。