Query taking too long to open

[Mark A. Sam]

Hello,

I was working on a continuous form, filtering it using VBA according to
different textboxes on the form. One of the filters is [Prod Code]. There
as no problem with it. I added a fitler for [Cust Code]. No problem with
that until I selected them together, then the recordset wouldn't be
delivered. After playing with the query, opening it, and filtering by
selection, I found that the [Prod Code] field is the problem. It seems to
hang up when I filter by this field. I let it run for up to 15 minutes
before cancelling.

The Access version is A2000. The Backend is SQlServer. The query has two
linked tables and 3 local tables. It has almost 300,000 records and opens
in about 6 seconds without any filtering.

Thanks for any help and God Bless,

Mark A. Sam

 

[June7 via AccessMonster.com]

Post your query code showing the use of both criteria for review and analysis.

Hello,

I was working on a continuous form, filtering it using VBA according to
different textboxes on the form. One of the filters is [Prod Code]. There
as no problem with it. I added a fitler for [Cust Code]. No problem with
that until I selected them together, then the recordset wouldn't be
delivered. After playing with the query, opening it, and filtering by
selection, I found that the [Prod Code] field is the problem. It seems to
hang up when I filter by this field. I let it run for up to 15 minutes
before cancelling.

The Access version is A2000. The Backend is SQlServer. The query has two
linked tables and 3 local tables. It has almost 300,000 records and opens
in about 6 seconds without any filtering.

Thanks for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

I'm not sure what this will tell you since the problem exists using the
FilterBySelection menu option, but here it is. Also I don't think I was too
clear in my explanation, but the problem is with one field, [Prod Code] not
two fields. Thank you for your help.

SELECT [Order Entry Header].ordCustID AS CustID, [Order Entry Header].ordID,
[Order Entry ST Products].DetProdLocation AS Location, [Order Entry
Header].ordJob AS [Order Number], [Order Entry Header].ordCustName AS
[Customer Name], [Order Entry Header].ordInvoiceNumber AS [Invoice Number],
[Order Entry Header].ordInvDate AS [Invoice Date], [Order Entry ST
Products].ordDetID, [Order Entry ST Products].detQty AS Qty, [Order Entry ST
Products].detProdCode AS [Prod Code], [Order Entry ST Products].detExt AS
[Selling Price], CCur(nz([Mat Cost],0)) AS [Material Cost],
Format(nz(IIf([Selling Price]=0,0,[Mat Cost]/[Selling Price]),0),"Percent")
AS [Mat Cost Percent], CCur(nz([Lab Cost],0)) AS [Labor Cost],
Format(nz(IIf([Selling Price]=0,0,[Lab Cost]/[Selling Price]),0),"Percent")
AS [Lab Cost Percent], CCur(nz([Serv Cost],0)) AS [Services Cost],
Format(nz(IIf([Selling Price]=0,0,[Serv Cost]/[Selling Price]),0),"Percent")
AS [Serv Cost Percent], CCur(nz([Mat Cost],0)+nz([Lab Cost],0)+nz([Serv
Cost],0)) AS [Total Cost], Format(IIf([Selling Price]=0,0,(nz([mat
cost],0)+nz([lab cost],0)+nz([serv cost],0))/[Selling Price]),"Percent") AS
[Tot Percent]
FROM ((([Order Entry Header] INNER JOIN [Order Entry ST Products] ON [Order
Entry Header].ordID = [Order Entry ST Products].ordID) LEFT JOIN [_Cost
Analysis Det Labor Temp] ON [Order Entry ST Products].ordDetID = [_Cost
Analysis Det Labor Temp].ordDetID) LEFT JOIN [_Cost Analysis Det Material
Temp] ON [Order Entry ST Products].ordDetID = [_Cost Analysis Det Material
Temp].ordDetID) LEFT JOIN [_Cost Analysis Det Services Temp] ON [Order Entry
ST Products].ordDetID = [_Cost Analysis Det Services Temp].ordDetID
WHERE ((([Order Entry Header].ordCustID)=55) AND (([Order Entry ST
Products].detProdCode)="pd"))
ORDER BY [Order Entry Header].ordInvoiceNumber, [Order Entry
Header].ordInvDate;

Post your query code showing the use of both criteria for review and
analysis.

Hello,

I was working on a continuous form, filtering it using VBA according to
different textboxes on the form. One of the filters is [Prod Code].
There
as no problem with it. I added a fitler for [Cust Code]. No problem with
that until I selected them together, then the recordset wouldn't be
delivered. After playing with the query, opening it, and filtering by
selection, I found that the [Prod Code] field is the problem. It seems to
hang up when I filter by this field. I let it run for up to 15 minutes
before cancelling.

The Access version is A2000. The Backend is SQlServer. The query has two
linked tables and 3 local tables. It has almost 300,000 records and opens
in about 6 seconds without any filtering.

Thanks for any help and God Bless,

Mark A. Sam

 

[Mark A. Sam]

I'm not sure why this is the issue with the one particular field, but the
local tables are created use a MakeTable query so that orddetID (the linking
field) was not a primary key. When I changed it manually, it resolved the
issue.

 

[John Spencer]

Making a field the primary key automatically assigns an index to the
field. You should have gotten the same effect by just indexing the
field. Indexes speed up joins, searches, and sorts significantly with
large tables.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Mark A. Sam]

John,

This hadn't been a problem for 5 years since I made a form for my Client to
see data filtered various ways. The problem happened the other day when he
wanted to filter by customer. I set it up to do that and suddenly the
problem occured. But I added the primary key on the locals tables after
they are created and that solved it.

God Bless,

Mark