How to get back a value from SQL Scalar UDF in Access VBA, the most efficient way?

Hi Experts,

I have a SQL scalar function that accepts a few parameters and returns an int, and would like to check that results before saving each record, what is the most efficient method to accomplish that?

below is the function;
ALTER FUNCTION [dbo].[fnEmpWeeklyHoursSched] 
(
	@empid	AS	int,
	@Day	AS	datetime,
	@SchedID as int,
	@From as Datetime = null,
	@To as Datetime = null
)
RETURNS int
AS
BEGIN
	DECLARE @Result AS int = 0;
	
	if @SchedID = 0
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6); 
	else
	begin	   
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6
			and ID <> @SchedID); 
	SET @Result = @Result + dbo.fnMyDateDiff(@From, @To);
	end			

	RETURN @Result;
END

Open in new window


Thanks
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
The only way.
Create a passthrough query in Access that executes a stored procedure and returns a recordset.
The sproc needs to run your function and return a value
bfuchsAuthor Commented:
@Nick,

Wow,

That's way too complicate, I already got that to work directly calling the scalar function like the following.

SELECT PatientsEmployeesSchedule.ID, [Patients].[LastName] + ' ' +  [Patients].[FirstName] AS Pat, [Employeestbl].[LastName] + ' ' + [Employeestbl].[FirstName] AS Emp, PatientsEmployeesSchedule.Day, PatientsEmployeesSchedule.[From], PatientsEmployeesSchedule.[To],dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'Hours Sched'
FROM (Patients INNER JOIN PatientsEmployeesSchedule ON Patients.ID = PatientsEmployeesSchedule.PatientID) INNER JOIN Employeestbl ON PatientsEmployeesSchedule.EmployeeIDToBeApproved = Employeestbl.ID
ORDER BY PatientsEmployeesSchedule.Day;

Open in new window


Therefore I'm assuming changing the queries sql property by code and then using dlookup(FieldName,PassThroughQry) will do the job.

However this does not look to me as the most efficient method of accomplishing this...

Thanks,
Ben
Scott PletcherSenior DBACommented:
For ease of use and much greater efficiency, you need to make the main function an in-line table-valued function, and get rid of all the unnecessary date functions.  Just add the logic for fnMyDateDiff and fnFirstDayOfWeek into the itfv itself.  Scalar functions are relatively very slow in SQL Server, and thus should only be used when really necessary, not for simple utility tasks (as you would do in other languages).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nick67Commented:
I have a passthrough.
in SQL Server I have a scalar function that returns a string and takes two parameters
If I give the passthrough this SQL

use MyDatabase
select dbo.ufnCriteriaByPieceType(1, 2) AS Minimum


It returns the required string

So very likely, you can create a passthrough, and in VBA, through a QueryDef set its SQL to something like

Use YourDatabase
Select dbo.fnEmpWeeklyHoursSched(SomeEmpID,SomeDay,SomeSchedID,SomeFrom,SomeTo)


where you build the SomeXXX in code


Dim db as Database
dim rs as Recordset
Dim QDF as QueryDef
Dim HoursSched as Single
set db = CurrentDb
set QDF = db.QueryDefs("TheNameOfYourPassThrough")
QDF.SQL = "Use YourDatabase" & vbCrLf & "Select dbo.fnEmpWeeklyHoursSched(12345,'1-Nov-15','67890','15-Oct-15','15-Nov-15')
set rs = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)
HoursSched = rs!Result


Therefore I'm assuming changing the queries sql property by code
Yes, that's required
and then using dlookup(FieldName,PassThroughQry) will do the job.

DLookup is a dog.
You've already got the querydef open to change its SQL
Generate a recordset, and forget about DLookup
bfuchsAuthor Commented:
Hi Experts,

@Scott,
Can you please explain or (provide an example) how to create in-line table function?

@Nick,
Actually I was about to use an alternative function that works faster then dlookup, but perhaps in this case you right this may be a good approach here, will test it & let you know.

@All, I see you came up with ways to improve performance in both ends, great!

Thanks,
Ben
bfuchsAuthor Commented:
@Nick,

