Solved Access query with linked tables slow (1 Viewer)

nector

Member
Local time
Today, 16:45
Joined
Jan 21, 2020
Messages
381
I'm only pulling one record at a time to be used by the VBA record set from Cloud SQL server, I'm using this strategy as per advice from this platform , yes there is an improvement , but the 10 seconds its taking to pull data is too much and users do like this.

Here is my simple Query:

Code:
SELECT tblCustomerInvoice.InvoiceID, tblLineDetails.ItemesID, tblProducts.ProductName, tblProducts.ProductID, tblLineDetails.Quantity, tblLineDetails.UnitPrice, tblLineDetails.Discount, tblLineDetails.IsTaxInclusive, tblLineDetails.RRP, tblLineDetails.VAT, ((([Quantity]*[UnitPrice]))) AS TotalAmount, tblLineDetails.TaxClassA, tblLineDetails.TourismClass, tblLineDetails.ExciseClass, tblLineDetails.InsuranceClass, IIf([IsTaxInclusive]<0,"True","False") AS CGControl, tblProducts.BarCode, tblLineDetails.ESDPrice, tblLineDetails.Duty, tblCustomerInvoice.ReceiptType, tblCustomerInvoice.PaymentMode, tblCustomerInvoice.SalesType, tblCustomerInvoice.LocalPurchaseOrder, tblCustomerInvoice.Cashier, tblCustomerInvoice.BuyerTPIN, tblCustomerInvoice.BuyerName, tblCustomerInvoice.BuyerTaxAccountName, tblCustomerInvoice.BuyerAddress, tblCustomerInvoice.BuyerTel, tblCustomerInvoice.OrignalInvoiceCode, tblCustomerInvoice.OrignalInvoiceNumber, tblCustomerInvoice.TheNotes, tblCustomerInvoice.Moneytype, tblCustomerInvoice.FCRate
FROM tblCustomerInvoice INNER JOIN (tblProducts INNER JOIN tblLineDetails ON tblProducts.ProductID = tblLineDetails.ProductID) ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmCustomerInvoice]![CboEsdInvoices]));

If I convert it to a VIEW in SQL server cloud can I gain performance here, in any cases I will continue pulling one record at a time , lastly can a combo box be attached to a to View also even if in that area the combo box load with empty record until the users type in the first three characters only then can a short list is built
 

Minty

AWF VIP
Local time
Today, 14:45
Joined
Jul 26, 2013
Messages
10,377
Any query with multiple joins to large tables will work better on the server as a view than locally joining them in Access.

This looks like it is simply a look up list for a continuous form?
If so and you don't need to edit it, make sure you open it as a snapshot on the form, that will also speed things up.
 

nector

Member
Local time
Today, 16:45
Joined
Jan 21, 2020
Messages
381
Okay thanks Minty I missed the key word snapshoot, very correct
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:45
Joined
Feb 19, 2002
Messages
43,491
Any query with multiple joins to large tables will work better on the server as a view than locally joining them in Access.
I won't dispute the first part of this sentence but I will dispute the end. Access does NOT do the join locally UNLESS you are joining a SQL Server table to a local table or an Oracle table, etc.

Access makes every effort to make all queries "pass" through. That doesn't mean that you can't defeat Access. You certainly can.

Views of SQL Server table with joins are more efficient for the same reason that querydefs of Jet/ACE tables are more efficient. The server calculates the execution plan and saves it. The plan doesn't have to be calculated on the fly every time the query runs. In the case of SQL Server, you might even be able to add indexes to views to help even more.
 

Users who are viewing this thread

Top Bottom