L. Moronvalle L. Moronvalle - 3 months ago 8
SQL Question

Improving update performance in ACCESS

I have 2 tables in Access:

tbl_RM: (+ for primary key)
+ CustomerName (short text)
+ CountryCode (short text)
+ RMDate (Date/Time - format mmm-yy)
+ SerialNumber (short text)
BlackClicks (Double)
ColorClicks (Double)
AccentClicks (Double)
ProfessionalColorClicks (Double)


The second table

tbl_Invoices: (+ for primary key)
+ CustomerName (short text)
+ CountryCode (short text)
+ InvoiceDate (Date/Time)
+ SerialNumber (short text)
BlackClicks (Double)
ColorClicks (Double)


tbl_RM contains 98 496 records (out of which 9113 match the query below)

tbl_Invoices contains 9 618 records (all matching the below request).

I want to update tbl_Invoices with data from tbl_RM. here is my update query:

UPDATE tbl_Invoices
INNER JOIN tbl_RM
ON tbl_Invoices.CustomerName = tbl_RM.CustomerName
AND tbl_Invoices.CountryCode = tbl_RM.CountryCode
AND tbl_Invoices.SerialNumber = tbl_RM.SerialNumber
AND MONTH(tbl_RM.RMDate) = MONTH(tbl_Invoices.InvoiceDate)
AND YEAR(tbl_RM.RMDate) = YEAR(tbl_Invoices.InvoiceDate)
SET tbl_Invoices.BlackPages = tbl_RM.BlackClicks + tbl_RM.AccentClicks,
tbl_Invoices.ColorPages = tbl_RM.ColorClicks + tbl_RM.ProfessionalColorClicks


When running this in ACCESS 2013 it takes hours to compute. I guess there is a way to speed that up. Any suggestion will be very welcome.

Answer

The first suggestion is an index on tbl_RM(CustomerName, CountryCode SerialNumber). The might be good enough. If that is not then you might need to handle the dates in the index as well.