主页 / dba / 问题 / 62424
Accepted
Scott Chamberlain
Asked: 2014-04-03 23:24:27 +0800 CST

每个查询计划统计数据都表明我的查询应该更快，但事实并非如此

  • 772

我的情况有点奇怪。我有两个产生相同结果的查询，根据 SSMS 和 SQL Sentry Plan Explorer，第一个查询应该花费 95%，第二个查询应该花费 5%。但是，第一个查询在 132 毫秒内完成，第二个查询在 8,531 毫秒内完成。

--query 1
with results as(
SELECT
      P.[patient_guid]
      ,P.[client_guid]
      ,P.[patient_account_id]
      ,C.[database_guid]
      ,P.[patient_name]   
      ,P.[rabies_tag_number]
      ,P.[rabies_serial_number]
      ,P.[rabies_brand_name]
      ,P.[species]     
      ,P.[breed] 
      ,P.[coat_color]
      ,P.[sex]
      ,P.[birthdate]
      ,P.[latest_visit]
      ,P.[first_visit]
      ,P.[maturity_code]
      ,P.[medical_alert]
      ,P.[chronic_ailment]
      ,P.[continuous_medication]
      ,P.[weight]
      ,P.[doctor_preference]
      ,P.[tattoo_number]
      ,P.[avid_chip_number]
      ,P.[comment]
      ,P.[special_instructions]
      ,P.[health_plan_name]
      ,P.[deleted]
      ,P.[has_lab_results]
      ,P.[trainer_id]
      ,P.[stable_id]
      ,C.[first_name]
      ,C.[last_name]
      ,P.[active]
      ,P.[deceased_date]

      ,row_number() over (order by patient_name) as rownumber
      ,row_number() over (order by patient_name DESC) as totalRows
  FROM [patients] P

  inner join clients C on P.client_guid = C.client_guid
  where C.database_guid = @DatabaseGuid and P.patient_name like '%'+@SearchText+'%')

  select patient_guid, client_guid, patient_account_id, database_guid, patient_name, rabies_tag_number, rabies_serial_number, rabies_brand_name, species, 
         breed, coat_color, sex, birthdate, latest_visit, first_visit, maturity_code, medical_alert, chronic_ailment, continuous_medication, weight, 
         doctor_preference, tattoo_number, avid_chip_number, comment, special_instructions, health_plan_name, deleted, has_lab_results, first_name, last_name, trainer_id, stable_id, active, deceased_date,
         totalRows + rownumber - 1 as total from results where rownumber >= @PageSize * @PageNumber + 1 and rownumber <= @PageSize * @PageNumber + 1+ @PageSize - 1
         ORDER BY patient_name ASC

--query 2
with results as(
SELECT
      P.[patient_guid]
      ,P.[client_guid]
      ,P.[patient_account_id]
      ,C.[database_guid]
      ,P.[patient_name]   
      ,P.[rabies_tag_number]
      ,P.[rabies_serial_number]
      ,P.[rabies_brand_name]
      ,P.[species]     
      ,P.[breed] 
      ,P.[coat_color]
      ,P.[sex]
      ,P.[birthdate]
      ,P.[latest_visit]
      ,P.[first_visit]
      ,P.[maturity_code]
      ,P.[medical_alert]
      ,P.[chronic_ailment]
      ,P.[continuous_medication]
      ,P.[weight]
      ,P.[doctor_preference]
      ,P.[tattoo_number]
      ,P.[avid_chip_number]
      ,P.[comment]
      ,P.[special_instructions]
      ,P.[health_plan_name]
      ,P.[deleted]
      ,P.[has_lab_results]
      ,P.[trainer_id]
      ,P.[stable_id]
      ,C.[first_name]
      ,C.[last_name]
      ,P.[active]
      ,P.[deceased_date]
  FROM [patients] P
  inner join clients C on P.client_guid = C.client_guid
  where C.database_guid = @DatabaseGuid and P.patient_name like '%'+@SearchText+'%')
select results.*, (select count(*) from results) as total
from results
ORDER BY patient_name ASC, patient_guid
OFFSET (@PageSize * @PageNumber) ROWS
FETCH NEXT @PageSize ROWS ONLY;

查询 1 计划 （单击图像查看大图，或单击此处查看 XML）
在此处输入图像描述

查询 2 计划 （单击图像查看大图，或在此处查看 XML）在此处输入图像描述

SQL Sentry Plan Explorer 的输出

+------------+------------+---------+--------+------ ------+--------+--------+------------+-------- -+------------+------------+----------------+-- ----------------------+
| 声明 | 估算成本 | 持续时间 | 中央处理器 | 估算 CPU 成本 | 阅读 | 写 | 估算 IO 成本 | 东行 | 实际行 | 关键查找 | 排序操作 | 哈希匹配操作 |
+------------+------------+---------+--------+------ ------+--------+--------+------------+-------- -+------------+------------+----------------+-- ----------------------+
| 查询 1 | 94.90% | 132 | 140 | 66.80% | 27,187 | 96.10% | 9 | 50 | 1 | 4 | 0 | |
| 查询 2 | 5.10% | 8,525 | 8,531 | 33.20% | 636,973 | 3.90% | 100 | 50 | 1 | 0 | 1 | |
+------------+------------+---------+--------+------ ------+--------+--------+------------+-------- -+------------+------------+----------------+-- ----------------------+

来自的输出SET STATISTICS IO ON

（50 行受影响）
表“患者”。扫描计数 1464，逻辑读取 27172，物理读取 0，预读读取 0，lob 逻辑读取 200，lob 物理读取 0，lob 预读读取 0。
表“客户”。扫描计数 1，逻辑读取 15，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

（50 行受影响）
表“客户”。扫描计数 1，逻辑读取 263329，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。
表“患者”。扫描计数 2，逻辑读取 480565，物理读取 12，预读读取 32362，lob 逻辑读取 200，lob 物理读取 0，lob 预读读取 0。
表“工作台”。扫描计数 0，逻辑读取 0，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

我最近对数据库所做的事情：

我运行了 Database Engine Tuning Advisor 并让它查看我的计划缓存（已经建立了几周）并添加了它推荐的所有统计信息和索引。之后，我UPDATE STATISTICS在 bothclients和patientsoption上都做了一个FULLSCAN。之后，我跑去DBCC FREEPROCCACHE使用旧的统计数据和索引清除所有旧计划。然后，我运行了几次查询以缓存数据，并让任何需要编译的查询计划进行编译。毕竟，我在 SQL Sentry Plan Explorer 中运行查询以获取我在此处列出的数字。

为什么 SQL 认为第一个查询要贵得多，为什么我执行的类似重写做得更好时我的第二个查询花费的时间要长得多？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    第二个查询中的子查询要求子查询需要在记录集返回的每行中运行一次，这让您很生气。如果您查看计划，它会告诉您每个运算符执行了多少次。我敢打赌，您会在第二个查询的某些运算符上看到大量执行。（我现在正在打电话，所以我无法打开计划。）

    • 4

相关问题