×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Simple explanation of my project. I have nested loops that each are based on a linked table query (source = SQL Server tables).

Loop1 start
| Loop2 start
| | Loop3 start
| | | Loop4 start
| | | Loop4 end
| | | Loop5 start
| | | Loop5 end
| | Loop3 end
| | Loop6 start
| | | Loop7 start
| | | Loop7 end
| | | Loop8 start
| | | Loop8 end
| | Loop6 end
| Loop2 end
Loop1 end



Loops 5 & 8 are my destination points that populate a table based on the criteria generated from the outer loops.

I am experiencing two things that surprise me. The time to execute this VBA (using same criteria that define the outer loops) has such a varying run time. I have two groupings of test runs, one on the LAN and one via VPN (Off-site). The location is outside this discussion. If I run the script from LAN the execution time can vary anywhere from a few minutes to over 10 minutes. Even when executing back to back times. Very inconsistent from time to time or even day to day.

The second experience is this execution takes anywhere from 4 minutes up to 30 minutes. The information is in reference tables housed in SQL Server. I am expecting the output to be no more than 4 minutes based on queries execution time, when done manually, is instantaneous.

1. Where is the bottleneck in the design that causes this to take long to execute?
2. Why is the execution time so random for times for running?

Here is my function that contains the loops

CODE

Dim db As DAO.Database
    Dim rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated As Recordset
    Dim SQL, SQL2, SQL3, SQL4 As Variant
    Dim origPractice As Integer
    Dim newPractice As Integer
    Dim Client As Variant
    Dim Practice As Integer
    Dim ShowPractice As Integer
    Dim StartTime As Date
    Dim n As Integer
    Dim s As String
    
    n = FreeFile()
    Open "C:\temp\Synergy3" & "_" & SurveyNumber & ".txt" For Output As #n
    
    Set db = CurrentDb
    
    s = "starting Matrix: " & Format(Now, "h:nn:ss AM/PM")
    Debug.Print s
    Print #n, s
    
    'get information pre looping
    'original Practice
    SQL = "SELECT PracticeID FROM tblProjectDetails WHERE aID = " & SurveyNumber & " ;"
    Set rs = CurrentDb.OpenRecordset(SQL)
    origPractice = rs!PracticeID 'practice will be used as base for what was selected and showpractice is for calculating
    'ClientID
    SQL = "SELECT ClientID FROM tblProjectDetails WHERE aID = " & SurveyNumber & " ;"
    Set rs = CurrentDb.OpenRecordset(SQL)
    Client = rs!ClientID
    
    'rs.Close
    'Set rs = Nothing
    
            
    
    'Parent Loop rsPARENT : https://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access
        SQL = "SELECT RoleID, AssociateID FROM tblProjectsAssignedTo WHERE SurveyNumberID = " & SurveyNumber & " ORDER BY 1 ASC;"
        Set rsPARENT = CurrentDb.OpenRecordset(SQL)
        'Check to see if the recordset actually contains rows
        If Not (rsPARENT.EOF And rsPARENT.BOF) Then
            rsPARENT.MoveFirst 'Unnecessary in this case, but still a good habit
            Do Until rsPARENT.EOF = True
    '        Debug.Print "Call Parent SQL " & Format(Now, "h:nn:ss AM/PM")
    'Child Loop  rsCHILD
                'Debug.Print "Parent: " & rsPARENT!AssociateID & ", Role: " & rsPARENT!RoleID
                SQL2 = "SELECT RoleID, AssociateID FROM tblProjectsAssignedTo WHERE SurveyNumberID = " & SurveyNumber & " ORDER BY 1 ASC;"
                Set rsCHILD = CurrentDb.OpenRecordset(SQL2)
                'Check to see if the recordset actually contains rows
                If Not (rsCHILD.EOF And rsCHILD.BOF) Then
                    rsCHILD.MoveFirst 'Unnecessary in this case, but still a good habit
                    Do Until rsCHILD.EOF = True
                    'set parent / Child elapse timer
    StartTime = Now
                    
                    s = "Parent Role = " & rsPARENT!RoleID & " Child Role = " & rsCHILD!RoleID & " Time: " & Format(Now, "h:nn:ss AM/PM")
                    Debug.Print s
