多个内部联接 [英] Multiple Inner joins

问题描述

你好，
我需要从一组表中检索一些列数据，每个表都有近10万条记录.查询需要检查每个表的ID列，因此我决定使用内部联接并提出此查询. >

Hi there,
I need to retrieve a few column data from a set of tables having nearly 1 lakh records each.The query needs to check on the Id column of each table.So I decided to use Inner joins and came up with this query.

select C.Date_Time,
E.AInPCS_CM001,
E.AInPCS_KWACNet,
B.CP_KWDC,
B.CP_ElecEffic,
B.CP_FT012ACTFuelFlow,
B.CP_FuelSPFact,
B.CP_LoadTime,
B.CP_NumCell,
B.CP_VDCTOTAL,
D.CL012_TempFact
from 
FREEZE a inner join CALCULATED b  on a.ID = b.FREEZE_ID 
inner join ANALOG c on  a.ID = c.FREEZE_ID  
inner join CONTROL_LOOP d on  a.ID = d.FREEZE_ID and b.FREEZE_ID = d.FREEZE_ID And B.DATE_TIME = C.DATE_TIME  and b.DATE_TIME = d.DATE_TIME
inner join STATE_PCS_VSD e on a.ID = e.FREEZE_ID and b.FREEZE_ID = e.FREEZE_ID and b.DATE_TIME = e.DATE_TIME 
inner join DIGITAL f on a.ID = f.FREEZE_ID and b.FREEZE_ID = f.FREEZE_ID and b.DATE_TIME = f.DATE_TIME 
and  e.AInPCS_CM001 > 145 
and e.AInPCS_CM001 IS NOT NULL 
and a.PLANT_ID = 9504 
and a.ID=b.FREEZE_ID  
and a.FREEZE_TYPE = 2
and b.FREEZE_ID = c.FREEZE_ID 
and b.DATE_TIME >= ''4/10/2011'' and b.DATE_TIME <= ''4/15/2011'' 
order by b.DATE_TIME,e.AInPCS_CM001

现在的问题是，此查询需要近2分钟的时间才能得出结果集.我需要微调此查询以更快地执行.请在这方面提出任何建议.

谢谢，
Naresh.

The problem now is that this query takes nearly 2 mins time to give result set. I need to finetune this query to execute faster.Please give any suggestions in this regard.

Thanks,
Naresh.

推荐答案

1)确保正确设置了索引(在要连接它们的列上).
2)您也可以尝试使用临时表.

此处 [

1) Make sure you have your indexes setup properly (on the columns you are joining them on).
2) You could also try using a temporary table.

Some more tips here[^].
If you search on the internet, you should be able to find other tips / articles which can help you optimize your query.

这篇关于多个内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！