Solved

Refining a query for optimized speed

Posted on 2011-03-03
5
225 Views
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:
Me.TransUPC.Requery
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
0
Comment
Question by:jimjennings
5 Comments
 
LVL 74

Assisted Solution

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


<Me.TransUPC.Requery>
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.
0
 
LVL 26

Assisted Solution

by:Nick67
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
http://allenbrowne.com/subquery-01.html
0
 
LVL 3

Accepted Solution

by:
rkharko earned 168 total points
ID: 35154309
Hi,
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
)ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IDX_Status_Item] ON [dbo].[tblCASalesOrders] 
(
	[Shipping Status] ASC,
	[ItemCancelled] ASC
) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IDX_tblCASalesOrders_SKU] ON [dbo].[tblCASalesOrders] 
(
	[SKU] ASC
) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IDX_tblItemDetails_UPC] ON [dbo].[tblItemDetails] 
(
	[UPC] ASC,
	discontinued ASC,
	[Inventory Number] ASC
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_qryTransInSum_TransUPC] ON [dbo].[qryTransInSum] 
(
	[TransUPC] ASC
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_qryTransInSumRO_TransUPC] ON [dbo].[qryTransInSumRO] 
(
	[TransUPC] ASC
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_tblTransactions_TransUPC] ON [dbo].[tblTransactions] 
(
	[TransUPC] ASC,
	[TransType] ASC
) ON [PRIMARY]
GO

Open in new window

0
 
LVL 68

Expert Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now