Solved Function appears to slow report being generated?

[Gasman]

Hi all,
I am just doing some reports, purely for myself with regard to the passengers I take in the Community Car.
I want to have them sorted by Surname and then Title and then Address.
So I created this small function which appears to do what I want

Public Function SurnameSort(strPassenger As String) As String
Dim strTitle As String, strName As String, strAddress As String
Dim strPart() As String
Dim i As Integer
'For i = 1 To Len(strPassenger)
    'Debug.Print Asc(Mid(strPassenger, i, 1)) & " " & Mid(strPassenger, i, 1)
'Next
' Need to get rid of any CHR(10) embedded from Excel
strPassenger = Replace(strPassenger, Chr(10), "")
strPart = Split(strPassenger, ",")
strTitle = Left(strPart(0), InStr(1, strPart(0), " ") - 1)
strName = Mid(strPart(0), InStr(1, strPart(0), " ") + 1)
SurnameSort = strName & "," & strTitle & "," & strPart(1)
'Debug.Print SurnameSort
End Function

Having that in my query, the query responds with just a slight delay, going to the last record 1541 is almost immediate.

SELECT GCDPivotData.Passenger, GCDPivotData.Due, GCDPivotData.Given, GCDPivotData.Tip, GCDPivotData.Percent, SurnameSort([Passenger]) AS Surname
FROM GCDPivotData
WHERE (((GCDPivotData.Passenger)<>"0")) OR (((GCDPivotData.Due)<>0));

However having that in the source of my report and grouping by Surname, the report pretty much hangs. I see the progress bar for query about 80% full, but that is it.

Now when I had surname as just

Surname: Mid([Passenger],InStr([Passenger]," "))

it opened immediately?

This is the same if I add a sort in the report, previously I was just using Group By.

So what could be causing this?
It does finally open, has opened whilst I was typing this, but not really acceptable?

The reason why I created the function is that I have both a Mr & Mrs at the same address, and the Instr() version would group those as one, not separate people.


TIA

 

[The_Doc_Man]

You are changing environments during your function call.

SQL is a compiled processor. It can be quite fast. Once you enter the SQL environment, normally you don't want to leave it again until the query is done. When you use functions inside of SQL - like MID() or INSTR() - SQL can call these COMPILED functions directly from the DLL (dynamic linked library file) that holds them.

However, you have now added a detailed VBA function that must be interpreted. This means that you must leave the SQL environment once per function call to invoke VBA's processing techniques.

The Access report processor is ALSO compiled code so could have called the string functions from the DLL directly, but again, you introduced interpretive VBA code that means you must jump out of that compiled environment. Because you are doing this in a report's SORT sub-phase, you have to call that routine A LOT. Remember that VBA is pseudo-compiled. You don't execute VBA - you interpret it. However, SQL and the report generator (and, for that matter, the form generator) involve mostly compiled code, which is why they are so fast. And which is why when you have complex and repetitive VBA involved, they aren't as fast any more.

This repeated environmental context switch (between compiled vs. interpreted) is, I believe, what is taking so long. This is a case where denormalization and other ugly speed-optimizer techniques might be helpful. Divide and conquer this problem.

Add a field in the source table to contain the "Sorting" version of that surname. That field becomes essentially static - computed once when you make that person's entry - so that the report doesn't have to change computational context so often, it just picks up the field set aside for this special purpose. Having the same name twice perhaps violates normalization rules, but it will DRASTICALLY increase the speed of processing. And, as kludges go, it ain't the worst I've ever seen.

 

[Gasman]

Thank you Doc,
That is what I have just actually done.
However the source was my Excel Master Workbook and a certain sheet.

So I ended up making a table that holds that data plus that Surname field. Now that works as expected.
Fortunately this is not a recurring theme, else as you know bloat would be an issue.

I will only need to do this twice more I expect, so I can live with the bloat.
It is amazing how Access suffers from bloat from deleting all records all the time and yet Foxpro managed to handle that with a ZAP command?

You would expect the different teams to at least talk to each other now and again?

 

[The_Doc_Man]

You would expect the different teams to at least talk to each other now and again?

Originally, they didn't... and they are still not on the same teams inside of MS.

MS bought FoxPro and used some of its features in Access, but they were and are separate products referencing their different origins.