多个内部联接

本文介绍了多个内部联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

你好， 我需要从一组表中检索一些列数据，每个表都有近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.