为什么执行时间不同 [英] Why execution Time different

问题描述

  SELECT  i.IMAGE_ID，count（ DISTINCT  p.formnumber ）
  FROM  image_details i 
  LEFT   JOIN  tin_user t  ON  t.image_id = i.IMAGE_ID 
  LEFT  < span class =code-keyword> JOIN  population p  ON  p.tin = t.tin 
  GROUP   BY  i.IMAGE_ID 
  ORDER   BY   2  

<大>执行时间> 41分钟

返回行6623

  SELECT  i.IMAGE_ID，count（ DISTINCT  p.formnumber）
  FROM  image_details i，tin_user t，population p 
  WHERE  t.image_id = i.IMAGE_ID  AND  p.tin = t.tin 
  GROUP   BY  i.IMAGE_ID 
  ORDER   BY   2  

执行时间< 27秒



返回第6623行





Table_Name（没有记录）（列数）

< big> Image_details（ 6733 ）（ 13 ）
 tin_user（ 746367 ）（ 6 ）
 population（ 743829 ）（ 50 ）< / big> 

解决方案

每个查询在执行之前由数据库引擎优化。这意味着引擎试图找到执行查询的最快计划。



影响执行时间的因素有很多。例如：

- 相关索引的存在

- 优化程序使用的统计数据

- 优化期间计算的排列数
- 执行查询时的工作量

- 内存中存在所需的数据块

- 返回结果时网络上的工作负载等等。



所以你应该做的是调查不同查询的计划。要加注星标，请查看以下文档：获取查询执行计划信息 [ ^

SELECT  i.IMAGE_ID , count( DISTINCT p.formnumber)
FROM image_details i
LEFT JOIN tin_user t ON t.image_id = i.IMAGE_ID
LEFT JOIN population p ON p.tin = t.tin
GROUP BY i.IMAGE_ID
ORDER BY 2

execution time > 41 Minutes
returned rows 6623

SELECT  i.IMAGE_ID, count( DISTINCT p.formnumber)
FROM image_details i , tin_user t , population p
WHERE t.image_id = i.IMAGE_ID AND p.tin = t.tin
GROUP BY  i.IMAGE_ID
ORDER BY 2

execution time < 27 Seconds

returned row 6623


Table_Name (No of Records) (No of Columns)

<big>Image_details (6733) (13)
tin_user (746367) (6)
population (743829) (50)</big>

解决方案

Each query is optimized by the database engine before execution. This means that the engine tries to find the fastest plan to execute the query.

There are several factors affecting the execution time. For example:
- presence of relevant indexes
- statistics used by the optimizer
- the number of permutations calculated during the optimization
- workload when the query is executed
- presence of the desired data blocks in the memory
- workload on the network when returning results and so on.

So what you should do is to investigate the plans of different queries. To star, have a look at the documentation at: Obtaining Query Execution Plan Information[^]

这篇关于为什么执行时间不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！