When the sql function does not return any records I get the attached error when using your suggestion, while dlookup (enclosed in nz() works fine, how can I get around this?

Thanks,
Ben
untitled.bmp
Anthony PerkinsCommented:
Can you please explain or (provide an example) how to create in-line table function?
I am sure that Scott will provide a much more elegant solution, however it should look something like this:
ALTER FUNCTION dbo.fnEmpWeeklyHoursSched2
    (
     @Day AS datetime,
     @SchedID AS int,
     @From AS Datetime = NULL,
     @To AS Datetime = NULL
	 )
RETURNS TABLE
AS
RETURN
    SELECT  EmployeeID,
            SUM(dbo.fnMyDateDiff([From], [To])) + dbo.fnMyDateDiff(@From, @To)
    FROM    PatientsEmployeesSchedule
    WHERE   [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day) + 6
            AND (ID <> @SchedID OR @SchedID = 0)
    GROUP BY EmployeeID;

Open in new window


That means you would need to change the query that calls this function to include it in the FROM clause.  If you can include that we can be more precise.

Word of caution:
You are still not out of the woods as you are sill calling three scalar functions and until you lose them you are not going to get good performance.  So to clarify by using an inline table-valued function you have reduced the number of calls by the number of rows in the calling query times the number of times it was called.  So if you only had one call to the scalar function and your query had 10,000 rows then in very broad terms you just saved yourself 10,000 calls.

I suspect a better approach than a function would involve using a temp table and then joining against this table.
bfuchsAuthor Commented:
@Anthony,

1- The following query I use to get the results of the SQL function, how should I modify to accommodate the in-line table function?

select dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'HoursSched from PatientsEmployeesSchedule where id = " & MyVar

Open in new window


Following are my other scalar functions, do you suggest I have them all in one place?

/****** Object:  UserDefinedFunction [dbo].[fnFirstDayOfWeek]    Script Date: 11/05/2015 15:30:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fnFirstDayOfWeek](@Day As datetime)
 returns datetime
 AS
 Begin

	DECLARE @Result datetime

    set @Result = @Day + 1 - DATEPART(weekday,@day) 
    RETURN @Result;
End 

----------------------------
/****** Object:  UserDefinedFunction [dbo].[fnMyDateDiff]    Script Date: 11/05/2015 15:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnMyDateDiff] 
(
	@dFrom	AS	datetime = NULL,
	@dTo	AS	datetime = NULL
)
RETURNS smallint
AS
BEGIN
	DECLARE @Result smallint

	IF @dFrom IS NULL OR @dTo IS NULL
		SET @Result = 0;
	ELSE IF @dFrom = @dTo
		SET @Result = 780;
	ELSE
	BEGIN
		SET @Result = ISNULL(DATEDIFF(n,@dFrom,@dTo),0);
		IF @Result < 0 SET @Result = 1440 + @Result;
	END;
	RETURN @Result;
END

Open in new window


Not sure what's the definition of in-line function, is that it does not include any variables and code logic? in that case I would not be able to cover the existing scalar function, as it has two ways of calculating the hours, one if ScheduleID id supplied and the other if no ScheduID is supplied (ScheduleID = 0) as shown above.
In addition how will I be able to include the logic of the 2 other function here?

Or perhaps the only difference is the returning a table instead of a value?

I suspect a better approach than a function would involve using a temp table and then joining against this table.
If that's the right way to go regarding performance, perhaps you can elaborate little more in this direction (as I'm fairly new in t-sql programming..)

Thanks,
Ben
bfuchsAuthor Commented:
@Scott, Anthony,

I did the following test in my front end that calls the SQL function 200 times in a loop, and compared both ways, the in-line function vs the sscalar function, and both took about 30 sec.

Public Sub testSQL()
    Dim qd As QueryDef, i As Long, z As Integer, sSql As String, rs As Recordset
    Debug.Print Now
    For i = 100000 To 100200
    Set db = CurrentDb
    Set qd = db.QueryDefs("TempPassThrough1Qry")

    'sSql = "select dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'HoursSched'"
    'sSql = sSql & " from PatientsEmployeesSchedule where id = " & i
    
    sSql = "Select [HoursSched] / 60 as 'HoursSched' from dbo.[fnEmpWeeklyHoursSched2] (151666,'11/9/15',2270921,'09:00:00.000','13:00:00.000')"
    qd.Sql = sSql
    z = Nz(tLookup("HoursSched", "TempPassThrough1Qry"), 0)
    'Set rs = qd.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    'z = Nz(rs(0), 0)
    'z = DLookup("HoursSched", "TempPassThrough1Qry")
    Next
    
    Debug.Print Now
    
End Sub

Open in new window


Is there any other way I can test to verify this will make a big difference in performance?

(The table in question contains over 1M records).

Thanks,
Ben
Nick67Commented:
When the sql function does not return any records
No, no, no.
Your function HAS to return a value -- its yours after all!
It should be structured to return a valid result, or a result that you can interpret as an error


ALTER FUNCTION [dbo].[fnEmpWeeklyHoursSched]
(
      @empid      AS      int,
      @Day      AS      datetime,
      @SchedID as int,
      @From as Datetime = null,
      @To as Datetime = null
)
RETURNS int


You've got it returning an SQL int (Access Long)
and you initialize it here
DECLARE @Result AS int = 0;

I suspect your logic error is here


      if @SchedID = 0
      SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
             WHERE EmployeeID = @empid
               AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6);
      else
      begin         
      SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
             WHERE EmployeeID = @empid
               AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6
                  and ID <> @SchedID);
      SET @Result = @Result + dbo.fnMyDateDiff(@From, @To);
      end


You aren't handling NULL cases -- and so null is propagating and getting out.
You need to decide what null means and how you want to handle it in your function.

And then it won't be a problem in Access :)
Scott PletcherSenior DBACommented:
Here's the equivalent fully in-line table-valued function.  


ALTER FUNCTION [dbo].[fnEmpWeeklyHoursSched]
(
      @empid      AS      int,
      @Day      AS      datetime,
      @SchedID as int,
      @From as Datetime = null,
      @To as Datetime = null
)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(MyDateDiff.[Days]) AS TotalHours
    FROM dbo.PatientsEmployeesSchedule
    CROSS APPLY (
        SELECT @Day + 1 - DATEPART(WEEKDAY, @day) AS [Day]
    ) AS FirstDayOfWeek
    CROSS APPLY (
        SELECT CASE
            WHEN @From IS NULL OR @To IS NULL
            THEN 0
            WHEN @From = @To
            THEN 780
            ELSE ISNULL(DATEDIFF(MINUTE,@From,@To),0) + CASE WHEN @From > @To THEN 1440 ELSE 0 END
          END AS [Days]
    ) AS MyDateDiff
    WHERE EmployeeID = @empid
      AND [Day] BETWEEN FirstDayOfWeek.[Day] AND FirstDayOfWeek.[Day] + 6
      AND (@SchedID = 0 OR ID <> @SchedID)
)
GO --end of function
bfuchsAuthor Commented:
@Nick,

Currently I use the following function replacement of Dlookup(), which I have downloaded somewhere a while back and this seems to work even faster than the one you suggested
set rs = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)
HoursSched = rs!Result

Open in new window


here is the code.

Function tLookup(pstrField As String, pstrTable As String, Optional pstrCriteria As String, Optional pdb As Database, Optional pLookupReset As tLookupReset = tLookupDoNothing) As Variant
    On Error GoTo tLookup_Err
'Exit Function
    ' Function  tLookup
    ' Purpose:  Replace DLookup, which is slow on attached tables
    '           For where you can't use TbtLookup() if there's more
    '           than one field in the criteria or field is not indexed.
    ' Created:  9 Jan 1996 T.Best
    ' Mod       1 Feb 1996 T.Best
    '   Error Trapping brought in line with this procurement system.

    ' Mod       13 Apr 1999 T.Best
    '   Lookups to ODBC datasource use the gdbSQL database object.

    ' Mod       14 Apr 1999 T.Best
    '   gdbSQL object no good if doing lookup on a local table, DOH!

    ' Mod       11 Jan 2002 G.Hughes
    '   Removed gdbSQL as it was slowing tLookup Down.!!!!!!!!!

    ' Mod       Unlogged
    '   Someone put gdbSQL back in

    ' Mod       27 Jan 2003 T. Best
    '   Optimise gdbSQL to use Pass-through, it wickedly fast

    ' mod       13 Mar 2003
    '   Taken out gdbSQL for redistribution and replaced
    '   the DbEngine with CurrentDB to avoid the now well
    '   documented (in CDMA) DbEngine reference bug.
    '   Added tLookupReset Parameter which does the following
    '   tLookupDoNothing    Do nothing
    '   tLookupRefreshDb    Refreshes collections on the db
    '   tLookupCloseDb      Sets the db to nothing
    '   Also added a db parameter so programmer can call it using
    '   their own db variable, which may be something they opened
    '   elsewhere (Idea by D.Fenton in CDMA).

    Static dbLookup As DAO.Database
    Dim rstLookup As DAO.Recordset
    Dim varvalue As Variant
    Dim strSQL As String

    ' if calling function sends a db then we'll use that
    If Not pdb Is Nothing Then
        Set dbLookup = pdb
    Else
        ' If our db vari is not initialised or the calling
        ' process wants the db objects refreshed then we'll
        ' set the db var using CurrentDb()
        If dbLookup Is Nothing Or pLookupReset = tLookupRefreshDb Then
            If Not dbLookup Is Nothing Then
                Set dbLookup = Nothing
            End If
            Set dbLookup = CurrentDb()
        End If
    End If


    ' If no criteria specified then we don't even want to get as far
    ' as putting the word "where" in there
    If Len(pstrCriteria) = 0 Then
        strSQL = "Select " & pstrField & " From " & pstrTable
    Else
        ' handle those instances where you call tLookup using a field
        ' on a form but can't be bothered to check whether it's null
        ' first before calling, e.g. =tLookup("col1","table","col2=" & txtWhatever)
        ' if txtWhatever was null it would cause an error, this way if there's
        ' nothing after the "=" sign then we assume it was null so we'll make
        ' it look for one.
        ' You may want to handle this differently and avoid looking up
        ' data where the criteria field is null and just always return a
        ' null in which case you'd need to add code to avoid doing the
        ' lookup altogether or just change the criteria to " = Null" as
        ' nothing will ever match with " = Null" so the function would
        ' return null.
        If Right(RTrim(pstrCriteria), 1) = "=" Then
            pstrCriteria = RTrim(pstrCriteria)
            pstrCriteria = Left(pstrCriteria, Len(pstrCriteria) - 1) & " is Null"
        End If

        ' build our SQL string
        strSQL = "Select " & pstrField & " From " & pstrTable & " Where " & pstrCriteria
    End If

    ' now open a recordset based on our SQL
    Set rstLookup = dbLookup.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)

    ' chekc if we returned anything at all
    If Not rstLookup.BOF Then
        ' return the value returned in the query
        varvalue = rstLookup(0)
    Else
        ' no records matched, return a null
        varvalue = Null
    End If
    tLookup = varvalue

tLookup_Exit:
    On Error Resume Next
    rstLookup.Close
    Set rstLookup = Nothing
    Exit Function

tLookup_Err:
    Select Case Err
        Case 3061
            ' Error 3061 - Too Few Parameters - Expected x, you know those programmers
            ' should really parse out those form object references for themselves but
            ' we can try to retrieve the situation here by evaluating any parameters
            ' we find in the SQL string.
            tLookup = tLookupParam(strSQL, dbLookup)
        Case Else
            MsgBox Err.Description, 16, "Error " & Err & " in tLookup() on table " & pstrTable & vbCr & vbCr & "SQL=" & strSQL
    End Select
    Resume tLookup_Exit
    Resume
End Function

Open in new window


Re the nulls, I added the following at the end of the SQL function
if @Result is null Set @result = 0	

Open in new window

let me know if this was the intended.

Thanks,
Ben
bfuchsAuthor Commented:
@Scott, Anthony,

Believe it or not, I just ran the following code to test the time it takes the in-line function (using the latest of Scott) vs the original 3 scalar functions, and this was the results

-tested multiple times-

Scalar - 12 Sec.
In-Line - 59 Sec.

Here is the test function  
Public Sub testSQL()
    Dim qd As QueryDef, i As Long, z As Integer, sSql As String, rs As Recordset
    Debug.Print Now
    For i = 100000 To 100400
    Set db = CurrentDb
    Set qd = db.QueryDefs("TempPassThrough1Qry")

    sSql = "select dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'HoursSched'"
    sSql = sSql & " from PatientsEmployeesSchedule where id = " & i

'    sSql = "Select [HoursSched] / 60 as 'HoursSched' from dbo.[fnEmpWeeklyHoursSchedscott] (151666,'11/9/15',2270921,'09:00:00.000','13:00:00.000')"
'    qd.Sql = sSql
    z = Nz(tLookup("HoursSched", "TempPassThrough1Qry"), 0)
   
    Next

    Debug.Print Now

End Sub

Open in new window


Therefore I guess we still need some tweaks here to make this perfect..

Thanks,
Ben
Nick67Commented:
Well, we are far afield from what I understood was your original intention
I have a SQL scalar function that accepts a few parameters and returns an int, and would like to check that results before saving each record

Your function is doing pretty much what my code does
Set rstLookup = dbLookup.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
set rs = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Different opening options can make some difference on multiple repeated options -- on a single operation the differences are likely in the milliseconds.

if @Result is null Set @result = 0
let me know if this was the intended.

That depends upon you.  If null and zero are all the same to you, then that can be okay.  But maybe you want to be able to tell the difference between when the function honestly returned zero and when it returned junk because the input parameters were haywire.  Maybe then you'd want it to return something different, like -1, that you trap for to detect why the function returned nonsense.  It all depends upon the logic you are using for this function.

I don't think your test is really the right methodology.
You're looping.
That's always slow, and likely the slowness is on the Access end.

And I don't understand the relevance.  If you are checking a single value before committing a change, milliseconds aren't going to be relevant.  If you are batch committing a whole bunch of records, the RBAR isn't the way you want to get that done.  So I'm confused.
bfuchsAuthor Commented:
@Nick,
Well, we are far afield from what I understood was your original intention
Actually my main concern was if that is the right approach, to reference a query object and modify its sql property, then run it to get the results, or perhaps there is a way to get connected directly which will probably perform faster..
for example I saw on google something like this in ADO
Dim rst As ADODB.Recordset
Set rst = Connection.Execute("SELECT dbo.MyFunction('" & Me.field & "')")

Open in new window

I don't think your test is really the right methodology.
You're looping.
What other option would you recommend to do for testing in order to determine which function would perform better?
If you are checking a single value before committing a change, milliseconds aren't going to be relevant
Perhaps you right, it does not make a big difference here, just was looking for a reason why choose that option here over the current tlookup function which I use in the rest of the application.

Thanks,
Ben
Anthony PerkinsCommented:
What other option would you recommend to do for testing in order to determine which function would perform better?
Test both queries from SSMS only.  Take MS Access out of the equation.
Nick67Commented:
for example I saw on google something like this in ADO
I've seen the same thing.
I'd post the link but the Moderators would have a conniption.
In the MS Access discussion group, I have a discussion about that.
At times such ADO can be up to 10 times faster than DAO recordset code.
The code below executes the same function as my post in ID: 41199294

I store the connection string in a table and on a hidden form in my app.
That way, I don't have to crawl the code to find and change it if required.
With this code, you can build up the SQL string directly in VBA, instead of altering the .SQL property of a passthrough.

Dim dbCon As New ADODB.Connection
Dim rst As ADODB.Recordset
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value
dbCon.Open
Set rst = dbCon.Execute("SELECT dbo.ufnCriteriaByPieceType(1,2) as Minimum")
MsgBox rst!minimum
dbCon.Close
Set dbCon = Nothing
Set rst = Nothing

Open in new window


But , by doing so, you still miss out on one of the server's abilities to optimize.
At the beginning I said
The only way.
Create a passthrough query in Access that executes a stored procedure and returns a recordset.
The sproc needs to run your function and return a value

Clearly, the first sentence was incorrect.
But IF you create a parameterized stored procedure, the server will optimize its running.
IF you craft ad-hoc SQL and run it -- as we are doing now -- it cannot do that nearly so well.

Which is why I suggested that you should have a stored procedure on your server that calls your function.  Maybe the function will be well-optimized, too.
@ScottPletcher and @acperkins can speak to that better than I can.
I am more on the Access end, they are more on the server end of things.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Anthony,
Test both queries from SSMS only.  Take MS Access out of the equation.
Perhaps you right when we only need to figure out which option will perform better from SQL perspective, however since I will be calling this from my Access app, I guess we need to test that in that scenario as well..(Let say for example, MS access has a bug that it cannot access an in-line table function directly, and therefore it calls another routine that communicates with that function..just enough to slow down the process..only theory though).

@Nick,
Will test your latest & keep you posted.

Thanks,
Ben
Scott PletcherSenior DBACommented:
The idea with an in-line table-valued function is that it would be called, using CROSS APPLY, directly as part of the table SELECT, not called multiple times standalone.  That is where the huge performance gain comes in from using a itvf.
bfuchsAuthor Commented:
Hi Experts,

You will not beleive, but after changing the code to what Nick suggested
Public Sub testSQLNick()

Dim dbCon As New ADODB.Connection
Dim rst As ADODB.Recordset
    Dim qd As QueryDef, i As Long, z As Integer, sSql As String, rs As Recordset
    Debug.Print Now
    For i = 100000 To 100400
    'sSql = "select dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'HoursSched'"
    'sSql = sSql & " from PatientsEmployeesSchedule where id = " & i
    sSql = "Select [HoursSched] / 60 as 'HoursSched' from dbo.[fnEmpWeeklyHoursSchedscott] (151666,'11/9/15',2270921,'09:00:00.000','13:00:00.000')"
dbCon.ConnectionString = "MyCredentials"
dbCon.Open
Set rst = dbCon.Execute(sSql)
If rst.RecordCount > 0 Then
    z = Nz(rst!HoursSched)
End If
dbCon.Close
Next
Set dbCon = Nothing
Set rst = Nothing
Debug.Print Now
End Sub

Open in new window


The results are as follows

Scalar - 4 Sec.
In-Line - 1Sec.!!!

Finally we got this to work as desired!!

What are your toughts?

Thanks,
Ben
Nick67Commented:
At times such ADO can be up to 10 times faster than DAO recordset code.

Scalar - 12 Sec.
 In-Line - 59 Sec.
to
Scalar - 4 Sec.
 In-Line - 1Sec.!!!

3x better on scalar and 59x better on in-line.
Not bad!
It knocked my socks off the first time I saw it too.
It's a tool to keep in the shed.
Anytime you need read-only data, and DAO takes a while to deliver it, ask yourself "Can I flange this up as ADO and how will it perform?"

The answer is sometimes delightful.
And your test function isn't even optimized.
You set up the connection and keep it open, only closing the recordsets

Public Sub testSQLNick()

Dim dbCon As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim qd As QueryDef, i As Long, z As Integer, sSql As String, rs As Recordset
dbCon.ConnectionString = "MyCredentials"
dbCon.Open
Debug.Print Now
For i = 100000 To 100400
    sSql = "Select [HoursSched] / 60 as 'HoursSched' from dbo.[fnEmpWeeklyHoursSchedscott] (151666,'11/9/15',2270921,'09:00:00.000','13:00:00.000')"
    Set rst = dbCon.Execute(sSql)
    If rst.RecordCount > 0 Then
        z = Nz(rst!HoursSched)
    End If
    rst.close
Next
dbCon.Close
Set dbCon = Nothing
Set rst = Nothing
Debug.Print Now
End Sub

Open in new window


File this one under 'you learn something new every day' :)
bfuchsAuthor Commented:
Thanks experts,
You all greatly contributed to the success of this project!
bfuchsAuthor Commented:
Hi Experts,

You did great regarding performance, perhaps you can help me with the following.

@Scott, Anthony
Testing Scott's function with the following
Select [HoursSched] / 60 as 'HoursSched' from dbo.[fnEmpWeeklyHoursSchedscott]
 (92001,'11/13/15',2445045,'11:00:01 PM','8:00:01 AM')

Open in new window

and getting 27, while the correct answer should be 41
sample data attached.

@Nick,
Yesterday I got an error when there were no records, when checked for RecordCount property I saw it was -1, therefore I added the check If rst.RecordCount > 0 Then, however today I see that this property is -1 although function returned a record, so what is the correct way of handling this?

BTW, I follow your suggestion of adding "if @Result is null Set @result = 0" to my original function and still had instances with no results..

Thanks,
Ben
11-11-15.csv
Untitled.png
Nick67Commented:
With an ADO recordset, .RecordCount doesn't come up with a correct number unless you force full population of the recordset.
I do that with DAO, too, just to be sure it tells me no lies, but with ADO it's a necessity to force the issue if you really want to use .RecordCount for something.

Make sure you have
rst.MoveLast
rst.MoveFirst

in place before you try to use rst.RecordCount

BTW, I follow your suggestion of adding "if @Result is null Set @result = 0" to my original function and still had instances with no results..


I am more on the Access side of things, and troubleshooting your UDF is beyond my skillset.
You clearly want that UDF to ALWAYS return a value, and sometimes it doesn't.
That's a question in itself that you may want to ask in a new Q to get help from folks that bang T-SQL around a lot more than I do to help you troubleshoot why your UDF still returns null on occasion.
Debugging T-SQL is not my forte.
bfuchsAuthor Commented:
@Nick,

First thanks for replying.

I was about to test if:

1- in case of no records, rst.MoveFirst  would not result in an error.
2- adding the MoveLast/MoveFirst would not wipe out the benefit of performance we finally got by this method.

However I got the attached error while executing it.

Perhaps we are missing something here?

Thanks,
Ben
untitled.bmp
Nick67Commented:
Perhaps we are missing something here?
Maybe.
There are many cursor types.
We might be getting an adOpenForwardOnly which would explain the error.
The -1 for .RecordCount would also be explained
More detail here:
http://www.functionx.com/vbaccess/Lesson26.htm

•A cursor is referred to as forward-only if it allows you to move forward through the records. Here is how it works. Suppose that you create a Recordset object and specify its records. Suppose that, while using the record set, you get to a record that was set as a starting. Also, suppose that either you only or other people besides you are working on the same record. If you make a change on the current record, the other people will be notified. If other people make a change on the current record, you also would know. After using that record, you move to the next. With the forward-only cursor, you cannot move back to a record you left already. This means that, even if you are still working on the record set, if there are changes performed on a record you left behind (for example, if another person who is working on the same record changes something on a record that you passed already), you cannot know and you cannot find out because you cannot go back to a record left behind. If this becomes a necessity, you can close the recordset and re-open it. A forward-only cursor is appropriate if you don't need to navigate back and forth among the records of a record set. Because of the way it works, if you access the RecordCount property of a forward-only cursor, it would produce -1.
In ADO, this type or cursor is represented by the adOpenForwardOnly constant

We might need
rst.CursorType = adOpenStatic
after
dbCon.Open
bfuchsAuthor Commented:
Tried that but, wherever I put it gives me an error, before the dbCon.Open it tells me Object or with..not set.
After the .Open I get, it cannot do after connection is open.

However I see the dbCon.Execute command has one of its arguments "RecordsAffected", any idea how to make use of that?

If you think this may be kind of complicate stuff, I will post another question.

Thanks,
Ben
bfuchsAuthor Commented:
Just tried the below and so far seems to work

If Not rst.BOF And Not rst.EOF Then

Open in new window


Thanks,
Ben
Nick67Commented:
It is not my forte.
You may be best to post new questions.

Nick67
Nick67Commented:
I would like to say thanks, though.
The one app I have uses a scalar UDF to concatenate multiple rows and columns of normalized data together as a string.

Today I had need of that string in the results of a query.
So I built a couple of Public Functions

Option Compare Database
Option Explicit


Public Function TheMin(PieceID As Long)
Dim dbCon As New ADODB.Connection
Dim rst As ADODB.Recordset
dbCon.ConnectionString = "TheString"
dbCon.Open
Set rst = dbCon.Execute("SELECT dbo.ufnCriteriaByPieceType(" & PieceID & ",2) as Minimum")
TheMin = rst!minimum
dbCon.Close
Set dbCon = Nothing
Set rst = Nothing
End Function

Public Function TheNom(PieceID As Long)
Dim dbCon As New ADODB.Connection
Dim rst As ADODB.Recordset
dbCon.ConnectionString = "theString"
dbCon.Open
Set rst = dbCon.Execute("SELECT dbo.ufnCriteriaByPieceType(" & PieceID & ",1) as Nominal")
TheNom = rst!nominal
dbCon.Close
Set dbCon = Nothing
Set rst = Nothing
End Function

Open in new window


Then, in the MS Access query I can call them like so
nom: theNom([pieceID])
min: theMin([pieceID])

That works like a hot damn!
And I may, or may not have thought to do so without your Q

Nick67
Anthony PerkinsCommented:
The preferred, fastest and default method is a fire hose (forward only, read only) cursor.  And yes, forward only means that you cannot use MoveFirst and the RecordCount is not known.  That is by design and as it should be.  So if you cannot use that for whatever reason and are prepared to take the performance hit, then make sure to set the parameters for the Recordset's Open method.  Here is the syntax for the Open method:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The two parameters you should focus on are the CursorType and the LockType.  Pick carefully.
Nick67Commented:
@acperkins

@bfuchs has his UDF occasionally returning either no records or null, which blows up his VBA.
Normally with DAO you'd test for an empty recordset with .RecordCount.
Clearly not a good idea with what ADO code we've posted.

Can you look at his UDF and tell why it doesn't always return an integer?

Nick67
bfuchsAuthor Commented:
@Nick,
As posted above (ID: 41235909), it seems like the FE problem was taken care off.

However, If someone can figure out why the below function does not always return a value is welcome:)
ALTER FUNCTION [dbo].[fnEmpWeeklyHoursSched] 
(
	@empid	AS	int,
	@Day	AS	datetime,
	@SchedID as int,
	@From as Datetime = null,
	@To as Datetime = null
)
RETURNS int
AS
BEGIN
	DECLARE @Result AS int = 0;
	
	if @SchedID = 0
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6); 
	else
	begin	   
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6
			and ID <> @SchedID); 
	SET @Result = @Result + dbo.fnMyDateDiff(@From, @To);
	end		
	
	if @Result is null Set @result = 0	

	RETURN @Result;
END

Open in new window


Thanks,
Ben
Anthony PerkinsCommented:
No idea why you are not getting any rows back, but it the UDF cannot have anything to do with it. Rather it should be this code:
SELECT  PatientsEmployeesSchedule.ID,
        [Patients].[LastName] + ' ' + [Patients].[FirstName] AS Pat,
        [Employeestbl].[LastName] + ' ' + [Employeestbl].[FirstName] AS Emp,
        PatientsEmployeesSchedule.Day,
        PatientsEmployeesSchedule.[From],
        PatientsEmployeesSchedule.[To],
        dbo.[fnEmpWeeklyHoursSched](EmployeeID, Day, PatientsEmployeesSchedule.[ID], PatientsEmployeesSchedule.[from], PatientsEmployeesSchedule.[to]) / 60 AS 'Hours Sched'
FROM    (Patients
         INNER JOIN PatientsEmployeesSchedule ON Patients.ID = PatientsEmployeesSchedule.PatientID
        )
        INNER JOIN Employeestbl ON PatientsEmployeesSchedule.EmployeeIDToBeApproved = Employeestbl.ID
ORDER BY PatientsEmployeesSchedule.Day;

Open in new window

So I would suspect you are missing a row in one of those tables.  A quick LEFT JOIN should find the culprit.

But in any case you can certainly check for that situation without having to check the row count, just a simple if NOT rs.EOF should do the job.
bfuchsAuthor Commented:
@Anthony,
You right, the problem was not with the function, it was with the way I called the function as posted
    sSql = "select dbo.[fnEmpWeeklyHoursSched](EmployeeID,Day,PatientsEmployeesSchedule.[id],PatientsEmployeesSchedule.[from],patientsemployeesschedule.[to])/60 as 'HoursSched'"
    sSql = sSql & " from PatientsEmployeesSchedule where id = " & i

Open in new window

which I guess if there is no record matching criteria in the underlying table, I will not get any records in this recordset.

just a simple if NOT rs.EOF should do the job
Yes, that is what I was referring in my last post.

Thanks,
Ben
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.