Refining a query for optimized speed

Posted on 2011-03-03
Medium Priority
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 664 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 664 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 672 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 70

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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