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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|