Solved

Refining a query for optimized speed

Posted on 2011-03-03
5
230 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 69

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

778 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