speeding up a query

[frank]

I have a query with a function call that is used as the
recordsource for a listbox. The queries performance is
slow due to the function call and/or function design. I
was hoping to get some ideas on speeding things up. I
needed to display in a list box, descriptions (1 or many)
that are assigned to each piece of media. My approach was
to pass the Media_ID in a query to a function that will
return a text string with the following
format: "xxxx/xxxx/xxxx". This is working fine except
that it's very slow. Any suggestions?

Thanks in advance.

In the query, i call the function by; Internal #:
fGetInternalNumDesc(MediaID)

Public Function fGetInternalNumDesc(MediaID As Long) As
String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strInternalDesc As String

Set db = CurrentDb()

strSQL = "SELECT tblInternalNums.InternalNumDesc " & _
"FROM tblInternalNums INNER JOIN
tblMediaInternalNums " & _
"ON tblInternalNums.InternalNumID = " & _
"tblMediaInternalNums.InternalNumID " & _
"WHERE tblMediaInternalNums.MediaID=" & MediaID


Set rst = db.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
Do Until rst.EOF
strInternalDesc = strInternalDesc & "/" &
rst.Fields("InternalNumDesc")
rst.MoveNext
Loop
End If

If Len(strInternalDesc) > 0 Then
strInternalDesc = Right(strInternalDesc, Len
(strInternalDesc) - 1)
End If
fGetInternalNumDesc = strInternalDesc

End Function

 

[Dirk Goldgar]

I have a query with a function call that is used as the
recordsource for a listbox. The queries performance is
slow due to the function call and/or function design. I
was hoping to get some ideas on speeding things up. I
needed to display in a list box, descriptions (1 or many)
that are assigned to each piece of media. My approach was
to pass the Media_ID in a query to a function that will
return a text string with the following
format: "xxxx/xxxx/xxxx". This is working fine except
that it's very slow. Any suggestions?

Thanks in advance.

In the query, i call the function by; Internal #:
fGetInternalNumDesc(MediaID)

Public Function fGetInternalNumDesc(MediaID As Long) As
String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strInternalDesc As String

Set db = CurrentDb()

strSQL = "SELECT tblInternalNums.InternalNumDesc " & _
"FROM tblInternalNums INNER JOIN
tblMediaInternalNums " & _
"ON tblInternalNums.InternalNumID = " & _
"tblMediaInternalNums.InternalNumID " & _
"WHERE tblMediaInternalNums.MediaID=" & MediaID


Set rst = db.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
Do Until rst.EOF
strInternalDesc = strInternalDesc & "/" &
rst.Fields("InternalNumDesc")
rst.MoveNext
Loop
End If

If Len(strInternalDesc) > 0 Then
strInternalDesc = Right(strInternalDesc, Len
(strInternalDesc) - 1)
End If
fGetInternalNumDesc = strInternalDesc

End Function

Hmm. It should speed it up some if you replace this:

Set db = CurrentDb()

with this:

Set db = DBEngine.Workspaces(0)(0)

 

[Sergey Poberezovskiy]

I would suggest a few things to speed up the query:
1. Create query with the following SQL:

PARAMETERS [MEDIA ID] Long;
SELECT in.InternalNumDesc
FROM tblInternalNums As in INNER JOIN tblMediaInternalNums
As mn ON in.InternalNumID = mn.InternalNumID
WHERE mn.MediaID = [MEDIA ID]

Let's call it "qryMediaNumbers". Run it with a number and
save it.

2. In your function code:

Dim qd as DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qryMediaNumbers")
With qd.OpenRecordset(dbOpenForwardOnly)
Do While Not .EOF
strInternalDesc = strInternalDesc & "/" & .Fields
(0)
.MoveNext
Loop
End
If Len(strInternalDesc) > 0 Then
strInternalDesc = Right(strInternalDesc, Len
(strInternalDesc) - 1)
End If

First, you pre-compile the query, then you use the fastest
recordset open method. That should speed things up a
little.

But I suspect that the root of the problem could lie
elsewhere. Think of the following questions:
- Are your tables local to the database?
- How many records (roughly) in those tables?
- Are they indexed on linked fields?

HTH