General Speed Question (1 Viewer)

[steve1111]

Sorry for probably a pretty entry level question, I have looked a little but couldn't find a black or white answer and I was hoping for some quick feedback before I did the work.

Currently, I have a subform that holds times slots and 5 days, there are 115 textboxes on the subform. There are three of those forms in my main form.

I can loop through a recordset for three of my drivers for the week and the form loads in about 6-8 seconds... not terrible but I have more lookups and formatting to add.

My question is, is it quicker on the load instead of each driver having 1 subform with 115 boxes, to have each driver have 5 subforms that each control a different day of the week, each of the 5 subforms only having 23 textboxes, or are we splitting hairs on any potential speed increase this way?

Thanks, I don't want to break this down into smaller chunks if I won't gain speed.

 

[Minty]

Is the data in the subforms editable ?

 

[steve1111]

I am not treating them as editable. I am thinking a right-click menu to open a popup form with the ride(s) ID numbers to do all the editing there. there are more details for the ride than I can fit in the boxes.

 

[Minty]

You might find this quicker it you created a temporary table for each sub form?

Using a query would be quicker than looping through a recordset if the desired results can be achieved that way.

 

[steve1111]

I started out with a query and used a series of dlookups and dcounts to get the day and time blocks that I needed, but that was taking 12-18 seconds to loop through all the controls. So that is what lead me to recordsets, only slightly more familiar that QueryDefs...would brushing up on the QueryDefs be the way to speed this up more?

Public Sub BuildRidesD1()


'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                strRidesM = "SELECT dbo_Rides.ID, dbo_Rides.ApptDatetime, IIf([dbo_Rides]![ApptDuration]=0,'O','I') AS Dir, dbo_Rides.DriverID, dbo_Patients.LastName, Timevalue([dbo_Rides]![ApptDatetime]) AS rTime, dbo_Clinics.Abbr, dbo_Rides.isFrontSeatOnly, dbo_Rides.isRidealong, dbo_Rides.NumPassengers, dbo_Patients.isWheelchair, dbo_Patients.ExtraPickupMins "
                strRidesM = strRidesM & "FROM (dbo_Rides INNER JOIN dbo_Patients ON dbo_Rides.PatientID = dbo_Patients.ID) INNER JOIN dbo_Clinics ON dbo_Patients.DefaultClinicID = dbo_Clinics.ID "
                strRidesM = strRidesM & " WHERE DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] & "#" & " AND [DriverID] = " & [Forms]![mTM]![subTM1].[Form]![subD1H].[Form]![txtDriverID] & " AND [CxlReasonID] is null"
                strRidesM = strRidesM & " ORDER BY dbo_Rides.ApptDatetime, IIf([dbo_Rides]![ApptDuration]=0,'O','I');"
                              
                 Set rsm = db.OpenRecordset(strRidesM, adOpenDynamic, dbSeeChanges)
                              
