Query taking too long

[tomd1969]

Long time listener, first time caller.

I have a database that is split into two files. Let's call them:

1. MyDatabase.accdb
2. MyDatabase_be.accdb

MyDatabase.accdb contains the functionality (forms, queries, reports, etc), and MyDatabase_be.accdb contains all of the tables and data that are imported to MyDatabase.accdb via a link.

One table (CycleCounts) has a field "PickLoc," which is a product's picking location, and is scraped from another source. The first three digits of this PickLoc are the bin number, followed by shelf number, then lane#.

We have to inventory these products one bin at a time, and I need to be sure that all of the SKUs in a bin have been counted before going to the next bin. So, essentially, if we count anything from a bin, we must count *everything* in that bin.

These counts are in a separate table (ShelfCounts) with a many-to-one relationship.

In order to check for missing products, I had to do a query first to get which bins were done on which date (this query is called "BayByDate"):

PARAMETERS vrDate DateTime;
SELECT DISTINCT CycleCounts.CC_Date, Left ([CycleCounts].[PickLoc],3) AS Bin
FROM CycleCounts INNER JOIN ShelfCounts ON CycleCounts.CC_ID=ShelfCounts.CC_ID
WHERE CycleCounts.CC_Date=[vrDate];

Then to check for missing products, I have this query (called "MissingProds"):

PARAMETERS vrDate DateTime;
SELECT CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
FROM CycleCounts
WHERE (((Left([CycleCounts].[PickLoc],3)) In 
      (Select [BayByDate].[Bin] FROM [BayByDate];)) 
   AND ((CycleCounts.CC_ID) Not In 
      (SELECT CC_ID FROM [ShelfCounts];)))
GROUP BY CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
HAVING (((CycleCounts.CC_Date)=[vrDate]))
ORDER BY CycleCounts.CC_1stAFFLOW;

Essentially, if the bin number of the product is in the BayByDate report, but the CC_ID isn't in the ShelfCount table, select it.

The problem is that this query takes forever, sometimes a half hour or more, and this is only going to get worse as more and more records get added to the CycleCounts and ShelfCounts tables. Any ideas on how to speed this up?

Any help is appreciated.

 

[WayneRyan]

Tom,

The JET engine is not designed to handle Nested Select statements very well at all.
Even if you have small tables in the Nested Selects, you will find that it takes a
long, long time.

You will have to figure out a way to Join the tables:

PARAMETERS vrDate DateTime;

SELECT CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
FROM CycleCounts Inner Join [BayByDate] On
       Left([CycleCounts].[PickLoc], 3) = [BayByDate].[Bin] Inner Join [ShelfCounts] On
           [CycleCounts].[CC_ID] = [ShelfCounts].[CC_ID]
GROUP BY CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
HAVING (((CycleCounts.CC_Date)=[vrDate]))
ORDER BY CycleCounts.CC_1stAFFLOW;

hth,
Wayne

 

[tomd1969]

Tom,

The JET engine is not designed to handle Nested Select statements very well at all.
Even if you have small tables in the Nested Selects, you will find that it takes a
long, long time.

You will have to figure out a way to Join the tables:

PARAMETERS vrDate DateTime;
 
SELECT CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
FROM CycleCounts Inner Join [BayByDate] On
       Left([CycleCounts].[PickLoc], 3) = [BayByDate].[Bin] Inner Join [ShelfCounts] On
           [CycleCounts].[CC_ID] = [ShelfCounts].[CC_ID]
GROUP BY CycleCounts.SKU, CycleCounts.PickLoc, CycleCounts.CC_Date
HAVING (((CycleCounts.CC_Date)=[vrDate]))
ORDER BY CycleCounts.CC_1stAFFLOW;

hth,
Wayne

Nope. No dice. I'm getting:

Syntax error (missing operator) in query expression 'Left(CycleCounts.PickLoc, 3)=BayByDate.Bin INNER JOIN ShelfCounts on CycleCounts.CC_ID=ShelfCounts.CC_I'.

Here's the weird part:

I tried to add a calculated field "Bin" in the CycleCounts table equal to =Left(PickLoc,3) thinking that might speed it up. I can see the calculated field working in the *_be.accdb, but it won't show up in the functionality file (the *.accdb file). I've tried refreshing the link using the link manager; I've tried deleting the link and then re-establishing it; I've tried deleting the link, shutting down Access, re-booting the PC, restarting Access, and re-establishing the link to the table. Nothing has worked. Access just won't recognize this field through a linked table for some reason.

 

[tomd1969]

Found a solution to this. It may not be optimal, but it works.

First, I have to list all of the products and their Bins. This query is named "SKUsWithBin":

SELECT CycleCounts.CC_ID, CycleCounts.CC_Date, CycleCounts.SKU, CycleCounts.PickLoc, Left([PickLoc],3) AS Bin
FROM CycleCounts
WHERE (((CycleCounts.CC_Date)=vrDate));

Next, I get a count of ShelfCount rows. This one is named "CountOfSC_IDPerSKU":

SELECT SKUsWithBin.CC_ID, SKUsWithBin.CC_Date, SKUsWithBin.SKU, SKUsWithBin.PickLoc, SKUsWithBin.Bin, Count(ShelfCounts.SC_ID) AS CountOfSC_ID
FROM ShelfCounts RIGHT JOIN SKUsWithBin ON ShelfCounts.CC_ID = SKUsWithBin.CC_ID
GROUP BY SKUsWithBin.CC_ID, SKUsWithBin.CC_Date, SKUsWithBin.SKU, SKUsWithBin.PickLoc, SKUsWithBin.Bin
HAVING (((SKUsWithBin.CC_Date)=vrDate));

Then, I do an inner join with the BinByDate query, and filter out everything but where the Count of SC_IDs is 0. This is called "MissingProductsByBin":

SELECT CountOfSC_IDPerSKU.CC_ID, CountOfSC_IDPerSKU.CC_Date, CountOfSC_IDPerSKU.SKU, CountOfSC_IDPerSKU.PickLoc, CountOfSC_IDPerSKU.Bin
FROM BinByDate INNER JOIN CountOfSC_IDPerSKU ON BinByDate.Bin = CountOfSC_IDPerSKU.Bin
WHERE (((CountOfSC_IDPerSKU.CC_Date)=vrDate) AND ((CountOfSC_IDPerSKU.CountOfSC_ID)=0));

Et voila! This query takes fractions of a second where the other one, if it finished at all, took 30-45 minutes.

Thanks for your assistance!