Query: Join numbers from one table and ranges from other table RRS feed

  • Question

  • Table1:

    Fld_FROM Fld_TO
      1        3
      5       11
     14       26
     77       91
    100      101

    Query result:
     2  1  3
     5  5 11
    87 77 91

    I created such queries:

    SELECT Table1.Fld, Table2.Fld_FROM, Table2.Fld_TO
    FROM Table1, Table2
    WHERE (Table1.Fld Between Table2.Fld_FROM And Table2.Fld_TO)=True;

    SELECT Table1.Fld, Table2.Fld_FROM, Table2.Fld_TO
    FROM Table1 INNER JOIN Table2 ON (Table1.Fld Between Table2.Fld_FROM And Table2.Fld_TO);

    Both queries work, but they are very slow.
    • Edited by _DK Wednesday, April 20, 2016 8:30 PM
    Wednesday, April 20, 2016 8:26 PM


All replies

  • >>>Both queries work, but they are very slow.

    According to your description, how slow is your query? I have made a sample to try to reproduce this issue, unfortunately, I can't. So I suggest that you could copy your database file to an other machine or compare and repair it, then try to resolve this issue.

    In addition your query can modify like below:
    SELECT Table1.Fld, Table2.Fld_FROM, Table2.Fld_TO
    FROM Table1, Table2
    where Table1.Fld between  Table2.Fld_FROM and Table2.Fld_TO

    Thursday, April 21, 2016 5:43 AM
  • Few minutes. There is nothing to repair, it works very well, but slow. Table1 contains almost 1756 records, Table2 162483 records. Perhaps your tables are significantly smaller. Maybe VBA with synchronized rs.MoveNext in two recordsets would be faster.
    • Edited by _DK Thursday, April 21, 2016 10:35 AM
    Thursday, April 21, 2016 10:34 AM
  • I believe you'll find it much faster to run if you use math compares:

    SELECT Table1.Field1, Table2.FieldFrom, Table2.FieldTo
    FROM Table1 INNER JOIN Table2 ON Table1.Field1 >= Table2.FieldFrom AND Table1.Field1 <= Table2.FieldTo;

    Thursday, April 21, 2016 10:40 PM
  • Thanks but it is not faster. Maybe I will try VBA in one - few days.
    Thursday, April 21, 2016 11:22 PM
  • Hi, _DK

    In a cross join, each row from one table is combined with each row from another table, the result set contains 1756*162483 rows (Table1 has 1756 rows and Table2 has 162483; 1756 multiplied by 
    162483 equals 1756*162483).

    In addition any time you run a query that has tables that are not explicitly joined, a cross join is the result. So I think that this reason cause this issue. 

    For more information, click here to refer about Join tables and queries

    Friday, April 22, 2016 4:43 AM
  • Can you literally reduce your db to just two tables with one or two fielda each, and post it in a public place like a free OneDrive account? That way we can run some tests with realistic record counts to see if we can beat your "few minutes" performance. My bet is on a Tally table, but I won't know until I get my hands on your db.

    -Tom. Microsoft Access MVP

    Friday, April 22, 2016 4:50 AM
  • VBA is quite fast (~2s):

    Private Sub FindRange()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Const tabela As String = "logs"
    Set db = CurrentDb()
    db.Execute "UPDATE Table1 SET [Fld Name1] = Null;"
    Set rs1 = db.OpenRecordset("SELECT * FROM Table1 ORDER BY Fld;", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("SELECT * FROM Table2 ORDER BY Fld_FROM, Fld_TO;", dbOpenSnapshot)
    If rs1.EOF = False And rs2.EOF = False Then
        Do Until rs1.EOF
            Do Until rs1("Fld") <= rs2("Fld_TO")
                If rs2.EOF Then GoTo end_Label
            If rs1("Fld") >= rs2("Fld_FROM") Then
                rs1("Fld Name1") = rs2("Fld Name2")
            Else: rs1("Fld Name1") = "error"
            End If
    End If
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
    End Sub

    This code works provided, that ranges do not overlap each other. And both queries must be sorted the same way.
    • Edited by _DK Friday, April 22, 2016 4:48 PM
    Friday, April 22, 2016 4:43 PM
  • Tom – I am far from being a SQL expert, which is why I went hunting on the internet for “Tally Table”, to find the magic “iterations using set theory”. There are many “explanations” but I’m dense, and reading SQL Server code (or Access SQL code, for that matter) doesn’t help. Do you have a small Access database that would demo the principle? Or an article you recommend? Assume you're starting at 0.

    peter n roth -, Maybe some useful stuff

    Saturday, April 23, 2016 7:33 PM
  • Sorry, no I don't, but if you do what I asked (post your db, stripped to the bare essentials), I'm willing to invest a bit of time, just for fun. As I said, this is just a hunch.

    -Tom. Microsoft Access MVP

    Saturday, April 23, 2016 7:44 PM
  • Well, I wasn’t the OP, so I don’t have a database stripped or otherwise. I was looking for the recursive principle.

    peter n roth -, Maybe some useful stuff

    Saturday, April 23, 2016 7:53 PM
  • joined record set query will always be faster than a vba loop approach.

    in my small test universe the solution I posted provides a return in the blink of an eye....  and so the question may be horse power when it comes to 164k records....where there is a <= AND >=  type join there is a bit of crunching to do...

    you should trial this in a non split scenario; will it can be split but put both Front and Back on the same PC; and ideally a PC with a healthy amount of RAM ...say 6G or 8G or more...   then I believe the calculation time should be more acceptable.  This will at least establish your best case baseline.  Then move the BE to its correct location and see if the network is introducing a significant portion of the delay.

    Monday, April 25, 2016 12:47 PM
  • In this case VBA is fastests. There are many types of queries - maybe one of them would be more applicable than posted above |?
    Monday, April 25, 2016 1:51 PM
  • Hi, _DK

    I think that there is no type of query that is more applicable than you posted above.
    Tuesday, April 26, 2016 8:04 AM