Click here to Skip to main content
15,036,145 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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.
Posted

1 solution

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.
   
Comments
S.M.Naresh 30-Jul-11 7:48am
   
Can u suggest whether to use table indexing to retrieve data from such huge no. of recordss?
Abhinav S 30-Jul-11 8:46am
   
Yes you should be indexing the table, but not every column. Choosing between clustered or non-clustered is always a hard trade-off. Choose whichever appears to be working better for you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900