Optimizing query performance with master/detail tables

[mariost]

Hi all,
a rather basic problem I guess but I could not find something to optimize this "common type" of queries.
For my app, I have to export certain data from an Access DB to an Excel file. I am doing this with TransferSpreasheet.
The query must compile data from a Master table that includes information for a Production Order and a Detail table that has information about Customers Orders. One Production Order can include multiple Customer Orders for the same product. There is also a many-to-many table that lists which Customers Orders belong to which Production Orders (using the ProdOrderIDs add OrderIDs).
The problem is a calcuated field I have that concatenate all the Customer orders for each Production Order. Thus, the query returns some data from the Production Order table and in one field it concatenated all customer orders related to this Production Order. This field is populated via a VBA function which retrieves the relevant Customer orders info for the particular Production Order ID. The query returns around 1000 records as of now (to be grow up). The query takes more than 3 minutes in a FE and BE setup. The query and the function runs on the FE.
Any ideas on how to optimize this? It shall be a common problem but unfortunately I cannot solve it.
Thanks
Marios

 

[jdraw]

There may be readers who understand what you are asking, but it is not clear to me.
You are really describing How you have done something which isn't performing to your expectations.

Can you step back and describe a CustomerOrder, a Production Order and a (Production Order)Detail with a few examples? Also more info re the calculated field would be helpful to readers.

It would also be helpful if you could post a jpg of your tables and relationships. You'll have to zip it in order to post it with less than 10 posts.

 

[mariost]

Hi,

sorry for not being clear enough.

Find attached the relationships schema.

See below the qry:

SELECT tblProductionOrder.prod_ID AS ID, tblProductionOrder.prod_proCode AS Code, tblProducts.pro_Description, GetMoldStatusExport([prod_MoldCode]) AS MoldStatus, tblProductionOrder.prod_MachineCode AS Machine, tblProductionOrder.prod_moldCode AS [Mold Code], tblMolds.mold_Description, tblProductionOrder.prod_ProductionHrs AS [Production Hrs], tblProductionOrder.prod_QuantityRequired AS Quantity, tblProductionOrder.prod_BatchNo AS [Batch No], tblProductionOrder.prod_Priority AS Priority, GetPorderCustomers([prod_ID],True) AS Customers
FROM (tblProducts INNER JOIN tblProductionOrder ON tblProducts.pro_Code = tblProductionOrder.prod_proCode) LEFT JOIN tblMolds ON tblProductionOrder.prod_moldCode = tblMolds.mold_Code
WHERE (((tblProductionOrder.prod_Status)="Open"))
ORDER BY tblProductionOrder.prod_Priority;


See below the GetPorderCustomers function



Function GetPorderCustomers(prodID, incDate As Boolean)


If Not IsNull(prodID) Then

Dim objRec As ADODB.Recordset
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
Set objRec = New ADODB.Recordset

Dim SQLstr As String


SQLstr = "SELECT tblCustomers.cus_Name, tblOrders.order_Quantity, tblOrders.order_DateAvailable " & _
" FROM (tblCustomers INNER JOIN tblOrders ON tblCustomers.ID = tblOrders.order_CustomerName) " & _
" INNER JOIN tblJoinProdOrder ON tblOrders.order_ID = tblJoinProdOrder.orderID " & _
" WHERE (((tblJoinProdOrder.prodID)= " & prodID & "));"


objRec.Open SQLstr, cnn, adOpenKeyset, adLockReadOnly, adCmdText

Dim CustomersText As String

CustomersText = ""

If Not objRec.EOF And objRec.RecordCount >= 1 Then

While Not objRec.EOF
If incDate = True Then
CustomersText = CustomersText & objRec("cus_Name") & ": " & objRec("order_Quantity") & " - " & objRec("order_DateAvailable") & vbCrLf
Else
CustomersText = CustomersText & objRec("cus_Name") & ": " & objRec("order_Quantity") & vbCrLf
End If
objRec.MoveNext
Wend

End If

objRec.Close

If Right$(CustomersText, 2) = vbCrLf Then
CustomersText = Left$(CustomersText, Len(CustomersText) - 2)
End If

GetPorderCustomers = CustomersText

End If

End Function

 

[mariost]

Hi,

thanks for replying. Of course I consider joining the tables, the problem is that I want one row for each production order and there are many customer orders for the specific production order. The function basically takes all the customer orders (name,date,qty) concatenate them and put them in one field of the production order row.

Marios

 

[arnelgp]

have you tried opening the BE database (this is advisable for FE/BE).
create a public variable in a module that will hold your BE database instance.