For Each c In Me.Form
    If left(c.Name, 1) = "z" Then
        Select Case Mid(c.Name, 2, 1)
            Case "m"
                tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                
                    With rsm
                        .Filter = "DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 5 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
                        Set rsmf = .OpenRecordset
                    End With
                    
                    With rsmf
                        If .RecordCount <> 0 Then
                            If Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                'Do Until .EOF
                                    If .RecordCount > 1 Then
                                        c = .RecordCount & " Rides" & vbNewLine & vbNewLine
                                            Do Until .EOF
                                                c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine & "------------------" & vbNewLine
                                            .MoveNext
                                            Loop
                                        Else
                                        Do Until .EOF
                                        c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
                                        .MoveNext
                                        Loop
                                    End If
                                '.MoveNext
                                'Loop
                            End If
                        End If
                    End With
                    
                    Forms!mTM!subTM1.Form!subD1H.Form!mRideCount = ""
                
            Case "t"
                tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                
                    With rsm
                        .Filter = "DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 4 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 4 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
                        Set rsmf = .OpenRecordset
                    End With
                    
                    With rsmf
                        If .RecordCount <> 0 Then
                            If Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                Do Until .EOF
                                c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
                                .MoveNext
                                Loop
                            End If
                        End If
                    End With
                
            Case "w"
                tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                
                    With rsm
                        .Filter = "DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 3 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 3 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
                        Set rsmf = .OpenRecordset
                    End With
                    
                    With rsmf
                        If .RecordCount <> 0 Then
                            If Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                Do Until .EOF
                                c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
                                .MoveNext
                                Loop
                            End If
                        End If
                    End With
                
            Case "r"
                tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                
                    With rsm
                        .Filter = "DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 2 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 2 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
                        Set rsmf = .OpenRecordset
                    End With
                    
                    With rsmf
                        If .RecordCount <> 0 Then
                            If Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                Do Until .EOF
                                c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
                                .MoveNext
                                Loop
                            End If
                        End If
                    End With
                
                
            Case "f"
                tbs = DLookup("NumValue1", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                tbe = DLookup("NumValue2", "tConstants", "[StatusName] = '" & right(c.Name, 1) & "'")
                
                    With rsm
                        .Filter = "DateValue([ApptDateTime]) Between  #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 1 & "# And #" & [Forms]![mTM]![subTM1].[Form]![txtToDate] - 1 & "#" & " And [rTIme] Between " & tbs & " And " & tbe & ""
                        Set rsmf = .OpenRecordset
                    End With
                    
                    With rsmf
                        If .RecordCount <> 0 Then
                            If Not .EOF Then
                                .MoveLast
                                .MoveFirst
                                Do Until .EOF
                                c = c & !Dir & ":" & vbTab & !LastName & vbNewLine & !abbr & vbNewLine
                                .MoveNext
                                Loop
                            End If
                        End If
                    End With
                
        End Select
    End If
Next

rsmf.Close
rsm.Close
db.Close
Set rsm = Nothing
Set rsmf = Nothing
Set db = Nothing

End Sub

 

[Minty]

If you can generate the data into a temporary table quickly, then you can create a form / sub form to display it, even it it isn't in a normalised format.

You could probably create the table in the desired layout of rows and columns using a suitable set of queries or possibly a union query.
Query Defs might well be the way forward, but without seeing your base data structure it's quite difficult to visualize.

 

[steve1111]

Ok, thanks Minty, you have given me some next steps forward!

 

[Pat Hartman]

Normalizing the table is the best option. Then if you want to display data as a grid, use a crosstab query to pivot it. That eliminates all the code used to populate unbound controls if that is what you are doing.

 

[steve1111]

Hi Pat, Thanks for the suggestion. The little I have actually used crosstabs in the past i don't think this would work with the about of data I need to show in each box. More so than just an aggregate function. I need to pull in Names, Id, direction and store code, some days and times block have more than one record as well.

I will give that for a count of IDs only, very fast solution.

 

[The_Doc_Man]

I'm not going to step into the design question but I will step into the speed issue.

Anything you do in VBA is INTERPRETED code, not executed code in the strict sense of that term. VBA is a semi-compiler that produces pseudo-code segments that are then treated as input to simulator/emulator type of module.

Things done via query are managed by something that IS compiled. The query manager analyzes your query text, sets itself up, and then performs query steps for you using compiled code. I have seen three-for-one speed improvements by converting recordset operations to SQL. Admittedly, recordsets are more flexible, but if the SQL can be done without too many sub-queries, you usually have an advantage in SQL.

Just a note: Your IIF could be managed by a type of JOIN too. IIFs have to be evaluated for every field. You could get some minor improvement that way.

However, there is another way that might initially seem counterintuitive. It is not.

You are doing a three-table join plus an ORDER BY and a non-trivial WHERE clause. The WHERE clause seems to ONLY apply to the drivers table. Split this up as follows. Do one query on the drivers table and apply ALL of your where filtration on it. THEN do your join on the other tables and apply the ORDER BY clause. (Yes, I'm talking about a query of a query.)

The reason is that in the combined query you have EVERY RECORD IN EVERY MEMBER TABLE participating in the big JOIN and then the filtration and ordering occur. If you split this so that you only JOIN with the drivers selected by the WHERE clause, the number of elements to be joined is smaller. The ORDER BY only needs to be on the outermost layer of the query. The WHERE clauses don't "care" about order of filtration and the JOIN clauses don't "care" about order either.

Doing it your way, you dump everything in the box and then sort it out and filter it out. Doing it via split queries, you limit the amount you put in the box in the first place. Less work to do at the next step.

 

[steve1111]

Doc, thank you for the explanation, and it does make sense. I will work that direction combined with the other suggestions head back to the direction of the query since i do not need to manipulate the recordset in this step.

 

[Pat Hartman]

Post a picture of your form and I'll tell you if you can do any part of it with crosstabs. For example, a crosstab can only pivot one field at a time so if you want a count and a dollar total, you can't do that with a single crosstab. However you can create two crosstabs and join them so that you have both count and dollars in the same row.

 

[steve1111]

Hi Pat,

I am on a different computer now, but there is a png in the first post. Each "cell" cross-references the driver, day and 30-minute time block. It pulls in the direction, I or O, the Job name and then the second line of the cell is the job location. There are still some attributes I would like to code in the second line of the cell that would be meaningful symbols based on lookups of the ride handled by if statements.

Some cells have more than one ride and are represented by the number of rides, like "2 Rides". Then when the textbox has the focus it will grow so you can see up to 6 jobs if they are scheduled with that driver on that date in that time block.

Again, not being overly familiar with crosstabs, I like what you are presenting, but I would also need to show all five days in the work week like I the capture above, even if the driver is off or nothing is currently scheduled. With my initial playing around I am only getting the dates that show up with rides.

Knowing little about crosstabs, this is definitely intriguing.

 

[Pat Hartman]

Getting 5 days per week is not a problem since you can use the column names property to tell the query that you always want 5 days even if there are not always 5 days in the data. Getting three weeks across is more of a problem and we are getting into Excel territory here but you could use three subforms side by side for week 1, week 2, and week 3. Since you have 4 crosstabs that you want to stack, you will need to use a union query and you'll need to figure out a way to encode the rows so that you can control their sort order.

 

[isladogs]

Just spotted this question and thought I'd share a similar situation in one of my databases - calendar events in month / week / day views.

In this case I didn't use crosstabs (though I often do so).
The event slot placements depends on the times & are matched against a grid of half hourly time slots (week & day views)

The month & day views load almost instantly.
The week view has over 450 controls and takes 2-3 seconds which I think is about as good as it can get with this design
As a result, I believe it should be possible to optimise the load time of your current form whether using crosstabs or otherwise

In case its of interest, see attached for screenshots

 

[steve1111]

Ridders, very nice work. did you load with queries or a recordset? I like Doc's suggestion if I am not going to use crosstabs, but I would like to become more familiar with them so I am going to play around with Pat's Idea too.

I see you have some formatting too, there are a few more lookups I need to do from the data and then some conditional formatting so if I could get down to 2-3 seconds I could sleep at night!

 

[isladogs]

Sorry I forgot to reply to this post.
The data for each subform is obtained from 3 'temporary' local tables: tblCalendarMonthData / tblCalendarWeekData / tblCalendarDayData

The tables are permanent but the data isn't
When the form is opened it defaults to the month view subform & that table is populated for the selected month. When you change months, it is repopulated. Similarly if you change to the week view, that table is populated ... etc

The reason for this is to ensure the data is editable
When events are created or edited from the form, the main source tables are also updated so it all stays synchronised.
It all works well including the export calendar to Outlook feature

 

[steve1111]

Thanks everyone, three separate temp tables were the way to go getting a total of 345 textboxes to loop and concatenate the string in less than a second and a half.