Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with query speed (XP)

    This query functions exactly as I would like it too, however works wwaayy too slow (about an hour!) because of the size of my database and since the backend is on a network drive. Does anyone know another method of doing this? Would splitting it into 2 separate queries speed it up or slow it down? Any other methods for performing this?

    Here's the part that takes forever:
    <code>SELECT carrier_name, state, phone, fax, DatID FROM testCompany_Import WHERE (testCompany_Import.DatID) IN (SELECT DatCompNumber FROM tblDatHistory WHERE DatCompNumber NOT IN (SELECT DatID FROM tblCompany_Information))</code>


    The query works fast if I chop it down to this (not the info I need though):
    <code>SELECT carrier_name, state, phone, fax, DatID FROM testCompany_Import WHERE (testCompany_Import.DatID) IN (SELECT DatCompNumber FROM tblDatHistory)</code>


    testCompany_Import - 20K records
    tblDatHistory - 100K records (and growing 5-10K each day)
    tblCompany_Information - 17K records


    FULL CODE:
    <code>
    Dim strTable As String
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim Conn As ADODB.Connection
    Dim rCount As Integer

    Set Conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    strTable = "tblCompany_Information"
    strSQL = "SELECT carrier_name, state, phone, fax, DatID FROM testCompany_Import WHERE (testCompany_Import.DatID) IN (SELECT DatCompNumber FROM tblDatHistory WHERE DatCompNumber NOT IN (SELECT DatID FROM tblCompany_Information))"

    rst.Open strSQL, CurrentProject.Connection

    DoCmd.SetWarnings False
    rst.MoveFirst
    rCount = 0
    Do
    DoCmd.RunSQL "INSERT INTO tblCompany_Information (carrier_name, state, phone, fax, DatID) VALUES ('" & fFixApostrophe(rst.Fields(0)) & "','" & rst.Fields(1) & "','" & rst.Fields(2) & "','" & rst.Fields(3) & "','" & rst.Fields(4) & "')"
    rst.MoveNext
    rCount = rCount + 1
    Loop Until rst.EOF
    DoCmd.SetWarnings True

    MsgBox "" & rCount & " Records Imported."

    rst.Close
    Conn.Close
    Set rst = Nothing
    Set Conn = Nothing
    </code>


    Thanks,
    Ryan

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Help with query speed (XP)

    Subqueries take forever, so try the following:

    1. Create a query based upon testCompany_Import

    2. Create a query based upon DatCompNumber joining table via a LEFT JOIN on table tblCompany_Information

    3. Create a query based upon queries 1 and 2 above

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with query speed (XP)

    IN is one of the very fast operators in SQL but NOT IN is one of the slowest. This is a place where subqueries won't work as well as joins on separate queries or virtual tables.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with query speed (XP)

    Thanks for the replies! I looked up the join statement and have come up with this:

    <code>
    SELECT tblCompany_Information.DatID, tblDatHistory.DatCompNumber
    FROM tblCompany_Information LEFT JOIN tblDatHistory ON tblCompany_Information.DatID = tblDatHistory.DatCompNumber
    </code>


    Unfortunately, I can't figure out how to get it to select the data I am after. Right now it shows the records that are in both tables. I know I'm missing some thing simple here but I seem to have hit a wall <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Thanks again!
    Ryan

    Edit:
    Cancel that, I think I almost have it working now. I was requesting data from the exsiting company table rather than the newer imported company table.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with query speed (XP)

    Still haven't got this fixed yet [img]/forums/images/smilies/sad.gif[/img]. I haven't made much progress since last post either:
    <code>
    SELECT DISTINCT testCompany_Import.DatID, tblDatHistory.DatCompNumber
    FROM testCompany_Import LEFT JOIN tblDatHistory ON testCompany_Import.DatID = tblDatHistory.DatCompNumber:
    </code>
    I know this is only querying two table because I can't figure out how to add a third to it. I need it to show me the records from testCompany_Import when they have a match in tblDatHistory and are not already in tblCompany_Information.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Help with query speed (XP)

    Your query that you show should be one query, named say Q1.
    Build another query:
    SELECT * FROM Q1 LEFT JOIN tblCompanyInformation ON Q1.DatID = tblCompanyInformation.yourID
    WHERE tblCompanyInformation.yourID is Null

    Change yourID to the ID in table tblCompanyInformation

  7. #7
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with query speed (XP)

    Thanks Pat!!!

    That's exactly what I was missing. Everything seems to work fine now, import time reduced from multiple hours to about 1-2 minutes!

    Thanks again!

    Ryan

Posting Permissions

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