''                    Print #n, s
    
                        'Debug.Print "Parent: " & rsPARENT!AssociateID & ", Role: " & rsPARENT!RoleID & " Child: " & rsCHILD!AssociateID & ", Role: " & rsCHILD!RoleID
    'ServiceType Practice Loop rsServiceType (Technical; but NOT Behavioral skills)
                        s = "Technical Begins: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
                        SQL3 = "SELECT ServiceTypeID FROM tblProjectServiceTypes WHERE ProjectQueueForLcID = (SELECT Max(aID) FROM tblProjectQueueForLCs WHERE SurveyNumberID = " & SurveyNumber & ") ORDER BY 1 ASC;"
                        Set rsServiceType = CurrentDb.OpenRecordset(SQL3)
                        'Debug.Print "Call ServiceType SQL " & Format(Now, "h:nn:ss AM/PM")
                        'Check to see if the recordset actually contains rows
                        If Not (rsServiceType.EOF And rsServiceType.BOF) Then
                            rsServiceType.MoveFirst 'Unnecessary in this case, but still a good habit
                            Do Until rsServiceType.EOF = True
                                'Debug.Print "ServiceTypeID: " & rsServiceType!ServiceTypeID
                                
                                'Consulting Matrix Part 1: Technical Skills
                                SQL4 = "SELECT Technical FROM tblConsultingMatrix WHERE ParentRole = " & rsPARENT!RoleID & " AND ChildRole = " & rsCHILD!RoleID & " ;"
                                Set rsConsultingMatrix = CurrentDb.OpenRecordset(SQL4)
                                'Check to see if the recordset actually contains rows
                                If Not (rsConsultingMatrix.EOF And rsConsultingMatrix.BOF) Then
                                    rsConsultingMatrix.MoveFirst
                                    Do Until rsConsultingMatrix.EOF = True
                                    
                              'ShowPractice logic here for child
                                        Select Case rsPARENT!RoleID
                                            Case 7, 6, 5
                                                ShowPractice = origPractice
                                                Practice = 5
                                            Case 4
                                                SQL = "SELECT aID FROM tblSkills WHERE practiceID = " & origPractice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID
                                                Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
                                                'Debug.Print "Call skills Role4 SQL " & Format(Now, "h:nn:ss AM/PM")
                                                If Not (rs.EOF And rs.BOF) Then
                                                    'records exist
                                                    ShowPractice = origPractice
                                                    Practice = origPractice
                                                Else
                                                    'records do NOT exist
                                                    ShowPractice = origPractice
                                                    Practice = 5
                                                End If
                                            Case 3, 2, 1
                                                ShowPractice = origPractice
                                                Practice = origPractice
                                        End Select
    'Technical Skills to be populated HERE
                                        'Behavioral needs be done outside/after ServiceType Loop
                                        If rsConsultingMatrix!Technical = 1 Then
                                        ''Debug.Print "Begin Technical Skills " & Format(Now, "h:nn:ss AM/PM")
                                            SQL = "SELECT aID, SkillNo, Skill, SkillVersion, BusinessTypeID, ShortForm "
                                            SQL = SQL + "FROM tblSKills "
                                            SQL = SQL + "WHERE PracticeID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND "
                                            SQL = SQL + "SkillTypeID = " & rsConsultingMatrix!Technical & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID & " AND "
                                            SQL = SQL + "SkillVersion = (SELECT MAX(SkillVersion) FROM tblSkills WHERE PracticeID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND SkillTypeID = " & rsConsultingMatrix!Technical & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID & ") AND "
                                            SQL = SQL + "ActiveYn = 1 AND BusinessTypeID = 1 "
                                            SQL = SQL + "ORDER BY SkillNo ASC "
                                            Set rsSkills = db.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges) '--, dbSeeChanges)
                                            'Debug.Print "Skill Recordset Defined: " & Format(Now, "h:nn:ss AM/PM")
                           
                                            If Not (rsSkills.EOF And rsSkills.BOF) Then
                                                rsSkills.MoveFirst
                                                Do Until rsSkills.EOF = True
    ''                                                Set rsSurveyGenerated = CurrentDb.OpenRecordset("tblSurveysGenerated", dbOpenDynaset, dbFailOnError + dbSeeChanges)
    ''                                                rsSurveyGenerated.AddNew
    ''                                                    rsSurveyGenerated!SurveyNumberID = SurveyNumber
    ''                                                    rsSurveyGenerated!ClientNumberID = Client
    ''                                                    rsSurveyGenerated!ParentID = rsPARENT!AssociateID
    ''                                                    rsSurveyGenerated!ChildID = rsCHILD!AssociateID
    ''                                                    rsSurveyGenerated!SkillAutoID = rsSkills!aID
    ''                                                    rsSurveyGenerated!SkillOrderNumber = rsSkills!SkillNo
    ''                                                    rsSurveyGenerated!SkillVersion = rsSkills!SkillVersion
    ''                                                    rsSurveyGenerated!ShowPracticeID = ShowPractice
    ''                                                    rsSurveyGenerated!PracticeID = Practice
    ''                                                    rsSurveyGenerated!RoleID = rsCHILD!RoleID
    ''                                                    rsSurveyGenerated!SkillTypeID = rsConsultingMatrix!Technical
    ''                                                    rsSurveyGenerated!ServiceTypeID = rsServiceType!ServiceTypeID
    ''                                                    rsSurveyGenerated!BusinessTypeID = rsSkills!BusinessTypeID
    ''                                                    rsSurveyGenerated!SurveyCreatedDate = Date
    ''                                                rsSurveyGenerated.Update
    '
                                                    SQL2 = "INSERT INTO tblSurveysGenerated (SurveyNumberID, ClientNumberID, ParentID, ChildID, SkillAutoID, SkillOrderNumber, SkillVersion, ShowPracticeID, PracticeID, RoleID, SkillTypeID, ServiceTypeID, BusinessTypeID, SurveyCreatedDate) "
                                                    SQL2 = SQL2 + "VALUES (" & SurveyNumber & ", " & Client & ", " & Chr(34) & rsPARENT!AssociateID & Chr(34) & ", " & Chr(34) & rsCHILD!AssociateID & Chr(34) & ", " & rsSkills!aID & ", " & rsSkills!SkillNo & ", " & rsSkills!SkillVersion & ", " & ShowPractice & ", " & Practice & ", " & rsCHILD!RoleID & ", " & rsConsultingMatrix!Technical & ", " & rsServiceType!ServiceTypeID & ", " & rsSkills!BusinessTypeID & ", '" & Date & "') "
                                                    db.Execute SQL2
    '                                                DoCmd.SetWarnings False
    '                                                DoCmd.RunSQL SQL2
    '                                                DoCmd.SetWarnings True
                                                    rsSkills.MoveNext
                                                Loop 'Skills
                                            Else 'Skills
                                                'Do not populate Skills
                                            End If 'Skills
                                            'Debug.Print "T:Skills done " & Format(Now, "h:nn:ss AM/PM")
                                            
                                        Else 'Technical Skills
                                            'Do not populate Technical Skills (0's)
                                        End If 'Technical Skills
                                        'Debug.Print "End ConsultingMatrix : " & Format(Now, "h:nn:ss AM/PM")
                                        rsConsultingMatrix.MoveNext
                                    Loop 'ConsultingMatrix
                                Else 'ConsultingMatrix
                                    'Debug.Print "There are no records in the Consulting Matrix recordset."
                                End If 'ConsultingMatrix
                                'Debug.Print "Finished looping through CONSULTING MATRIX. " & Format(Now, "h:nn:ss AM/PM")
    
                                rsServiceType.MoveNext
                            Loop 'rsServiceType
                        Else 'rsServiceType
                            'Debug.Print "There are no records in the SERVICE TYPE recordset. " & Format(Now, "h:nn:ss AM/PM")
                        End If 'rsServiceType
                                s = "Technical Ends: " & Format(Now, "h:nn:ss AM/PM")
                                Debug.Print s
