Click here to Skip to main content
14,643,391 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100