Query has suddenly become slow

[GPM4663]

Hi Everyone,
I have the following query with a few left joins which was taking about 5 secs to open and now takes 40 secs? The only thing I have changed are the queries that they are based on but they run and open instanteously! Even if I change those queries to make-table queries and then base the query below on the tables it still takes 40 secs to open. I don't understand why it's all of a sudden so slow. This query is used in a report hence the users have been complaining about how slow it has become but I just don't understand why it has happened. ANy advice would be greatly appreciated.

many thanks in advance,

GPM


SELECT A.CustomerNo, A.Name, A.ItemNo, A.Description, A.[Description 2], nz([TotalInvoiceQty])-Nz([TotalCreditQty]) AS ActualQty, B.TotalBudgetQty, nz([TotalInvoiceQty])-Nz([TotalCreditQty])-Nz([totalBudgetQty]) AS QtyVariance, Nz([TotalInvoiceAmt])-Nz([totalCreditAmt]) AS ActualAmount, IIf(Nz([TotalInvoiceAmt])-Nz([totalCreditAmt])=0,0,(Nz([TotalInvoiceAmt])-Nz([totalCreditAmt]))/(nz([TotalInvoiceQty])-Nz([TotalCreditQty]))) AS AvgPrice, Nz([totalInvoiceAmountLCY])-nz([totalCreditAmountLCY]) AS ActualAmountLCY, IIf(Nz([TotalInvoiceAmt])-Nz([totalCreditAmt])=0,0,(Nz([totalInvoiceamountLCY])-nz([totalCreditAmountLCY]))/nz([TotalInvoiceQty])-Nz([TotalCreditQty])) AS AvgPriceLCY
FROM ((qryCustomerItem AS A LEFT JOIN qrySalesInvoice AS I ON A.CustomerItemNo = I.CustomerItemNo) LEFT JOIN qrySalesBudget AS B ON A.CustomerItemNo = B.CustomerItemNo) LEFT JOIN qrySalesCredit AS C ON A.CustomerItemNo = C.CustomerItemNo
WHERE (((Trim([C].[CustomerItemNo] & .[CustomerItemNo] & .[CustomerItemNo] & ""))<>""))
ORDER BY A.CustomerNo;

 

[BNPMike]

You need to appreciate that Jet re-compiles the SQL of all the queries as a new query, so how individual queries run is not entirely relevant.

I notice you have a function in the WHERE clause which I would suspect would make Access bring all the data across (ie do an area sweep, rather than use indexes). If you can think how to get that to be more like WHERE SOMETHING = X, you should get a speed increase.

 

[PHV]

You may try this criteria instead:
WHERE Not (C.CustomerItemNo Is Null AND I.CustomerItemNo Is Null AND B.CustomerItemNo Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GPM4663]

Thanks Guys,
I gave that a go but unfortunately it hasn't made any difference. Is there any thing else I could try?

Thanks,

GPM

 

[BNPMike]

It is odd. I would now go back and try and identify the single cause. Put all the old code back. Does it still run like it did before. Now make each change separately and test it until you find where the application slows down.

You made a changes - did they change the number of records processed/retrieved?

Maybe it was just on the verge of bad before. What, for example is your indexing strategy?

 

[GPM4663]

Hi Everyone,
I just deleted the criteria from the query and it runs instantaneously (even though it gives me all the records, including the zero value ones). I even tried to change the criteria to <>0 for 2 fields instead but it was still as slow. The tables are imported from an erp system so don't naturally have an indices. What is the best way for me to index the tables to maybe speed this process up?

Thanks,

GPM

 

[PHV]

What is the best way for me to index the tables to maybe speed this process up?
either with the GUI in the table design window
or with a CREATE INDEX action(DDL) query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GPM4663]

Hi PHV,
Sorry, I didn't mean that to sound like that. I know how to index but what I'm wondering is which fields should I ensure are indexed in the table i.e. the fields I am searching criteria under or any fields that have unique codes in them?

Thanks

GPM

 

[PHV]

which fields should I ensure are indexed
Without the database schema nor the SQL code of the underlaying queries it's hard to say anything.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

I know this doesn't explain how one minute it is ok and not the next but if you are importing data from another source then this gives you the opportunity to re-order the data into the sequencing that best suits your query.

Professionals would use DDL to add indexes but if you don't want to do that the easiest way is to load data into existing tables. You can then declare indexes (if in doubt, add them wherever they may help). To refresh, delete the data, compact the database, then append the new data.

Access can deal with startlingly inefficient SQL by virtue of high network bandwith and the awesome cpu and buffering power of modern pcs. However ultimately you are safer aiming for efficient coding.