我需要一个针对此查询的性能调整插入的示例 [英] I need an example of performance tuning insert for this query

问题描述

我一直无法理解以前的查询性能调优。下面是我试图通过添加索引来改进的查询。



感谢您的帮助。



查询：

I've been having trouble understanding with Performance Tuning previous queries. Underneath is the query that I am trying to improve by adding an index.

Thank you for your help.

Query:

select model, descr, mpg,
(
select count(*) from car_details where mpg is not null) as "Number of Cars"
from car_names, car_details
where car_details.id = car_names.id and mpg is null
for (reg_name) in ('NE', 'NW', 'SE', 'SW')
);

SQL脚本文件：



的https://docs.google.com/document/d/1vClWrxZnU3AKhQzeWwqbMtf5du-3EcfHTuLdbgGvxU4/edit?usp=sharing



我尝试了什么：



我试过制作简单的插入语句，但看起来它没有改变性能。

SQL Script File:

https://docs.google.com/document/d/1vClWrxZnU3AKhQzeWwqbMtf5du-3EcfHTuLdbgGvxU4/edit?usp=sharing

What I have tried:

I've tried making simple insert statements but it looks like it made no changes in performance.

推荐答案

摆脱子查询。而是使用CTE创建一个包含要匹配的总数和ID的表。



其余的看似合理。

Get rid of subqueries. Instead use a CTE to create a table with totals and ids to match against.

The rest seems reasonable.

这篇关于我需要一个针对此查询的性能调整插入的示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！