MS Access - VBA Function Design Question - Performance Concerns

Question

Hi Folks -

I am designing a rather simple solution although I anticipate performance drawbacks - allow me to explain.

I have an Access Table that I am populating via an INSERT and SELECTing from a linked excel file that is refreshed each month. The excel file has a column for Cost Center which from Workday, does not come with the fully qualified string. Therefore, I have another "Master" Cost Center file that is refreshed daily from our MDM system.

The objective is to take the partial string from my excel file and "search" the "Master" file for 'like' digits. If found, return the fully quality ID otherwise return NULL.

My problem is the "Master" Cost Center linked file/table is 300k rows so iterating through 70k rows and checking against a file that s 300k is time consuming with how I have my function setup. I have my function setup as follows:

Function LongNamenew(CostCentr_id As String) As String

    Dim rst As Recordset
    Dim gUnderScore As Variant

    gUnderScore = Mid(CostCentr_id, InStrRev(CostCentr_id, "_") + 1, 256)

    Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Element] FROM [LT-SAP_to_Cost_Center_Full_Reference_Mapping] WHERE [Element] LIKE '*" & gUnderScore & "*'")

    LongNamenew = rst.Fields("Element")
End Function

Is there a way to speed this up? Ar there are, faster ways? Thank you!

Answer 1

Hum, difficult. I would suggest that the 300k row be imported to a Access table. I would then suggest that you index that row.

However, this is a problem:

[Element] LIKE '*" & gUnderScore & "*'"

The above can't use indexing in Access. However, "if" you can match on the start, like this:

[Element] LIKE " & gUnderScore & "*'"

Note how we match on the start (no wild card at the start of the Element test/check.

Also, it not 100% clear which table is 300k rows, and the other 70k.

However, if a match on SomeValue* as opposed to someValue, then some processing tricks can be used.

And with both tables imported into access? If you can loop on the 70k row set? The you can make this run VERY fast. In fact, using the other side (300k rows)? This is also possible, but we NOT be able to execute a whole sql query and inster match - each query you fire off has a cost near the same as processing 10,000 rows. (each time!!!). So, its going to be too slow.

If one can match on SomeValue* and not SomeValue (wild card on each side), and the two tables imported into Access?

Then Access can chew up even 300k rows - and do this VERY fast (under 1 minute would be my guess-estimate).

Given this problem, I would in fact by-pass SQL and use a little known trick in Access (seek()). But seek() can't work on a linked table to Excel, and it can't work for say a linked table to some server database source.

But, if you have both tables in Access? Then yes, you can process this.

But one can't afford a WHOLE query in a function like you have - it will be too slow.

So, unless we can get some kind of indexing in "operation" here, then you don't have practical performance options here (too big to scan 300k each time).

However, if we can drop the inStr() match, and a bit of neat-o code? Then my guess is this much data can be processed in about 1-2 minutes flat.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada