I have the following query, which is producing the results I need in my overview report, but it is EXTREMELY slow! It takes almost 12 seconds to execute on the local machine with is a very high spec brand new quad core machine with 8GB of RAM. This database will be running across a network, with some of the clients being pentium 4 machines :eek: so I need to refactor it somehow!

Here is the SQL I have so far for the top level query, which is pulling data from another query which I will add further down:


SELECT
qryAll_Lote_info.Sector,
qryAll_Lote_info.Manzana,
Count(qryAll_Lote_info.Lote_No) AS No_de_lotes,
DCount("Lote_No","tblLotes","EstadoNo = 1 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Disponible,
DCount("Lote_No","tblLotes","EstadoNo = 2 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Vendido,
DCount("Lote_No","tblLotes","EstadoNo = 3 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Contrato_Privado,
DCount("Lote_No","tblLotes","EstadoNo = 4 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Hipotecado,
DCount("Lote_No","tblLotes","EstadoNo = 5 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Libre,
Sum(qryAll_Lote_info.Lote_Valor) AS Manzana_Valor,
Sum(qryAll_Lote_info.Total_Pagado_USD) AS Total_Pagado_USD

FROM qryAll_Lote_info
GROUP BY qryAll_Lote_info.Sector, qryAll_Lote_info.Manzana, DCount("Lote_No","tblLotes","EstadoNo = 1 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 2 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 3 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 4 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 5 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'");
Here is the SQL for the subquery (qryAll_Lote_info):

SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblLotes.Lote_Valor, (SELECT DISTINCT
SUM(tblPagos.Pago_USD)
FROM
tblPagos
WHERE
tblLotes.LoteID = tblPagos.LoteID) AS Total_Pagado_USD, tblLotes.EstadoNo
FROM tblLotes;
I really need some help in how to refactor this and I know there MUST be a much better way of pulling all this data together in to one.

TIA
Jon

bump to the top