The higher the database size, the slower the query process?

[agust]

I am developing a database and testing it with dummy data. With 50,000 records, the query process using SQL command is just OK. But if I double the total records, the query process takes longer time. The query is to make recapitulation using SUM of SQL command based on certain criteria. Any idea to make the query faster?

Thanks.

 

[WayneRyan]

agust,

50K records isn't that much ... just how long are we talking about?

Execution speed is dependant on a lot of things.

First, do you have the indexes set for all of your joins?
Are the datatypes efficient (Numeric) or inefficient (strings)?

Do you have any "weird" constructs like Nested Selects?
Things of this type can cripple JET.

Lastly, we are talking about Native Access queries (not
ADO/DAO recordsets), and there are no network considerations.

Wayne

 

[agust]

Hi Wayne,

Thanks for the reply. I am testing on a standalone computer and using SQL command for ADO recordset. I used nested LEFT JOIN before and now I reduce it to a single LEFT JOIN. It is faster now - but not as I expect yet. It seems that this join makes the query slow. In fact some of the keys are texts (IDHealthFacility,IDVillage,IDDiagCode) and you said it is inefficient. Below is the SQL command:

INSERT INTO TableRecapMonthlyReport(IDHealthFacility,IDVillage,[Month],[Year],IDDiagCode,IDAgeGroup,TotalNewCase,TotalOldCase,TotalGeneralCase) SELECT '" & listData.Value & "' AS IDHF, 'DUMMY' AS IDV, " & RecapMonth & " AS Mo, " & RecapYear & " AS Yr, '" & sRec.Fields(0) & "' AS IDDC, " & sRec2.Fields(0) & " AS IDAG, SUM(TableMonthlyLB1.TotalNewCase), SUM(TableMonthlyReport.TotalOldCase), SUM(TableMonthlyReport.TotalGeneralCase) FROM TableMonthlyReport LEFT JOIN TableHealthFacilityInfo ON TableMonthlyReport.IDHealthFacility = TableHealthFacilityInfo.IDHealthFacility WHERE TableMonthlyReport.Month=" & RecapMonth & " AND TableMonthlyReport.Year=" & RecapYear & " AND TableHealthFacilityInfo.IDHealthCenter='" & listData.Value & "' AND TableMonthlyReport.IDDiagCode='" & sRec.Fields(0).Value & "' AND TableMonthlyReport.IDAgeGroup=" & sRec2.Fields(0).Value