UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recorset Speed Issue, Access 2016    
 
   
ordnance1
post Jan 2 2017, 11:56 PM
Post#1



Posts: 477
Joined: 7-May 11



I need to create a recordset from a query (I realize a from a table is faster but not an option). When I create my recordset from a table the underlying code took about 3 seconds to execute. Using a query as the basis for the recordset the underlying code takes about 16 seconds.

Is there anyway to speedup

CODE
'   This block of code creates a recordset that contains the number of time-off
'   requests for the specified month.

    Dim frm As Form, strSQL As String, rst As DAO.Recordset, db As DAO.Database
    Dim intCount As Integer

'   Make a shortcut reference to the specified month's subform
        Set frm = Forms![frm_Navigation]![NavigationSubform].Controls("cal" & Format(intMonth, "00")).Form
'   Create a recordset with the count of time-off requests for the specified month
        strSQL = "SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
                 "FROM qry_Filtered_DaysOff GROUP BY RequestDate " & _
                 "HAVING Month([RequestDate]) = " & intMonth
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)
        If rst.RecordCount > 0 Then rst.MoveFirst
'   Fill in the month name at the top of the monthly calendar
        frm.Controls("txtMonthName") = DateSerial(intYear, intMonth, 1)


Here is the complete block of code:

CODE
Public Sub FormatCalendar(intMonth As Integer, intYear As Integer)

'   This subroutine started out as a simple routine to draw the correct squares on a popup
'   calendar form written by Allen Browne.  Now it includes code to format the squares based
'   on the number of time off requests made for the day associated with each square.

'   Variables:
    Dim dtStartDate As Date     'First of month
    Dim iDays As Integer        'Days in month
    Dim iOffset As Integer      'Offset to first label for month.
    Dim i As Integer            'Loop controller.
    Dim iDay As Integer         'Day under consideration.
    Dim bShow As Boolean        'Show the label

'============================================================================
=====================
'   This block of code creates a recordset that contains the number of time-off
'   requests for the specified month.

    Dim frm As Form, strSQL As String, rst As DAO.Recordset, db As DAO.Database
    Dim intCount As Integer

'   Make a shortcut reference to the specified month's subform
        Set frm = Forms![frm_Navigation]![NavigationSubform].Controls("cal" & Format(intMonth, "00")).Form
'   Create a recordset with the count of time-off requests for the specified month
        strSQL = "SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
                 "FROM qry_Filtered_DaysOff GROUP BY RequestDate " & _
                 "HAVING Month([RequestDate]) = " & intMonth
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)
        If rst.RecordCount > 0 Then rst.MoveFirst
'   Fill in the month name at the top of the monthly calendar
        frm.Controls("txtMonthName") = DateSerial(intYear, intMonth, 1)
'============================================================================
=====================

'   Figure out which squares to hide/show, and what day number to place in the visible ones
        dtStartDate = DateSerial(intYear, intMonth, 1) 'First of month
        iDays = Day(DateSerial(intYear, intMonth + 1, 0)) 'Days in month.
        iOffset = Weekday(dtStartDate, vbSunday) - 2    'Offset to first label for month.

'   Hide the circle highlight by default (later it will be made visible only on the
'   month that holds the currently selected date)
        frm.Controls("lblHighlight").Visible = False
    
        For i = 0 To 41
            With frm.Controls("lblDay" & Format(i, "00"))
                iDay = i - iOffset
                bShow = ((iDay > 0) And (iDay <= iDays))
                If .Visible <> bShow Then
                    .Visible = bShow
                End If

'============================================================================
=====================
'    This block of code was added by UtterAccess.com user doctor9 to handle formatting each
'    day, based on the number of time off requests for that day.
        If Forms![frm_Navigation]![txt_CalTest] < 3 Then

    'Valid day for this month?
        If iDay > 0 And iDay <= Day(DateSerial(intYear, intMonth + 1, 0)) Then
    'Is this a holiday?  Make the box dark grey and skip checking time off requests
        If DCount("LineID", "tbl_Holidays", _
        "HolidayDate=#" & DateSerial(intYear, intMonth, iDay) & "#") > 0 Then

    'White text on Black
                .ForeColor = vbWhite
                .BackColor = vbBlack
            Else
