SSMS Query slow join-CodePudding

Tried to join three tables: car, car_and_engine, and engine. The second table, car_and_engine, connects the cars and their engines. A car type has up to three possible engine types. The query is significantly slower than expected (based on experience with similar operations in other languages). Is there anything terribly inefficient about this code?

select engine_type, AVG(horsepower) into #horsepower_by_engine_type
from TRANSPORT.dbo.engine
group by engine_type
go

with temp as(select * from TRANSPORT.dbo.car left join TRANSPORT.dbo.car_and_engine on TRANSPORT.dbo.car_and_engine.car_type_y = TRANSPORT.dbo.car.car_type_x)

select * from temp left join #horsepower_by_engine_type as e1 on temp.engine_type_1 = e1.engine_type
left join #horsepower_by_engine_type as e2 on temp.engine_type_2 = e2.engine_type
left join #horsepower_by_engine_type as e3 on temp.engine_type_3 = e3.engine_type

CodePudding user response：

You don't really need a temp table (except when you are doing some diagnostics). You could replace your temp table syntax with an inline-view.

with temp as(select * from TRANSPORT.dbo.car left join TRANSPORT.dbo.car_and_engine on TRANSPORT.dbo.car_and_engine.car_type = TRANSPORT.dbo.car.car_type)

select * from temp left join 
  (select engine_type, AVG(horsepower) 
   from TRANSPORT.dbo.engine
   group by engine_type) as e1 on temp.engine_type_1 = e1.engine_type
left join 
  (select engine_type, AVG(horsepower) 
   from TRANSPORT.dbo.engine
   group by engine_type) as e2 on temp.engine_type_2 = e2.engine_type
left join 
  (select engine_type, AVG(horsepower) 
   from TRANSPORT.dbo.engine
   group by engine_type) as e3 on temp.engine_type_3 = e3.engine_type

Better still, you could put your summary into your CTE

with temp as (select * from TRANSPORT.dbo.car left join TRANSPORT.dbo.car_and_engine on TRANSPORT.dbo.car_and_engine.car_type = TRANSPORT.dbo.car.car_type),
     avgHP as (select engine_type, AVG(horsepower) from TRANSPORT.dbo.engine group by engine_type)

select * from temp left join avgHP as e1 on temp.engine_type_1 = e1.engine_type
left join avgHP as e2 on temp.engine_type_2 = e2.engine_type
left join avgHP as e3 on temp.engine_type_3 = e3.engine_type