''                                Print #n, s
                        
    'Begin Behavioral Skills HERE (OUTSIDE SERVICE TYPES)
    'ServiceType Loop rsServiceType (Behavioral; NOT Technical skills)
    
                        s = "Behavioral Begins: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
                        SQL3 = "SELECT DISTINCT ServiceTypeID, ServNo FROM tblSkills "
                        SQL3 = SQL3 + "WHERE ProjectRoleID = " & rsCHILD!RoleID & " AND PracticeID in (5," & Practice & ") AND SkillTypeID = 2 AND ActiveYn = 1 AND BusinessTypeID = 1 "
                        SQL3 = SQL3 + "ORDER BY ServNo ASC"
                        Set rsServiceType = CurrentDb.OpenRecordset(SQL3)
                        'Check to see if the recordset actually contains rows
                        If Not (rsServiceType.EOF And rsServiceType.BOF) Then
                            rsServiceType.MoveFirst 'Unnecessary in this case, but still a good habit
                            Do Until rsServiceType.EOF = True
                                'Consulting Matrix Part 2: Behavioral Skills
                                SQL4 = "SELECT Behavioral FROM tblConsultingMatrix WHERE ParentRole = " & rsPARENT!RoleID & " AND ChildRole = " & rsCHILD!RoleID & " ;"
                                Set rsConsultingMatrix = CurrentDb.OpenRecordset(SQL4)
                                'Check to see if the recordset actually contains rows
                                If Not (rsConsultingMatrix.EOF And rsConsultingMatrix.BOF) Then
                                    rsConsultingMatrix.MoveFirst
                                    Do Until rsConsultingMatrix.EOF = True
                                        '---ShowPractice logic here for child
                                        'Roles 7,6,5 are typically practice 'ALL' (5)
                                        'Role 4 can be ALL(5) or other
                                        'Roles 3,2,1 are typically Practice
                                        Select Case rsPARENT!RoleID
                                            Case 7, 6, 5
                                                ShowPractice = origPractice
                                                Practice = 5
                                            Case 4
                                                SQL = "SELECT aID FROM tblSkills WHERE practiceID = " & Practice & " AND ProjectRoleID = " & rsCHILD!RoleID & " AND ServiceTypeID = " & rsServiceType!ServiceTypeID
                                                Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
                                                'Debug.Print "Call B:Skills Role 4 SQL " & Format(Now, "h:nn:ss AM/PM")
                                                If Not (rs.EOF And rs.BOF) Then
                                                    'records exist
                                                    ShowPractice = origPractice
                                                    Practice = origPractice
                                                Else
                                                    'records do NOT exist
                                                    ShowPractice = origPractice
                                                    Practice = 5
                                                End If
                                            Case 3, 2, 1
                                                ShowPractice = origPractice
                                                Practice = 5
                                        End Select
    
                                        If rsConsultingMatrix!Behavioral = 1 Then  ' 1 = Yes; 2 = No
                                            'Yes do Behavioral Skills
                                            SQL = "SELECT aID, SkillNo, Skill, SkillVersion, BusinessTypeID "
                                            SQL = SQL + "FROM tblSKills "
                                            SQL = SQL + "WHERE PracticeID = 5 AND ProjectRoleID = " & rsCHILD!RoleID & " AND "
                                            SQL = SQL + "SkillTypeID = 2 AND ServiceTypeID = " & rsServiceType!ServiceTypeID & " AND "
                                            SQL = SQL + "SkillVersion = (SELECT MAX(SkillVersion) FROM tblSkills WHERE PracticeID = 5 AND ProjectRoleID = " & rsCHILD!RoleID & " AND SkillTypeID = 2 AND ServiceTypeID = " & rsServiceType!ServiceTypeID & ") AND "
                                            SQL = SQL + "ActiveYn = 1 AND BusinessTypeID = 1 "
                                                'if Role = CRM(7), add filter for ShortForm
                                                If rsCHILD!RoleID = 7 Then
                                                    SQL = SQL + "AND ShortForm = 1 "
                                                Else
                                                    'Do nothing
                                                End If 'ShortForm
                                            SQL = SQL + "ORDER BY SkillNo ASC "
                                            Set rsSkills = db.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
    
                                            If Not (rsSkills.EOF And rsSkills.BOF) Then
                                                rsSkills.MoveFirst
                                                Do Until rsSkills.EOF = True
                                            
                                                    'Begin populating Behavioral Skills
                                                    SQL2 = "INSERT INTO tblSurveysGenerated (SurveyNumberID, ClientNumberID, ParentID, ChildID, SkillAutoID, SkillOrderNumber, SkillVersion, ShowPracticeID, PracticeID, RoleID, SkillTypeID, ServiceTypeID, BusinessTypeID, SurveyCreatedDate) "
                                                    SQL2 = SQL2 + "VALUES (" & SurveyNumber & ", " & Client & ", " & Chr(34) & rsPARENT!AssociateID & Chr(34) & ", " & Chr(34) & rsCHILD!AssociateID & Chr(34) & ", " & rsSkills!aID & ", " & rsSkills!SkillNo & ", " & rsSkills!SkillVersion & ", " & ShowPractice & ", " & Practice & ", " & rsCHILD!RoleID & ", 2, " & rsServiceType!ServiceTypeID & ", " & rsSkills!BusinessTypeID & ", '" & Date & "') "
                                                    db.Execute SQL2
    '                                                DoCmd.SetWarnings False
    '                                                DoCmd.RunSQL SQL2
    '                                                DoCmd.SetWarnings True
                                                    rsSkills.MoveNext
                                                Loop 'Skills
                                            Else 'Behavioral Skills
                                                'Do not populate Behavioral Skills (0's)
                                            End If 'Behavioral Skills
                                            'Debug.Print "B:Skills done " & Format(Now, "h:nn:ss AM/PM")
                                        End If 'rsConsultingMatrix!Behavioral
        
                                        rsConsultingMatrix.MoveNext
                                    Loop 'ConsultingMatrix
                                Else 'ConsultingMatrix
                                    ''Debug.Print "There are no records in the Consulting Matrix recordset."
                                End If 'ConsultingMatrix
                                'Debug.Print "Finished looping through CONSULTING MATRIX. " & Format(Now, "h:nn:ss AM/PM")
                                rsServiceType.MoveNext
                            Loop 'rsServiceType
                    
                        Else 'rsServiceType
                            'Debug.Print "There are no records in the SERVICE TYPE recordset. " & Format(Now, "h:nn:ss AM/PM")
                        End If 'rsServiceType
                        s = "Behavioral Ends: " & Format(Now, "h:nn:ss AM/PM")
                        Debug.Print s