'                   Not a holiday - format the label based on how many time off requests exist for that day
                intCount = 0
                If rst.RecordCount > 0 Then
                    rst.FindFirst "[RequestDate]=#" & DateSerial(intYear, intMonth, iDay) & "#"
                    If rst.NoMatch = False Then
                        intCount = rst!CountOfAbsenceID
                    End If
                End If


                Select Case intCount
'                Case Is < DLookup("Slots", "tbl_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#") 'Open slots remaining
                Case Is < DLookup("Slots", "tbl_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#") 'Open slots remaining

                    .BackColor = vbGreen
                    .ForeColor = vbBlack
                Case Is >= DLookup("Slots", "tbl_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#")  'No open slots, only next in line available
                    .BackColor = vbRed  'Red
                    .ForeColor = vbYellow
                End Select
            End If
        End If
    End If


    If Forms![frm_Navigation]![txt_CalTest] = 3 Then

'           Valid day for this month?
        If iDay > 0 And iDay <= Day(DateSerial(intYear, intMonth + 1, 0)) Then
'               Is this a holiday?  Make the box dark grey and skip checking time off requests
            If DCount("LineID", "tbl_Holidays", _
                        "HolidayDate=#" & DateSerial(intYear, intMonth, iDay) & "#") > 0 Then

'                   White text on Black
                .ForeColor = vbWhite
                .BackColor = vbBlack
            Else