Public BE_DB As Dao.Database

now you must make an instance of your db (to an opening first form, or through autoexec macro).

Public Sub subOpenBE()

set BE_DB = DbEngine.WorkSpace(0).OpenDatabase(Name:=fnGetBEName("yourLinkTableNameHere"), Options:=False,ReadOnly:=False,Connect:="")
End Sub

Public Sub fnGetBEName(strLinkTableName As String) As String
On Error GoTo HandleErr
Dim strConnect As String
strConnect = CurrentDb.TableDefs(strTableName).Connect
' The path and filename are after ";DATABASE=".
fnGetBEName= _
Mid$(strConnect, InStr(strConnect, ";") + 10)
ExitHere:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Msgbox Err.Number & ": " & Err.description, , "agpGetLinkPath"
End Select
Resume ExitHere
Resume
End Function

on closing the db, you must find away to close the db.

Public Sub subCloseBE()
SET BE_DB = Nothing
End Sub

 

[gemma-the-husky]

A general query about what you are doing ...

I struggle to see why a production order would contain orders for many customers.

do you produce exactly the quantity needed to fulfil a given selection of orders? even if you do, I still do not quite understand why the production order would need to cross refer to those particular orders. ie - if you have orders for 950 widgets, do you only produce 950 widgets? do you never get any waste?

I just see two different things.

a) unfulfilled /pending orders
b) uncompleted production

which may or may not be exactly the same.

maybe getting these two queries separately would work a lot more quickly.

However, if you do need to cross match, then maybe there is an indexing issue, or even a table design issue.

 

[mariost]

Hi arnelgp,

I am not sure how this will help. The tables are linked to the FE. Do you mean that by creating and instance of the BE to the FE, will result in running this query faster?

Marios

 

[mariost]

Hi gemma-the-husky

you are right that customer orders are not part of a production order. That's why they are in separate tables.

However we need a report (export to Excel) that will combine the Production Order info with the related customer orders for scheduling purposes. Based on this report the Production Manager schedules which orders will be produced.

Thanks for trying to help

M.

 

[gemma-the-husky]

Hi gemma-the-husky

you are right that customer orders are not part of a production order. That's why they are in separate tables.

However we need a report (export to Excel) that will combine the Production Order info with the related customer orders for scheduling purposes. Based on this report the Production Manager schedules which orders will be produced.

Thanks for trying to help

M.

but that's why I think it's 2 different things. Not related in terms of database relations, just related in terms of business practice.

ie - if you get an order for 500 widgets - do you decide to make them on a particular works production order? If so then you need a structure that holds the details of exactly which sales orders are linked to particular product orders.

if instead you just decide to make 1000 widgets because you have orders for 968 widgets, then it's a different thing, and the database itself won't hold a specific link between the sales orders and the production order.

either way, I can't see that your query, including GetPorderCustomers([prod_ID],True) AS Customers, can match either of the above scenarios.

 

[mariost]

[gemma-the-husky]

the way the factory works is that customer orders are accumulated and a production order is scheduled to cover these orders. There is never a deficit, always a surplus that goes to the stock.

That's the way the company works, and the system was built to support this. I am not saying that's the best way, but this is the way it is and thus the need to relate customer orders with production orders.



M.

 

[gemma-the-husky]

then I think you need an productionorder-salesorder junction table identifying the sales orders associated with the order, and then your query would run much more quickly.

 

[jdraw]

I'm seeing

1 ProductionOrder---->Many CustomerOrders

and total products in a ProductionOrder >= total Products in related Customer Orders
since there is a surplus and never a deficit

I think the database/model needs to be reviewed and redesigned to ensure it meets the business requirements. I'm not convinced that the relationships shown in Post #3 match the requirements.

It would be useful to the OP and readers if a complete description of the business in plain English was presented. I'm also wondering what new reports/info are expected from this database, if any.

 

[CJ_London]

duplicate post

http://www.accessforums.net/queries/optimizing-ms-access-query-56631.html

 

[arnelgp]

yes it will speed up your query.
https://msdn.microsoft.com/en-us/library/dd942824(v=office.12).aspx
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html

 

[mariost]

Hi

[arnelgp]: I will try it. Thanks.

[gemma-the-husky]: There is already a productionorder-salesorder junction table. See post #3.

[jdraw]: As I said this system and business model was already in place and I am sure that it would be more difficult to change the model for this query that runs slow. I agree that the model is not optimized but to change the model is not really an option. If there is something technical we can do to speed this up then fine.

Thanks,

Marios