''                        Print #n, s
    
                        'END Behavioral Skills

s = "Time: " & Format(Now, "h:nn:ss AM/PM") & " - ElapsedTime for Parent " & rsPARENT!RoleID & " with child " & rsCHILD!RoleID & " is " & HoursMinutes(StartTime, Now)
Debug.Print s
Print #n, s
                        
                        rsCHILD.MoveNext
                    Loop 'rsCHILD
                Else 'rsCHILD
                    'Debug.Print "There are no records in the CHILD recordset. " & Format(Now, "h:nn:ss AM/PM")
                End If 'rsCHILD
                'Debug.Print "Finished looping through CHILD records. " & Format(Now, "h:nn:ss AM/PM")
                
                rsPARENT.MoveNext
            Loop 'rsPARENT
        Else 'rsPARENT
            'Debug.Print "There are no records in the PARENT recordset. " & Format(Now, "h:nn:ss AM/PM")
        End If 'rsPARENT
        'Debug.Print "Finished looping through PARENT records. " & Format(Now, "h:nn:ss AM/PM")
    
    
    rs.Close
    rsPARENT.Close
    rsCHILD.Close
    rsServiceType.Close
    rsConsultingMatrix.Close
    
    Set rs = Nothing
    Set rsPARENT = Nothing
    Set rsCHILD = Nothing
    Set rsServiceType = Nothing
    Set rsConsultingMatrix = Nothing
    
    Close #n