'                   Not a holiday - format the label based on how many time off requests exist for that day
                intCount = 0
                If rst.RecordCount > 0 Then
                    rst.FindFirst "[RequestDate]=#" & DateSerial(intYear, intMonth, iDay) & "#"
                    If rst.NoMatch = False Then
                        intCount = rst!CountOfRequestID
                    End If
                End If

                Select Case DLookup("Rank", "qry_MyRequests_Ranking", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#")
                Case Is = 0
                    .BackColor = vbWhite
                    .ForeColor = vbBlack
                Case Is = 1
                    .BackColor = vbGreen
                    .ForeColor = vbBlack
                Case Is = 2
                    .BackColor = vbRed  'Red
                    .ForeColor = vbYellow
                End Select

            End If
        End If
    End If

If Forms![frm_Navigation]![txt_CalTest] = 4 Then

    '           Valid day for this month?
                If iDay > 0 And iDay <= Day(DateSerial(intYear, intMonth + 1, 0)) Then
    '               Is this a holiday?  Make the box dark grey and skip checking time off requests
                    If DCount("LineID", "tbl_Holidays", _
                                "HolidayDate=#" & DateSerial(intYear, intMonth, iDay) & "#") > 0 Then

    '                   White text on Black
                        .ForeColor = vbBlack
                        .BackColor = vbWhite
                    Else
    '                   Not a holiday - format the label based on how many time off requests exist for that day
                        intCount = 0
                        If rst.RecordCount > 0 Then
                            rst.FindFirst "[RequestDate]=#" & DateSerial(intYear, intMonth, iDay) & "#"
                            If rst.NoMatch = False Then
                                intCount = rst!CountOfRequestID
                            End If
                        End If

                        Select Case DLookup("Rank", "qry_Edit_Requests_Ranking", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#")
                        Case Is = 0
                            .BackColor = vbWhite
                            .ForeColor = vbBlack
                        Case Is = 1
                            .BackColor = vbGreen
                            .ForeColor = vbBlack
                        Case Is = 2
                            .BackColor = vbGreen  'Red
                            .ForeColor = vbBlack
                        End Select

                    End If
                End If
            End If

'           If this control represents the current date displayed in the textbox, highlight this control
            If intYear = Year(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) And intMonth = Month(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) And iDay = Day(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) Then
'               Show the highlight circle
                frm.lblHighlight.Visible = True
                Const lngcVOffset As Long = -83
                Const lngcHOffset As Long = -21
'               Move the highlight to circle the label they clicked on
                frm.lblHighlight.Left = frm.Controls(.Name).Left + lngcHOffset
                frm.lblHighlight.Top = frm.Controls(.Name).Top + lngcVOffset
            End If
'============================================================================
=====================

            If (bShow) And (.Caption <> iDay) Then
                .Caption = iDay
            End If
        End With
    Next

'   Cleanup
    Set frm = Nothing
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing

End Sub
Go to the top of the page
 
TheSmileyCoder
post Jan 3 2017, 03:23 AM
Post#2


UtterAccess VIP
Posts: 1,499
Joined: 19-January 12
From: Denmark, Copenhagen


There are 3 fundamental issues with your SQL.

Having vs Where:
Your SQL is using the having clause as opposed to the where clause. The difference between the 2 is like this: With a Where clause, you initially select the records you want, THEN perform the group. With the having clause, you first create ALL the groups, and THEN select the group you want. So that is inevitable going to be slower.

Months without years:
As soon as your database has run for a year, you will start getting weird results. Why? Well because the records from January of LAST year will start to pop up in your resultset. We need the year as well, but read on in the next point

The other is your use of a function in the having/where clause.
Before the database can evaluate which records to include, it must call the Months function on ALL records. That also means it cannot use any index (You should btw, have an index on your RequestDate) to speed up performance. If we instead use dates as delimiters then we can fully utilize the index, and don't have to call the Months function on each and every record (Have you heard the term RBAR before? Row By Agonizing Row, RBAR is a great enemy of SQL performance, and should be avoided if at all possible)

So how to rewrite the SQL? Well first consider that a month has a start and an end date, and realize we might as well select everything in between those two.
CODE
Dim StartDate as Date
Dim EndDate as date
StartDate=DateSerial(YourYear,intMonth,1) 'First date of the month
EndDate=dateadd("m",1,StartDate)


Finally the SQL
CODE
  strSQL = "SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
                 "FROM qry_Filtered_DaysOff " & _
                 " Where [RequestDate] >=" & Format(StartDate, "#mm\/dd\/yyyy#") & " AND RequestDate <" Format(EndDate, "#mm\/dd\/yyyy#") & _
                 " GROUP BY RequestDate "

Note how we include the startdate with the Greater than or Equal, but do not include the enddate.

Good luck. Id like to hear how long the new query will take to run.

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
LPurvis
post Jan 3 2017, 08:58 AM
Post#3


UtterAccess Editor
Posts: 16,058
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

I agree with everything Anders has to say there, regarding improving your query.
The reworking of the criteria is vital - and it alone will be the key factor in changing your performance.

Just to mention that while I entirely agree that your scalar (non-aggregated) criterion belongs in the WHERE clause and not the HAVING, it's largely for self-documenting reasons, it won't actually be making a difference.
When the SQL is parsed, the scalar criterion will be included in the requesting WHERE clause (i.e. the engine is smart enough to know not to apply non-aggregated criteria after aggregation has occurred.)
Even if there isn't a WHERE clause in your request as written. ;-)

But yes, do move it and the other suggestions too.

Cheers

--------------------
Go to the top of the page
 
ordnance1
post Jan 4 2017, 01:20 AM
Post#4



Posts: 477
Joined: 7-May 11



Thanks for your reply
I incorporated your suggestion's but I did have to make a modification to get it to work:

CODE
Where [RequestDate] >=" & Format(StartDate, "#mm\/dd\/yyyy#") & " AND RequestDate <"& Format(EndDate, "#mm\/dd\/yyyy#")
became
Where [RequestDate] >=" & StartDate & " AND RequestDate <" & EndDate &

the original code produced the following error

Attached File  Untitled_pictureaaaa.png ( 14.83K )Number of downloads: 0


beyond that the code worked but there was no increase in speed.


CODE
Public Sub FormatCalendar(intMonth As Integer, intYear As Integer)

'   This subroutine started out as a simple routine to draw the correct squares on a popup
'   calendar form written by Allen Browne.  Now it includes code to format the squares based
'   on the number of time off requests made for the day associated with each square.

'   Variables:
    Dim dtStartDate As Date     'First of month
    Dim iDays As Integer        'Days in month
    Dim iOffset As Integer      'Offset to first label for month.
    Dim i As Integer            'Loop controller.
    Dim iDay As Integer         'Day under consideration.
    Dim bShow As Boolean        'Show the label

'============================================================================
=====================
'   This block of code creates a recordset that contains the number of time-off
'   requests for the specified month.

    Dim frm As Form, strSQL As String, rst As DAO.Recordset, db As DAO.Database
    Dim intCount As Integer


Dim StartDate As Date
Dim EndDate As Date
StartDate = DateSerial(2017, intMonth, 1) 'First date of the month
EndDate = DateAdd("m", 1, StartDate)

'   Make a shortcut reference to the specified month's subform
        Set frm = Forms![frm_Navigation]![NavigationSubform].Controls("cal" & Format(intMonth, "00")).Form
'   Create a recordset with the count of time-off requests for the specified month
        strSQL = "SELECT RequestDate, Count(*) AS CountOfAbsenceID " & _
                 "FROM qry_Filtered_DaysOff " & _
                 " Where [RequestDate] >=" & StartDate & " AND RequestDate <" & EndDate & _
                 " GROUP BY RequestDate "
        Set db = CurrentDb
        Set rst = db.OpenRecordset(strSQL)
        If rst.RecordCount > 0 Then rst.MoveFirst
'   Fill in the month name at the top of the monthly calendar
        frm.Controls("txtMonthName") = DateSerial(intYear, intMonth, 1)
'============================================================================
=====================
'   Figure out which squares to hide/show, and what day number to place in the visible ones
        dtStartDate = DateSerial(intYear, intMonth, 1) 'First of month
        iDays = Day(DateSerial(intYear, intMonth + 1, 0)) 'Days in month.
        iOffset = Weekday(dtStartDate, vbSunday) - 2    'Offset to first label for month.

'   Hide the circle highlight by default (later it will be made visible only on the
'   month that holds the currently selected date)
        frm.Controls("lblHighlight").Visible = False
        For i = 0 To 41
            With frm.Controls("lblDay" & Format(i, "00"))
                iDay = i - iOffset
                bShow = ((iDay > 0) And (iDay <= iDays))
                If .Visible <> bShow Then
                    .Visible = bShow
                End If

'============================================================================
=====================
'    This block of code was added by UtterAccess.com user doctor9 to handle formatting each
'    day, based on the number of time off requests for that day.
        If Forms![frm_Navigation]![txt_CalTest] < 3 Then

    'Valid day for this month?
        If iDay > 0 And iDay <= Day(DateSerial(intYear, intMonth + 1, 0)) Then
    'Is this a holiday?  Make the box dark grey and skip checking time off requests
        If DCount("LineID", "tbl_Holidays", _
        "HolidayDate=#" & DateSerial(intYear, intMonth, iDay) & "#") > 0 Then

    'White text on Black
                .ForeColor = vbWhite
                .BackColor = vbBlack
            Else
'                   Not a holiday - format the label based on how many time off requests exist for that day
                intCount = 0
                If rst.RecordCount > 0 Then
                    rst.FindFirst "[RequestDate]=#" & DateSerial(intYear, intMonth, iDay) & "#"
                    If rst.NoMatch = False Then
                        intCount = rst!CountOfAbsenceID
                    End If
                End If

                Select Case intCount
                Case Is < DLookup("Slots", "qry_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#") 'Open slots remaining
                    .BackColor = vbGreen
                    .ForeColor = vbBlack
                Case Is >= DLookup("Slots", "qry_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#")  'No open slots, only next in line available
                    .BackColor = vbRed  'Red
                    .ForeColor = vbYellow
                End Select
            End If
        End If
    End If

'         If this control represents the current date displayed in the textbox, highlight this control
            If intYear = Year(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) And intMonth = Month(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) And iDay = Day(Forms![frm_Navigation]![NavigationSubform].Form![txt_SelectedDate]) Then
'               Show the highlight circle
                frm.lblHighlight.Visible = True
                Const lngcVOffset As Long = -83
                Const lngcHOffset As Long = -21
'               Move the highlight to circle the label they clicked on
                frm.lblHighlight.Left = frm.Controls(.Name).Left + lngcHOffset
                frm.lblHighlight.Top = frm.Controls(.Name).Top + lngcVOffset
            End If
'============================================================================
=====================
            If (bShow) And (.Caption <> iDay) Then
                .Caption = iDay
            End If
        End With
    Next

'   Cleanup
    Set frm = Nothing
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing

End Sub
Go to the top of the page
 
LPurvis
post Jan 4 2017, 04:52 AM
Post#5


UtterAccess Editor
Posts: 16,058
Joined: 27-June 06
From: England (North East / South Yorks)


Hi again.

Just to mention, the original suggestion was almost right. You just needed some additional character escaping.
i.e. instead of:
" Where [RequestDate] >=" & Format(StartDate, "#mm\/dd\/yyyy#") & " AND RequestDate <" Format(EndDate, "#mm\/dd\/yyyy#") & _
you'd have:
" Where [RequestDate] >=" & Format(StartDate, "\#mm\/dd\/yyyy\#") & " AND RequestDate <" Format(EndDate, "\#mm\/dd\/yyyy\#") & _

As for the speed not visibly improving, that suggests that the problem is already occurring upstream. (i.e. improving the efficiency of this query matters less, because you're already calculating heavily in the query upon which it's based).
Your original query was:
SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
"FROM qry_Filtered_DaysOff GROUP BY RequestDate " & _
"HAVING Month([RequestDate]) = " & intMonth
SO what's the definition of qry_Filtered_DaysOff?
Is RequestDate a non-calculated field in that?

Also how many rows are we talking about here? If it's not actually that many then the burden of processing likely lies not with query but with the subsequent operations you perform upon it.
When you iterate through the recordset, how many days are you processing? Filling just that month's grid?

And there's the DLookups you do:
DLookup("Slots", "qry_Filtered_Slots", "VacDate =#" & intMonth & " / " & iDay & " / " & intYear & "#")
How quickly do they execute (given that they're executed, what, 31 times?)
Another recordset for these might be better - but then the definition of the query again comes into play.

Timing intervals in stages of the code to determine where your lag is actually occurring is really your first step.

Cheers

--------------------
Go to the top of the page
 
TheSmileyCoder
post Jan 4 2017, 04:53 AM
Post#6


UtterAccess VIP
Posts: 1,499
Joined: 19-January 12
From: Denmark, Copenhagen


Besides the things mentioned by Leigh, Have you made sure you have an index on RequestDate?

--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 
ordnance1
post Jan 4 2017, 07:31 AM
Post#7



Posts: 477
Joined: 7-May 11



There was a compile issue with the code you sent.

The code line:

" Where [RequestDate] >=" & Format(StartDate, "#mm\/dd\/yyyy#") & " AND RequestDate <" Format(EndDate, "#mm\/dd\/yyyy#") & _

Would not compile until I added a & just before the second Format. After I added the & the code compiled but when it ran I got a run time error, see the attachment in my last post.

More importantly I want to thank you for all your time and expertise with this issue.
Go to the top of the page
 
LPurvis
post Jan 4 2017, 07:40 AM
Post#8


UtterAccess Editor
Posts: 16,058
Joined: 27-June 06
From: England (North East / South Yorks)


Ah right, you know what, I'm just gonna write it all out. :-)
CODE
strSQL = "SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
                 " FROM qry_Filtered_DaysOff " & _
                 " WHERE [RequestDate] >=" & Format(StartDate, "\#mm\/dd\/yyyy\#") & " AND RequestDate <" & Format(EndDate, "\#mm\/dd\/yyyy\#") & _
                 " GROUP BY RequestDate "


Where you've created variables for StartDate and EndDate I presume?
Does that compile and run?
However, just to reiterate, I don't think that's the ultimate cause of your performance. The other things mentioned need to be looked at.

Cheers

--------------------
Go to the top of the page
 
TheSmileyCoder
post Jan 4 2017, 07:42 AM
Post#9


UtterAccess VIP
Posts: 1,499
Joined: 19-January 12
From: Denmark, Copenhagen


Yea, Since I don't have your database its hard to get the syntax right, as I can't easily test it.
The purpose of the format is two fold. One is to tell Access that we are passing a Date. This is done be the # delimiters. When passing a date to the SQL engine we need # before and after, just like we use " before and after strings. When passing a date to SQL server it has to be ' before and after. Ohh the joys smile.gif

Furthermore when access converts a date to a string, it is always done in the regional format, which in my cases is Day/Month/Year, but the access database engine expects it in US format, i.e. month/day/year. So therefore I explicitly code my date variables to be formatted to match what the database engine expects, that way the code won't fail if loaded on a different PC.
It is a good idea to abstract the formatting away to a function like so:
CODE
Public Function FormatSQLDate(pDate As Date) As String
    FormatSQLDate = Format(pDate, "\#mm\/dd\/yyyy\#")
End Function

Note that I added a \ before and after the # in the format string. I had forgotten that in my initial reply.

So now it looks like:
CODE
strSQL = "SELECT RequestDate, Count(RequestDate) AS CountOfAbsenceID " & _
                 "FROM qry_Filtered_DaysOff " & _
                 " Where [RequestDate] >=" & FormatSQLDate(StartDate) & " AND RequestDate <" & FormatSQLDate(EndDate) & _
                 " GROUP BY RequestDate "


--------------------
TheSmileyCoder // Anders Ebro (Access MVP)

~~~~~~~~
Blog: www.TheSmileyCoder.com
YouTube Channel: TheSmileyCoder
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st September 2017 - 06:11 PM