Function performance different when run from an Access form

I have a SQL stored procedure that I call using a VBA function which populates two MS Access form fields. The whole purpose in using the stored procedure call is that the select query, when run in Access takes 8-10 secs to return values. The stored procedure= when run in SQL Mgt Studio retruns a val开发者_运维知识库ue in < 1sec. The function returns values in the Visual Basic "Immediate" window in about 1-2 seconds. But, when I call the function (which calls the stored procedure) it takes 8-10 seconds to return values to the Access form- defeating my whole purpose of using the stored procedure. Here is the function:

Option Compare Database
Option Explicit

Public strTrack As String
Public strBar As String
Public strProf As String
Public strFac As String

Public Function SP_Barcode(MyParam As String)
    Dim mydb As DAO.Database
    Dim qdf As QueryDef
    Dim sqlx As String
    Dim rs As DAO.Recordset
    strTrack = ""
    strBar = ""
    strProf = ""
    strFac = ""
    Set mydb = CurrentDb()
    Set qdf = mydb.CreateQueryDef("")
    sqlx = "exec dbo.SPWise_WasteManifestInfoByBarcode '" & MyParam & "'"
    qdf.Connect = "ODBC;Description=IMDB_Dev;DRIVER=SQLServer;SERVER=server\dev;UID=mmmmmm;Trusted_Connection=Yes;DATABASE=IMDB_Dev"
    qdf.SQL = sqlx
    qdf.ReturnsRecords = True
    Set rs = qdf.OpenRecordset()
    If Not (rs.EOF And rs.BOF) Then
        strTrack = rs.Fields(0)
        strBar = rs.Fields(1)
        strProf = rs.Fields(2)
        strFac = rs.Fields(3)
    Else
        Exit Function
    End If
    Debug.Print strTrack, strBar, strProf, strFac
    rs.Close
    Set mydb = Nothing
    Set qdf = Nothing
End Function

Is there any reason I would have poor performance when running the function from an Access form?

Thanks!

It sounds like the stored proc is working fine if you can call the function, SP_Barcode, in the VBA immediate window and it takes 2 seconds to return values.

There is some overhead when calling a stored proc from Access, like translation (Access to ODBC to SQL Server and back again) and creating a database connection. But it sounds like it isn't too bad if it runs about the same speed from the immediate window in VBA as it does from SQL server.

A few obvious things to check, first is the form unbound or bound to a datasource, i.e. is it making more connections to local and or SQL server tables/queries/stored procs that could be the bottleneck? If you have subforms check their bindings as well. When you open the form is your VBA window open when you run the benchmarks? Close it. It will speed things up.

Is this a Single Record form or a multiple record form? It could be calling this more than 1 time to populate a multiple record form. This would increase the number of connections made to the SQL db.

Are you doing the function call from a field or a load event? If it is being called on the fields then it may be called multiple times which could be creating multiple connections.

If it is a simple form with no other datasource you could build an existing passthrough query and then modify SQL param from the parent form (I'm assuming this is a front-end access database on each individual's PC and not a shared ADP, MDB, accessed by multi users). If it is still slow then it isn't your function.

Create the Passthrough Query like "WasteMainfestInfo_Passthru" with a prefilled in parameter and defined connection string via properties window and save it:

EXEC [dbo].[SPWise_WasteManifestInfoByBarcode ] @MyParmName = N'TestStringValue';

Set the form's bindings to WasteMainfestInfo_Passthru

In the parent form's event that opens this form do the following:

Set mydb = CurrentDb()
Set qdf = mydb.QueryDefs("WasteMainfestInfo_Passthru")
sqlx = "exec [dbo].[SPWise_WasteManifestInfoByBarcode] @MyParmName = N'" & MyParam & "';" 
qdf.SQL = sqlx    

DoCmd.OpenForm "WasteMainfestInfo_Passthru", acNormal
....
Set qdf = Nothing
Set mydb = Nothing

Your code looks okay so I really don't think it is the problem. I would add a With and EndWith for the qdf object but that won't increase performance by much.