Very poor performance on multi-join query (1 Viewer)

[GanzPopp]

Hi all,

I'm having some serious performance issues with my query. All tables are setup with unique indexes. The main table has ~500k rows and most LUTs have <300 rows (except for tblDataConnected, which has ~250k lines). I'm using Access 2010 with an ACCDB database on XP. Not all columns necessarily have a value, many have NULLs.

SELECT A.Name AS Vliegveld, D.Timestamp, UCASE(DT.Name)+" "+A.Code+" "+UCASE(RT.Code) AS CCCCARR, 
D.DDHHMMSS, RD.Name, D.DDDFF1, D.DN1, D.DX1, D.FX1, D.FN1, D.VV1, 
W1.Name, W2.Name, W3.Name, C1.Name, C2.Name, C3.Name, C4.Name, TT.Name, TD.Name, 
QNH.Name, QFE.Name, T.Name, D.TL, WS.Name, R.Name, MTI.Name, RVR.Name
FROM ((((((((((((((((((((tblDataNew AS D LEFT JOIN tblDataConnected AS DC ON DC.SpecialID=D.ID) 
LEFT JOIN tblAirports AS A ON D.AirportID=A.ID) 
LEFT JOIN tblDataTypes AS DT ON D.DataTypeID=DT.ID) 
LEFT JOIN tblRunwayTypes AS RT ON RT.ID=D.RunwayTypeID) 
LEFT JOIN lutRD AS RD ON RD.ID=D.RD1) 
LEFT JOIN lutWeather AS W1 ON W1.ID=D.WW1) 
LEFT JOIN lutWeather AS W2 ON W2.ID=D.WW2) 
LEFT JOIN lutWeather AS W3 ON W3.ID=D.WW3) 
LEFT JOIN lutCloudLayer AS C1 ON C1.ID=D.CLD1) 
LEFT JOIN lutCloudLayer AS C2 ON C2.ID=D.CLD2) 
LEFT JOIN lutCloudLayer AS C3 ON C3.ID=D.CLD3) 
LEFT JOIN lutCloudLayer AS C4 ON C4.ID=D.CLD4) 
LEFT JOIN lutTemperature AS TT ON TT.ID=D.TT1) 
LEFT JOIN lutTemperature AS TD ON TD.ID=D.TD1) 
LEFT JOIN lutPressure AS QNH ON QNH.ID=D.QNH1) 
LEFT JOIN lutPressure AS QFE ON QFE.ID=D.QFE1) 
LEFT JOIN lutTrend AS T ON T.ID=D.TREND1) 
LEFT JOIN lutCodes AS WS ON WS.ID=D.WS) 
LEFT JOIN lutRemarks AS R ON R.ID=D.REMARK1) 
LEFT JOIN lutCodes AS MTI ON MTI.ID=D.MTI) 
LEFT JOIN lutCodes AS RVR ON RVR.ID=D.RVR

Is it the amount of joins which is causing problems? Is Access having difficulties handling NULL values? Even adding a where-clause to limit the number of rows from the main table (to say anything with ID<1000) doesn't help at all!

In another query with the same data but less joins required (so less efficient storage), Access performs very fast and without problems.

Any help or thought is appreciated!

 

[CJ_London]

Have you optimised your indexing - ensure all fields that are linked have indexes.

 

[GanzPopp]

Yes, I have. All lookup tables have 1 index (on the ID relational field).

 

[CJ_London]

nulls can slow things down,

can you put a record with 0 (or 99 or whatever) value ID in the lookup table then change all the nulls to 0 (or 99 or whatever) in your two main tables - to see if this speeds thing up

 

[Galaxiom]

Even adding a where-clause to limit the number of rows from the main table (to say anything with ID<1000) doesn't help at all!

The Where won't make a difference because the join is processed first. A Where would only reduce the task if it was included in a subquery which was subsequently joined instead of the table.

 

[GanzPopp]

I went with another (less space-efficient) way which performs much better. Thanks all for your input!

 

[jdraw]

Would you like to show us the "other way"? It may help a reader at some time, and it performs better than your original.

 

[GanzPopp]

The other way is simply using less joins, so Access can handle it better. Many values are now stored directly in the main table instead of a look up table, implying increased redundancy.