End Function 

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

“Where is the bottleneck in the design that causes this to take long to execute?”

You should easily put a start and end time before and after any SQL execution, write it into a simple txt file, and see which point of your code takes the long time. You will see your bottlenecks right away.

Right from the start, you ask for PracticeID and ClientID from the same record in the same table in two ‘trips’ to your DB. Why? This is not going to make a difference in your performance, but …why?

You do know that all RED variables are Variants, right?
Dim rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated As Recordset


---- Andy

There is a great need for a sarcasm font.

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Andy, I am now altering my code to capture times before and after the db.Execute and CurrentDB.OpenRecordSet lines.

Aren't the [rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated]
supposed to be Recordset definitions? Are you saying I should define them as Variants?

The bottleneck I see from my trappings (as you can see above) show that overall the process has a run time variation and at the molecular level (each parent / child) the runs are taking longer than I'd expect. that's why I posted my code... thinking maybe I am processing the loops in an inefficient method. Right now I am using DAO.

Example I have been running the same criteria 12 times... the fastest time is 6 minutes 8 seconds... with the slowest time being 7 minutes 48 seconds... yesterday my slowest was 8 minutes 46 seconds with 6 min 22 sec as fastest... WHY!!??!! would the time be such a spread? Doing the same thing over and over and over is resulting in nearly 2 minutes run time differences! And it's not that after the first run of the day, it's slow and it gets faster (as in caching)... not at all... time is bouncing up and down.

