Performance Issues with VBA function using recordset

[ctakacs]

Hi Folks -

I am designing a rather simple solution although I an experiencing some pretty servere performance issues due to the size of my dataset. Allow me to explain.

I have an Access Table that I am populating via an INSERT, which SELECTs from a linked excel file that is refreshed each month. The excel file is ~70 rows has a column for Cost Center which from Workday, comes in an abbreviated form. Therefore, I have another linked table which is a "Master" Cost Center file that is refreshed daily from our MDM system.

The objective is to take the partial Cost Center ID from my linked excel file and "search" the "Master" file for the same digits. If found, return the fully quality ID otherwise return NULL.

My problem (I think) is the size of each data set. My excel file is 70k rows and the "Master" Cost Center linked file/table is 300k rows. I'm calling my VBA function from my SELECT statement. My function is as follows:

Function LongNamenew(CostCentr_id As String) As String

    Dim abbrev As Variant
    Dim gUnderScore As Variant
    Dim gHyphen As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    'Get the database and Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("LT-SAP_to_Cost_Center_Full_Reference_Mapping")
    
    ' CostCentr_id is sent as XXX - XXXX_xxxxxxx so extract the digits following the "_" symbol
    abbrev = Left(CostCentr_id, 3)
    gUnderScore = Mid(CostCentr_id, InStrRev(CostCentr_id, "_") + 1, 256)
    gHyphen = Mid(CostCentr_id, InStrRev(CostCentr_id, "-") + 1, 256)
    
    If abbrev = "SAU" Then
        'Search for the first matching record
        rst.FindFirst "[Element] LIKE '*" & gHyphen & "*'"
    Else
        'Search for the first matching record
        rst.FindFirst "[Element] LIKE '*" & gUnderScore & "*'"
    End If
        
    'Check the result
    If Not rst.NoMatch Then
        LongNamenew = rst!Element
    Else
        LongNamenew = ""
    End If
End Function

The challenge I am running into is that each time I hit the function, I'm opening the Recordset. Is there a more efficient way to meet my needs? Perhaps store the Recordset into a variable and just search the variable? I'm not sure. Right now it runs for hours and never ends up finishing.

Thank you, all!

[Gicu]

Could you please show us a sample of your data, both the spreadheet side and the master table. I woould try to create a calculated field in a query then join on that the other table\query.

Cheers,
Vlad

[ctakacs]

HI Vlad -

Sure thing. For instance, here is the column from my Excel file which is linked as "Global_Monthly_Headcount" that my Function is pulling the values from:

Cost Center - ID
ALB - 4348_348500
ARE - 0977_759320630
ARE - 0977_759320630
ARE - 0977_759320630
ARE - 8011_1125120
ARE - 8011_1124803
ARE - 8011_1124810

The Master Cost Center data set is a linked csv file called "LT-SAP_to_Cost_Center_Full_Reference_Mapping":


As an example, Element contains the full ID. As you can see above, ARE - 8011_1124803 is passed to my function, and then the digits 1124803 are used which would ultimately return 10000001124803.

Please let me know if you need anything else.

[CJ_London]

That will be pretty slow - you excel file won’t be indexed and I suspect your Sap file won’t be either. Import both into a temporary dB and ensure the relevant fields are indexed. Having said that you are using like *....* which negates the use of indexing. So might be worth splitting the cost centre code into its separate components as part of the import process, apply indexing to these new fields and use = rather than like.

you might find then you don’t need your function at all and can just use sql

[Gicu]

I agree with Ajax that importing and indexing would probably improve the performance a lot. Meanwhile can you try to create a query based on the linked spreadsheet, bring in the [Cost Center - ID] field and add a calculated field ShortID:Mid([Cost Center - ID],12) (I think you have 11 characters before the "short ID" that starts after the underscore). Then create a second query where you join the ShortID from the first query to the SHORT_C... (sorry but your screen cut off the entire name of the field) from LT-SAP_to_Cost_Center_Full_Reference_Mapping and bring the Element field in.

Cheers,
Vlad