Refining a query for optimized speed

Posted on 2011-03-03
Last Modified: 2012-08-13
Brief background of our inventory/sales/shipping system:
Sales Orders (imported & appended to tblCASalesOrders)
Purchase Orders (requirements created based on daily Sales orders - tblPurchaseOrders & tblPurchaseOrderDetails)
Inventory (tblTransactions)
Items (Data relevant to an item, including UPC - tblItems & tblItemDetails)

tblTransactions contains TransID, TransUPC, and TransType along with some other fields

There are four transaction types:
R - Received, S - Shipped, T - Returned, A - Adjusted

The problem (bottleneck) is occurring when shipping orders.  In the Ship Orders form, the users scan the order number barcode which pulls up the order (in less than a second).  They then click on the UPC field and start scanning the barcode(s).  They can scan all items for that order and all will eventually enter, however it takes around 7-10 seconds for each scan to enter as Access is 'calculating ...'

What the form is doing:
When the user clicks on the UPC field (TransUPC), it triggers 'On Got Focus' event which says:
I do this because I want to make sure the item being scanned is relevant to that order and that the item is in inventory (in case it was not scanned in at the Purchase Order receiving stage).

It's the query behind this that I believe is causing the bottleneck.  The field TransUPC is a combo box that joins two queries:

1) qryRcvSOUPC relates tblCASalesOrders and tblItemDetails on SKU
SELECT tblCASalesOrders.[order id], tblItemDetails.upc
FROM tblCASalesOrders INNER JOIN tblItemDetails ON tblCASalesOrders.SKU = tblItemDetails.[Inventory Number]
WHERE (((tblCASalesOrders.[Shipping Status])="Invoice Printed") AND ((tblCASalesOrders.ItemCancelled)=No));

2) qryTransQtyAvail relates tblItemDetails with four queries, qryTransInSum, qryTransInSumRO, qryTransOutSum, qryTransSumAdj on UPC
SELECT tblItemDetails.UPC, qryTransInSum.SumOfTransQty, qryTransInSumRO.SumOfTransQty, qryTransOutSum.SumOfTransQty, qryTransSumAdj.SumOfTransQty, tblItemDetails.[Inventory Number], (Nz([qryTransInSum.SumOfTransQty],0)+Nz([qryTransInSumRO.SumOfTransQty],0)+Nz([qryTransSumAdj.SumOfTransQty],0)-Nz([qryTransOutSum.SumOfTransQty],0)) AS QtyAvail, tblItemDetails.discontinued
FROM (((tblItemDetails LEFT JOIN qryTransInSum ON tblItemDetails.UPC = qryTransInSum.TransUPC) LEFT JOIN qryTransOutSum ON tblItemDetails.UPC = qryTransOutSum.TransUPC) LEFT JOIN qryTransInSumRO ON tblItemDetails.UPC = qryTransInSumRO.TransUPC) LEFT JOIN qryTransSumAdj ON tblItemDetails.UPC = qryTransSumAdj.TransUPC
WHERE (((tblItemDetails.UPC) Is Not Null) AND ((tblItemDetails.discontinued)=No))
ORDER BY tblItemDetails.[Inventory Number];

The four queries are all the same, only differing by the TransType (R,S,T,A)
SELECT tblTransactions.TransUPC, tblTransactions.TransType, Sum(tblTransactions.TransQty) AS SumOfTransQty
FROM tblTransactions
GROUP BY tblTransactions.TransUPC, tblTransactions.TransType
HAVING (((tblTransactions.TransType)="R"));
Basically, i'm taking all current UPC's, summing each TransType and then calculating a total for each UPC.

Is there a better way to do this? A more efficient query?

Thank-you and my apologies if I explained too much,

Jim Jennings
Question by:jimjennings
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 35029502

This will obviously requey the entire recordset.
If you have a lot of records, or the recordset is a complex query, ...then this will obviously slow things down.

<They can scan all items for that order and all will eventually enter...'>
Also, are you sure this is not a limitation of the Scanning software?

<, however it takes around 7-10 seconds for each scan to enter as Access is 'calculating >
If you are calculating values in the form controls themselves, this will produce the "Calculating..." text in the status bar,...and slow things down as well.

My point here is that depending on what you need to do validate things, this may just be what it has to be...

Without having access to the actual DB and knowing all of your requirements, it is difficult to make any specific recommendations.

But as a start you can make sure you have your Table indexes set up properly.
LVL 26

Assisted Solution

Nick67 earned 166 total points
ID: 35040089
boag2000 is definitely right about the indexes.  An index can make a world of difference.

Queries of queries are something to be avoided if the query designer allows it.
Sometimes it throws a conniption at a really complex query and you have no choices.

The next thing is to look at WHERE statements.  Your qryTrans... queries have no WHERE statement.
They may therefore be returning a much larger set of data than the end result requires.

Another thing that can be very efficient is sub-queries.  Basically they are when, instead of a where statement -- WHERE something = "this" -- you have a select statement in the criteria box of the query designer -- IN(select thisID from tblStuff where StuffName like "A*")

Getting the things being limited indexed first is the most important, figuring out how to get the smallest possible recordset returned is next, and then getting aggregates done after is last.

Allen Browne has a good tutorial on subqueries

Accepted Solution

rkharko earned 168 total points
ID: 35154309
You need to ensure that all columns used in Join clause or Where clause are members of one or more indexes on that tables. The following sql script will solve the problem with performance.
CREATE NONCLUSTERED INDEX [IDX_InventoryNumber] ON [dbo].[tblItemDetails] 
	[Inventory Number] ASC

CREATE NONCLUSTERED INDEX [IDX_Status_Item] ON [dbo].[tblCASalesOrders] 
	[Shipping Status] ASC,
	[ItemCancelled] ASC

CREATE NONCLUSTERED INDEX [IDX_tblCASalesOrders_SKU] ON [dbo].[tblCASalesOrders] 

CREATE NONCLUSTERED INDEX [IDX_tblItemDetails_UPC] ON [dbo].[tblItemDetails] 
	discontinued ASC,
	[Inventory Number] ASC

CREATE NONCLUSTERED INDEX [IDX_qryTransInSum_TransUPC] ON [dbo].[qryTransInSum] 
	[TransUPC] ASC

CREATE NONCLUSTERED INDEX [IDX_qryTransInSumRO_TransUPC] ON [dbo].[qryTransInSumRO] 
	[TransUPC] ASC

CREATE NONCLUSTERED INDEX [IDX_tblTransactions_TransUPC] ON [dbo].[tblTransactions] 
	[TransUPC] ASC,
	[TransType] ASC

Open in new window

LVL 69

Expert Comment

ID: 36032380
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question