advice for very slow report process -

[treviboy]

I have a report that I have been running for a while and now it seems to have slowed down. It takes over 15 minutes for me to process about 400 records and I don't know why.
The back end is on a file server. There's no problem with the connection - I am sure. I call a function for about 4 fields on a report. here's my code for the function. Are my connections not correct? I need advice ! Do you see anything wrong with my database connections?

Function fConcat(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) As String
Dim db As Database
Dim rs As ADODB.Recordset
Dim varConcat As Variant
Dim count As Integer
Dim strCriteria As String, strSql As String
On Error GoTo err_fconcat
varConcat = Null
Set db = CurrentDb


strSql = "select distinct [" & strFldConcat & "]from[" & strChildTable & "]"
strSql = strSql & "Where "
Select Case strIDType
Case "String":
strSql = strSql & "[" & strIDName & "]='" & varIDvalue & "'"
Case "Long", "Integer", "double":
strSql = strSql & "[" & strIDName & "] =" & varIDvalue

Case Else
GoTo err_fconcat
End Select


Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

With rs
If .RecordCount <> 0 Then
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ","
.MoveNext
Loop
End If
End With
fConcat = Left(varConcat, Len(varConcat) - 1)


end function

 

[Crowley16]

first of all, if you're using an access back end, then DAO will out perform ADO.

There are other things you can do to generally speed up access databases and these things can easily be found elsewhere...

however, using a non-trivial custom function within a query is always extremely slow, so the best way would probably be to get rid of the function altogether.

--------------------
Procrastinate Now!

 

[mp9]

A couple of questions and suggestions:

Q. Why is varConcat a variant. Can't you dim it as a string? I think this may help.

S. As Mr Crowley says, there's some benefit to be had from trying DAO if this is all native Access.

S. You dim variables you don't use (count, strCriteria) - lose these.

S. Since you're within a "With", replace rs.EOF with just .EOF

You may also find the Accessory performance page helpful for other optimising ideas.

 

[treviboy]

thanks for the advice. Do you think also that I should maybe do a 'getrows' in the page-load event ? I'll try your suggestions - thanks a lot. I appreciate the help.