Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: complicated(imo) select required

    I'm running access 2003

    I've got two tables; Samples and Results, Samples having ~ 15,000 records and results having ~ 207,000 records. What i need to do is retrieve a list of all regos (samples feild) that have two particular testnames (samples feild) where the SampleTypeID (samples feild)=1 and both samples have a result record that it's doneyet (results feild) = true and is of a particular traitcode (results feild).

    The results and samples tables are linked by SampleID which is the Samples tables primary key and the Results tables foreign key.

    because this particular query is going to be run in the loop of a form 100+ times I need it to run as fast as possible.

    I can get it to work using two querys but i'm sure it can be done faster with one.
    Last edited by Access Junkie; 06-08-06 at 23:08.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    sounds like a fairly straight forward select query. chances are, running the selection in one query will be barely, if any, faster than running it in two queries - providing the queries are well written.

    if you are looking for speed, then don't do redundant operations. are you running the same query each time in the loop? if so, run the query once to a recordset and then use the recordset in your loop.

    if this doesn't make sense, post what you are doing in the 100+ loop and we may be able to help you find a more efficient path.

    have fun,
    tc

  3. #3
    Join Date
    Jun 2006
    Posts
    72
    I'll explain what i need to do in better detail.

    I've got a form that allows you to select a single testname, a group of other testnames and a group of traits.

    What i need to do is for every trait, testname and other testname combination is get a list of regos that fit the parameters i specifed in my first post.

    So with the selected testname the form will loop through every selected trait and other testname combination doing the required query.

    The two queries i've got working at the moment are.

    SELECT DISTINCT Samples.TrialRego, Samples.Testname
    FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID
    WHERE (((Samples.Testname)="Brookton" Or (Samples.Testname)="Westonia") AND
    ((Results.DoneYet)=True) AND ((Results.TraitCode)="HWT") AND ((Samples.SampleTypeID)=1));

    The above query is saved as Regolist

    SELECT First(Regolist.TrialRego) AS [TrialRego Field], Count(Regolist.TrialRego) AS NumberOfDups
    FROM Regolist
    GROUP BY Regolist.TrialRego
    HAVING (((Count(Regolist.TrialRego))>1));

    The reasons I don't want to use two querys to do this is because if it's just one query i can create that query in the code and set a variable to it like this:

    Code:
    Dim Rst as DAO.Recordset
    Dim SQL as String
    
    SQL = "SQL would go here"
    Set Rst = Currentdb.CreateQueryDef("",SQL).openrecordset
    But with two querys i have to create the first one as an actual query in the database for the second to refer to like this:

    Code:
    Dim Rst as DAO.Recordset
    Dim SQL as String
    
    SQL = "SQL would go here"
    Currentdb.CreateQueryDef "queryname", SQL
    
    SQL = "SQL of query using other query would go here"
    Set Rst = Currentdb.CreateQueryDef("", SQL).OpenRecordSet
    And of course if i did it this way i'd also have to delete the old first query before it can be recreated for every iteration of the loop.

    any suggestions on how i might write the query or even a better way to go about the entire situation?

    [EDIT] I think i've found a better way to do what i need to do. instead of creating and deleting the first query for every iteration of the loop i can change the SQL of it. The code to do this is:

    currentdb.QueryDefs("Regolist").SQL = "Insert new sql here"

    I've not had a chance to test this discovery yet though.

    I'd still like to see a single query version of the two querys i'm using but i think may have just solved my problem.

    Also while i'm here i've had an occasional problem of selections getting stuck so that if i click on anything with multi selection it will select everything between what was already selected and what i click on as though i'm pressing shift. The only way i can fix it is to exit and go back into my database. So far i've not noticed any pattern for when it happens. Has anyone else had this problem and is it known what causes it?

    [EDIT EDIT]
    I tested the above solution i came up with and it takes about 30 seconds to run through a typical testname, other testname and traitcode selection which isn't crazy long but i'd like to cut it down if possible.

    The Code i'm using to do what i want is here.

    Code:
        Dim SQL As String
        Dim i, ii As Integer
        Dim Test As Boolean
        Dim Rst1, Rst2 As DAO.Recordset
        
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "RegoList" Then
                Test = True
                Exit For
            End If
        Next
        If Test = False Then
            CurrentDb.CreateQueryDef "RegoList", "SELECT *"
        End If
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "CountRegos" Then
                Test = True
                Exit For
            End If
        Next
        If Test = False Then
            SQL = "SELECT First(Regolist.TrialRego) AS [TrialRego Field], Count(Regolist.TrialRego) AS NumberOfDups " _
                & "FROM Regolist " _
                & "GROUP BY Regolist.TrialRego " _
                & "HAVING Count(Regolist.TrialRego)>1"
            CurrentDb.CreateQueryDef "CountRegos", SQL
        End If
        For i = 0 To LstSVarieties.ListCount - 1
            DoEvents
            For ii = 0 To lstControls.ListCount - 1
                DoEvents
                SQL = "SELECT TraitCode FROM TraitLists WHERE TraitGroupID=""" & lstTraitGroups & """"
                Set Rst1 = CurrentDb.CreateQueryDef("", SQL).OpenRecordset
                Do While Not Rst1.EOF
                    DoEvents
                    SQL = "SELECT DISTINCT Samples.TrialRego, Samples.Testname " _
                        & "FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID " _
                        & "WHERE (Samples.Testname=""" & LstSVarieties.ItemData(i) & """ Or Samples.Testname=""" & lstControls.ItemData(ii) & """) AND " _
                        & "Results.DoneYet=True AND Results.TraitCode=""" & Rst1!TraitCode & """ AND Samples.SampleTypeID=1"
                    CurrentDb.QueryDefs("RegoList").SQL = SQL
                    Set Rst2 = CurrentDb.OpenRecordset("CountRegos")
                    Rst1.MoveNext
                Loop
            Next
        Next
        MsgBox "Done"
    The line that is taking all the time is:

    Set Rst2 = CurrentDb.OpenRecordset("CountRegos")

    You'll probably notice i'm not yet using the results of the query for anything in this code, that's because i've not writen the code to do the next part yet... i want to get this right first. Any ideas on speeding it up?... if i have any myself i'll make this post even longer

    [EDIT EDIT EDIT]

    I've changed the code so that it creates both queries before starting the loop and it is running faster but i've found it's not updating the queries the way i'd hoped it would. I'm not sure if it's the first or second query that's not updating or if it's both as i've not had a chance to test it very much. Also the 30 second time i quoted was with it not updating correctly and the query it was reusing was only returning 4 records when these querys can return anything for 0-500 so it's not accurate.

    I'm going home for the weekend so i'll look forward to any insights on monday.

    [EDIT EDIT EDIT EDIT]

    I've got the form retrieving the data i want in the way i want and it's taking about a minute for typical selects which is toloratable but it'd be great to speed it up. If anyone has any ideas please let me know.

    The code now is:

    Code:
        Dim SQL, Regos(1000) As String
        Dim i, ii, iii As Integer
        Dim Test As Boolean
        Dim Rst1, Rst2 As DAO.Recordset
        
        txtdebug = ""
        For i = 0 To LstSVarieties.ListCount - 1
            DoEvents
            For ii = 0 To lstControls.ListCount - 1
                DoEvents
                SQL = "SELECT TraitCode FROM TraitLists WHERE TraitGroupID=""" & lstTraitGroups & """"
                Set Rst1 = CurrentDb.CreateQueryDef("", SQL).OpenRecordset
                Do While Not Rst1.EOF
                    DoEvents
                    SQL = "SELECT DISTINCT Samples.TrialRego, Samples.Testname " _
                        & "FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID " _
                        & "WHERE (Samples.Testname=""" & LstSVarieties.ItemData(i) & """ Or Samples.Testname=""" & lstControls.ItemData(ii) & """) AND " _
                        & "Results.DoneYet=True AND Results.TraitCode=""" & Rst1!TraitCode & """ AND Samples.SampleTypeID=1"
                    Set Rst2 = CurrentDb.CreateQueryDef("", SQL).OpenRecordset
                    iii = 0
                    Do While Not Rst2.EOF
                        DoEvents
                        Rst2.Filter = "TrialRego=" & Rst2!TrialRego
                        If Not Rst2.EOF Then
                            Rst2.MoveLast
                        End If
                        If Rst2.RecordCount <> 0 Then
                            Regos(iii) = Rst2!TrialRego
                            iii = iii + 1
                        End If
                        Rst2.Filter = ""
                        Rst2.MoveNext
                    Loop
                    txtdebug = txtdebug & LstSVarieties.ItemData(i) & " " & lstControls.ItemData(ii) & " " & Rst1!TraitCode & " " & iii + 1 & vbNewLine
                    Rst1.MoveNext
                Loop
            Next
        Next
        MsgBox "Done"
    Last edited by Access Junkie; 06-12-06 at 02:27.

  4. #4
    Join Date
    Jun 2006
    Posts
    72
    I had a look at the read me first sticky and i couldn't see anything about not doing bumps so i'll bumb this once and hope i don't annoy everyone too much

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Definetely do not create - delete - create - delete as that will balloon your DB out of control.

    Don't know if it is faster, but since you are using DAO, instead of:
    Set Rst1 = CurrentDb.CreateQueryDef("", SQL).OpenRecordset
    Use:
    Set Rst1 = CurrentDb.OpenRecordset(strSQL)

    Ditto for Rst2.

    Also, I would be careful using DoEvents. This is generally something you put in towards the end of development and it MAY actually cost you processing time since you are allowing the OS to do other things.

    What is your intention with this piece of code:
    Code:
                    Do While Not Rst2.EOF
                        DoEvents
                        Rst2.Filter = "TrialRego=" & Rst2!TrialRego
                        If Not Rst2.EOF Then
                            Rst2.MoveLast
                        End If
                        If Rst2.RecordCount <> 0 Then
                            Regos(iii) = Rst2!TrialRego
                            iii = iii + 1
                        End If
                        Rst2.Filter = ""
                        Rst2.MoveNext
                    Loop
    This appears to accomplish the same thing that a "total" query would. If you group by TrialRego and count TrialRego, the query only has to execute once per loop instead of once per record per loop.

    Overall, I'm not clear on what you are trying to do from the code and the explanations .... sorry for being thick!

    Can you break it down to a simple statement of your goal (you don't need to use all the control names)?

    I'll read this again later after a few glasses of wine - that may help!

    tc
    Last edited by tcace; 06-12-06 at 17:16.

  6. #6
    Join Date
    Jun 2006
    Posts
    72
    I think i got a little lost in my own codes actually. My intention with that code was basically what you said it was doing, I wanted to test if it would be faster to replace the second queries functionallity with code but it was quite slow.

    I've returned the code to how it was before trying that and fixed the updating problem (I just needed to populate the recordset using a movelast) and here is the code i'm using now.

    Code:
        Dim SQL As String
        Dim i, ii As Integer
        Dim Test As Boolean
        Dim Rst1, Rst2 As DAO.Recordset
        
        txtdebug = ""
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "RegoList" Then
                Test = True
                Exit For
            End If
        Next
        If Test = False Then
            CurrentDb.CreateQueryDef "RegoList", ""
        End If
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "CountRegos" Then
                Test = True
                Exit For
            End If
        Next
        If Test = False Then
            SQL = "SELECT First(Regolist.TrialRego) AS [TrialRego Field], Count(Regolist.TrialRego) AS NumberOfDups " _
                & "FROM Regolist " _
                & "GROUP BY Regolist.TrialRego " _
                & "HAVING Count(Regolist.TrialRego)>1"
            CurrentDb.CreateQueryDef "CountRegos", SQL
        End If
        SQL = "SELECT TraitCode FROM TraitLists WHERE TraitGroupID=""" & lstTraitGroups & """"
        Set Rst1 = CurrentDb.OpenRecordset(SQL)
        Do While Not Rst1.EOF
            DoEvents
            For i = 0 To LstSVarieties.ListCount - 1
                DoEvents
                For ii = 0 To lstControls.ListCount - 1
                    DoEvents
                    SQL = "SELECT DISTINCT Samples.TrialRego, Samples.Testname " _
                        & "FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID " _
                        & "WHERE (Samples.Testname=""" & LstSVarieties.ItemData(i) & """ Or Samples.Testname=""" & lstControls.ItemData(ii) & """) AND " _
                        & "Results.DoneYet=True AND Results.TraitCode=""" & Rst1!TraitCode & """ AND Samples.SampleTypeID=1"
                    CurrentDb.QueryDefs("RegoList").SQL = SQL
                    Set Rst2 = CurrentDb.OpenRecordset("CountRegos")
                    If Not Rst2.EOF Then
                        Rst2.MoveLast
                    End If
                    txtdebug = txtdebug & LstSVarieties.ItemData(i) & " " & lstControls.ItemData(ii) & " " & Rst1!TraitCode & " " & Rst2.RecordCount & vbNewLine
                Next
            Next
            Rst1.MoveNext
        Loop
        MsgBox "Done"
    This newest version is taking about 30 seconds to run through the same selection i've been using so a pretty vast improvement.

    As for using DAO i'm flexible on that and willing to learn ADO (that's the alternative right?). Does ADO have more functionallity or run faster than DAO?

    When I first started programming I'd commonly create infinate loops by mistake so I've made it a habit to include doevents in all my code now. I tried taking the doevents out of the code and it takes the time down to 25 seconds.

    The overall goal of this form is to output the values of trait tests for a variety compared to each of the controls only for trials that they are both in as well as the number of trials they are both in. Also after this is reported on i need to output catalogues(another report i've already got working) for each trial that was represented in this form's report. I realize this is probably alot to get your head around since it was for me lol so if this doesn't make sense i'm happy to explain further.
    Last edited by Access Junkie; 06-12-06 at 21:32.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    DAO is fine - I just think the OpenRecorset will be faster than then CreateQueryDef (but probably barely faster). Some will argue ADO is better - personally, use what you you know!

    As for the rest, I'll have to look tomorrow!

    tc

  8. #8
    Join Date
    Jun 2006
    Posts
    72
    Ok i've got the form doing exactly what I wanted it to do(not as fast as i'd like).

    The same selections i've been making since i started is taking 37 seconds but a more likely typical selection is taking 58 seconds.

    The line that takes all the time is seperated by 4 new lines in the code and is:
    Set Rst3 = CurrentDb.OpenRecordset("CountRegos")

    I'd like to use Rst3.Requery instead and simply set rst3 once before the loops which should work since the query behind it doesn't change but doing it that way only returns a maximum of two records for some reason.

    Here is the code with comments to make it easier to follow.

    Code:
        Dim SQL As String
        Dim i, ii, iii As Integer
        Dim Test As Boolean
        Dim Rst1, Rst2, Rst3, Rst4 As DAO.Recordset
        Dim Tbl As DAO.TableDef
        Dim LastTime, TotalTime As Variant
    
        'Create table and add column headings
        Set Tbl = CurrentDb.CreateTableDef("PairedComps")
        Tbl.Fields.Append Tbl.CreateField("Trait", dbText)
        Tbl.Fields.Append Tbl.CreateField("Variety", dbText)
        For i = 0 To lstControls.ListCount - 1
            DoEvents
            Tbl.Fields.Append Tbl.CreateField(lstControls.ItemData(i), dbText)
            Tbl.Fields.Append Tbl.CreateField(lstControls.ItemData(i) & "Variety", dbText)
            Tbl.Fields.Append Tbl.CreateField(lstControls.ItemData(i) & "Trials", dbText)
        Next
        'Delete the table if it already exists
        For i = 0 To CurrentDb.TableDefs.Count - 1
            DoEvents
            If CurrentDb.TableDefs(i).Name = "PairedComps" Then
                DoCmd.DeleteObject acTable, "PairedComps"
                Exit For
            End If
        Next
        'Append table to the database
        CurrentDb.TableDefs.Append Tbl
        
        Set Rst1 = CurrentDb.OpenRecordset("PairedComps")
        
        'Test if the query RegoList exists
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "RegoList" Then
                Test = True
                Exit For
            End If
        Next
        
        'If RegoList doesn't exist create it
        If Test = False Then
            CurrentDb.CreateQueryDef "RegoList", "SELECT *"
        End If
        
        'Test if the query CountRegos exits
        Test = False
        For i = 0 To CurrentDb.QueryDefs.Count - 1
            DoEvents
            If CurrentDb.QueryDefs(i).Name = "CountRegos" Then
                Test = True
                Exit For
            End If
        Next
        
        'if CountRegos doesn't exist create it
        If Test = False Then
            SQL = "SELECT First(Regolist.TrialRego) AS [TrialRego Field], Count(Regolist.TrialRego) AS NumberOfDups " _
                & "FROM Regolist " _
                & "GROUP BY Regolist.TrialRego " _
                & "HAVING Count(Regolist.TrialRego)>1"
            CurrentDb.CreateQueryDef "CountRegos", SQL
        End If
        
        'iii is used to count how many loop iterations have been completed
        iii = 0
        For i = 0 To LstSVarieties.ListCount - 1
            DoEvents
            'Retrieve all traits and their roundto values for the selected trait group
            SQL = "SELECT TraitLists.TraitCode, Traits.RoundTo " _
                & "FROM Traits INNER JOIN TraitLists ON Traits.TraitCode = TraitLists.TraitCode " _
                & "WHERE TraitGroupID=""" & lstTraitGroups & """ " _
                & "ORDER BY TraitLists.Order"
            Set Rst2 = CurrentDb.OpenRecordset(SQL)
            Do While Not Rst2.EOF
                DoEvents
                'Add a new record to the PairedComps table
                Rst1.AddNew
                Rst1!Variety = LstSVarieties.ItemData(i)
                Rst1!Trait = Rst2!TraitCode
                For ii = 0 To lstControls.ListCount - 1
                    DoEvents
                    'First iteration so increment iii
                    iii = iii + 1
                    'Output iii/total iterations required and the % completed
                    Me.Caption = iii & "/" & Rst2.RecordCount * LstSVarieties.ListCount * lstControls.ListCount & " " & Round((iii / (Rst2.RecordCount * LstSVarieties.ListCount * lstControls.ListCount)) * 100, 0) & "% Complete"
                    If LastTime <> 0 Then
                        'Calculate the total time taken so far
                        TotalTime = TotalTime + (Now - LastTime)
                        'Output the estimated time left
                        Me.Caption = Me.Caption & ". Estimated time left " & Format((TotalTime / iii) * ((Rst2.RecordCount * LstSVarieties.ListCount * lstControls.ListCount) - iii), "HH:MM:SS")
                    End If
                    LastTime = Now
                    'Select Regos and testnames for the current trait/variety/control
                    SQL = "SELECT DISTINCT Samples.TrialRego, Samples.Testname " _
                        & "FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID " _
                        & "WHERE (Samples.Testname=""" & LstSVarieties.ItemData(i) & """ Or Samples.Testname=""" & lstControls.ItemData(ii) & """) AND " _
                        & "Results.DoneYet=True AND Results.TraitCode=""" & Rst2!TraitCode & """ AND Samples.SampleTypeID=1"
                    'Update RegoList
                    CurrentDb.QueryDefs("RegoList").SQL = SQL
    
    
    
    
                    'Since Regolist has changed CountRegos needs to change
                    'i'd like to use rst3.requery as that runs about 5x faster
                    'here and do the set once at before the loops but it only
                    'returns a maximum of two records when done that way for
                    'some reason.
                    'This is the line that takes the most time
                    Set Rst3 = CurrentDb.OpenRecordset("CountRegos")
    
    
    
    
                    'Construct a crosstab query using the rego list from rst3
                    'that gives the average trait results
                    SQL = "TRANSFORM Avg(Results.Results) AS AvgOfResults " _
                        & "SELECT Results.TraitCode " _
                        & "FROM Samples INNER JOIN Results ON Samples.SampIDBarcode = Results.SampleID " _
                        & "WHERE Samples.SampleTypeID=1 AND Results.TraitCode=""" & Rst2!TraitCode & """ AND (Samples.Testname=""" & lstControls.ItemData(ii) & """ or Samples.Testname=""" & LstSVarieties.ItemData(i) & """) AND ("
                    Do While Not Rst3.EOF
                        DoEvents
                        SQL = SQL & "Samples.TrialRego=""" & Rst3(0) & """ OR "
                        Rst3.MoveNext
                    Loop
                    If Rst3.RecordCount <> 0 Then
                        SQL = Left(SQL, Len(SQL) - 4) & ") " _
                        & "GROUP BY Results.TraitCode " _
                        & "PIVOT Samples.Testname"
                        Set Rst4 = CurrentDb.OpenRecordset(SQL)
                        'If a roundto value has been specified use it
                        If Not IsNull(Rst2!roundto) Then
                            'Store the average results in the pairedcomps table
                            Rst1(lstControls.ItemData(ii)) = Round(Rst4(lstControls.ItemData(ii)), Rst2!roundto)
                            Rst1(lstControls.ItemData(ii) & "Variety") = Round(Rst4(LstSVarieties.ItemData(i)), Rst2!roundto)
                        'Otherwise round to one decimal place.
                        Else
                            'Store the average results in the pairedcomps table
                            Rst1(lstControls.ItemData(ii)) = Round(Rst4(lstControls.ItemData(ii)), 1)
                            Rst1(lstControls.ItemData(ii) & "Variety") = Round(Rst4(LstSVarieties.ItemData(i)), 1)
                        End If
                    End If
                    'Store the number of common trials in pairedcomps table
                    Rst1(lstControls.ItemData(ii) & "Trials") = Rst3.RecordCount
                Next
                'Update the pairedcomps table
                Rst1.Update
                'next trait
                Rst2.MoveNext
            Loop
        Next
        MsgBox "Done completed in " & Format(TotalTime, "HH:MM:SS")
    Last edited by Access Junkie; 06-13-06 at 04:17.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Can you zip and attach, perhaps with a scaled down data table?

    thanks,
    tc

  10. #10
    Join Date
    Jun 2006
    Posts
    72
    Unfortunately that would be too difficualt, the form itself has it's finger in most of the tables in the database and the database itself takes up 40+MBs. There's also a problem with it being the Interlectual Property of where i work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •