Thread: [SOLVED] Speed this up at all?
View Single Post
Old 07-18-2017, 02:51 AM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 10,794
Thanks: 58
Thanked 1,304 Times in 1,229 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Speed this up at all?

Quote:
Originally Posted by Gasman View Post
Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.
Stepping through recordsets is always going to be slower than a query.

Applying a function that opens a recordset for each employee will be very slow. Using a Domain function will still be slow. Try to do it by joining the tables so the engine can do its group processing. Remember the SQL View can support joins that can't be done in the Design View.

Wherever it is done, applying a function, any function, to every record before the Select can be applied will slow a query.
Quote:
Code:
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)

strCriteria = "EmployeeID = " & pEmployeeID & " AND YEAR([StartDate])= " & Year(pDate) & " AND YEAR([EndDate])= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"


'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"
Galaxiom is offline   Reply With Quote