Thoughts??

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

Quote (03Explorer )

Aren't the [rs, rsPARENT, rsCHILD, rsServiceType, rsSkills, rsConsultingMatrix, rsSurveyGenerated] supposed to be Recordset definitions? Are you saying I should define them as Variants?

I know they are supposed to be Recordset definitions, but they are Variants.
If you want them to be Recordsets, define them that way:

Dim rs As Recordset, rsPARENT As Recordset, rsCHILD As Recordset, rsServiceType As Recordset, rsSkills As Recordset, rsConsultingMatrix As Recordset, rsSurveyGenerated As Recordset

But I would define them in separate lines, but that's just me...

In the loops like yours, I would create larger recordsets at the beginning and Filter them when I need, instead of asking for small pieces of data separately.


---- Andy

There is a great need for a sarcasm font.

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Good to know about the definitions as they are Supposed to be vs what you are saying they Actually function as.

I will look into making a bigger recordset and loop of that. I have a feeling I can't eliminate all down to one RecordSet, but I believe I can minimize a few out of the mix.

Thanks for the suggestions!!

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

I used to do some loops inside another loops where I was going after some data in every loop. That was taking some time. Then after re-doing the logic with this new approach - grab more data and filter when needed - the time went down BIG TIME. Sometimes in the range of: from several minutes to just a few seconds.

Even if you can cut just a few trips to your data base, you may improve your execution time as well. Imagine you loop 50 times so make 50 trips to your DB. Now you can go once and filter it 50 times, which is a LOT faster.

Good luck smile


---- Andy

There is a great need for a sarcasm font.

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Andy, can you give me some samples of how you are filtering on the recordset if pulling more data in a run?

Thanks

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

I deal with ADODB recordsets, so my approach is something like this:

CODE

Dim rec1 As New ADODB.Recordset
Dim rec2 As New ADODB.Recordset

rec2.Open "Select ID, Address From SomeTable"
rec1.Open "Select ID, FullName From MyTable"

Do While Not rec1.EOF
    rec2.Filter = "ID = " & rec1!ID.Value
    If rec2.RecordCount > 0 Then
        'Do your magic here
    End If
    rec1.MoveNext
Loop

rec1.Close
Set rec1 = Nothing

rec2.Close
Set rec2 = Nothing 


---- Andy

There is a great need for a sarcasm font.

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Thank you Andy,

I found this URL: http://www.access.mvps.org/access/modules/mdl0010....

With this as the sample (assuming it's DAO and not ADO)

CODE

'***************** Code Start *************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Sub sFilterRS()
Dim db As Database, rs1 As Recordset
Dim rs2 As Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Select * from tblRuns")
    rs1.Filter = "RunID=4"
    Set rs2 = rs1.OpenRecordset
    'rs2 should contain only one record
    MsgBox rs2.RecordCount
    Set rs2 = Nothing:  Set rs1 = Nothing
    Set db = Nothing
End Sub
'***************** Code End ************** 

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

I typically use DAO because it's been around the longest (just like me).
I would be more explicit with the rs definitions.

CODE --> vba

'   using DAO objects
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsPARENT As DAO.Recordset
    Dim rsCHILD As DAO.Recordset
    Dim rsServiceType As DAO.Recordset
    Dim rsSkills As DAO.Recordset
    Dim rsConsultingMatrix As DAO.Recordset
    Dim rsSurveyGenerated As DAO.Recordset 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

(OP)
Duane, is there a benefit for being explicit when declaring the variables? or does that go with the logic of coding with indenting... good practice but serves no performance benefits.

RE: Nested Loops each based on SQL Server Linked table queries (SLOW and INCONSISTENT for run time)

Good practice, managing your resources, and assurance that you are using what you want to use, and not what the system may decide to use.

"coding with indenting" is a necessity, a must, IMHO if you want to see what is going on in your code. Otherwise, how do you know where the block of code starts and stops... ponder


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close