A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Slow Join



 
 
Thread Tools Display Modes
  #1  
Old May 17th, 2010, 04:43 PM posted to microsoft.public.access.queries
jenniferspnc
external usenet poster
 
Posts: 65
Default Slow Join

I'm trying to rewrite queries for better performance and am stuck on one.
This is how it is written now:
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer INNER JOIN (tbl_parts INNER JOIN tbl_OrderParts ON
tbl_parts.Part_Number = tbl_OrderParts.Part_Number) ON
tbl_manufacturer.Manufacturer_ID = tbl_parts.Manufacturer_ID;

I rewrote it as follows but now it's not updatable (does not allow entry in
my form):
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer, tbl_parts, tbl_OrderParts
WHERE (((tbl_parts.Part_Number)=[tbl_OrderParts].[Part_Number]) AND
((tbl_manufacturer.Manufacturer_ID)=[tbl_parts].[Manufacturer_ID]));

Where have I gone wrong? The first query is super slow and thought I should
revisit using the Inner Joins...

  #2  
Old May 17th, 2010, 06:05 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Slow Join

jenniferspnc wrote:

I'm trying to rewrite queries for better performance and am stuck on one.
This is how it is written now:
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer INNER JOIN (tbl_parts INNER JOIN tbl_OrderParts ON
tbl_parts.Part_Number = tbl_OrderParts.Part_Number) ON
tbl_manufacturer.Manufacturer_ID = tbl_parts.Manufacturer_ID;

I rewrote it as follows but now it's not updatable (does not allow entry in
my form):
SELECT tbl_OrderParts.UniqueID, tbl_OrderParts.Part_Number,
tbl_OrderParts.Sales_Order, tbl_parts.Product_Description, tbl_parts.ECCN,
tbl_parts.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM tbl_manufacturer, tbl_parts, tbl_OrderParts
WHERE (((tbl_parts.Part_Number)=[tbl_OrderParts].[Part_Number]) AND
((tbl_manufacturer.Manufacturer_ID)=[tbl_parts].[Manufacturer_ID]));

Where have I gone wrong? The first query is super slow and thought I should
revisit using the Inner Joins...



While the two queries are logically the same, there may be
something about the second one that Access thinks is too
complex to be updatable. Or, maybe you are trying to update
a field in one of the dependent tables that is not updatable
in either query.

IMO, the Inner Join query is the preferred approach. If it
is slow, the first thing to do is to make sure that you have
an index in each table for the field used in the ON and
WHERE expressions. It looks like some of the fields should
be primary keys and that should be sufficient if you have
properly created the relationships between the tables.

--
Marsh
MVP [MS Access]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.