Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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 71

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

604 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