asked on

Access update query takes 1 minute+ to run. Ideas to improve speed?

Hi Experts,

I currently have an Access 2010 query with uses one SQL table and one SQL view.
1) DATA-LOAD_SHEET 2) dbo_View_LoadSheetTopDelNo
This query updates the delivery number (DelvNum) and Postcode details in the DATA-LOAD_SHEET table from the dbo_View_LoadSheetTopDelNo SQL view (these two fields can change quite often so query needs to run to update them). The problem is the query takes a long time to run - usually about 1 minute or more.

The DATA-LOAD_SHEET table currently only has 77 records and the dbo_View_LoadSheetTopDelNo has 7177 records. Opening the two tables individually do not take too long to open. The larger table takes a bit longer. So it seems it’s the query that’s the issue. Any ideas on how I could improve the speed of this query?

Access SQL view is as below:

UPDATE dbo_View_LoadSheetTopDelNo INNER JOIN [DATA-LOAD_SHEET] ON dbo_View_LoadSheetTopDelNo.DocNum = [DATA-LOAD_SHEET].OrderNum SET [DATA-LOAD_SHEET].DelvNum = [dbo_View_LoadSheetTopDelNo].[highest_delivery_number], [DATA-LOAD_SHEET].POSTCODE = [dbo_View_LoadSheetTopDelNo].[ZipCode];

Thanks

mbizup

Give this syntactical variation a try:

UPDATE [DATA-LOAD_SHEET], dbo_View_LoadSheetTopDelNo
SET [DATA-LOAD_SHEET].DelvNum = [dbo_View_LoadSheetTopDelNo].[highest_delivery_number], [DATA-LOAD_SHEET].POSTCODE = [dbo_View_LoadSheetTopDelNo].[ZipCode]
WHERE dbo_View_LoadSheetTopDelNo.DocNum = [DATA-LOAD_SHEET].OrderNum

Open in new window

Saurabh Bhadauria

join on actual table in place of view.. it may help

Thanks,
Saurabh

kevin1983

ASKER

mbizup - Thanks I tried your suggestion but seems to take same length of time to run

kevin1983

ASKER

Saurv - thanks for your suggestion, the reason i'm using a view is because I need to get the most recent delivery note number from the document so cant simply look at the actual table unless I create a new query in access instead of the view but im not sure if this would help.

The SQL for the SQL view used in the access query is as below:

SELECT TrgetEntry, DocEntry, DocNum, NumAtCard, Expr1, CardCode, ShipToCode, Address, U_MAX_BDATE, U_MAX_BTTO, U_MAX_BTFROM, U_MAX_BREF, Name,
DelvDocDelivery, ZipCode, U_AZU_ICB, SalesDocDelivery, highest_delivery_number, DocStatus
FROM (SELECT R.TrgetEntry, O.DocEntry, O.DocNum, O.NumAtCard, DN.DocEntry AS Expr1, O.CardCode, O.ShipToCode, O.Address, O.U_MAX_BDATE, O.U_MAX_BTTO,
O.U_MAX_BTFROM, O.U_MAX_BREF, O.CardName AS Name, ODLN.Address2 AS DelvDocDelivery, C.ZipCode, C.U_AZU_ICB,
O.Address2 AS SalesDocDelivery, ODLN.DocNum AS highest_delivery_number, O.DocStatus, row_number()
OVER (partition BY o.docnum
ORDER BY odln.docnum DESC) AS rn
FROM dbo.RDR1 AS R INNER JOIN
dbo.ORDR AS O ON R.DocEntry = O.DocEntry LEFT OUTER JOIN
dbo.DLN1 AS DN ON DN.DocEntry = R.TrgetEntry AND DN.BaseLine = R.LineNum LEFT OUTER JOIN
dbo.ODLN AS ODLN ON ODLN.DocEntry = DN.DocEntry LEFT OUTER JOIN
dbo.CRD1 AS C ON ODLN.ShipToCode = C.Address) AS x
WHERE x.rn = 1

jerryb30

Run the update as a pass-through query.

kevin1983

ASKER

jerryb30 - please can you clarify what you mean by this?

SOLUTION

jerryb30

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

kevin1983

ASKER

Thanks everyone for your help, running the query directly on SQL server as acerperkins solution suggested seems to be the best solution and works well.

jerryb30 - a pass through query seems ok as another possible option.