Solved

slow query with between operators

Posted on 2014-01-08
10
362 Views
Last Modified: 2014-01-18
Hello,

Here is a query that we use to popluate a combobox.  It takes forever to run.  About 12.7 seconds.  There are 25,000 rows in tblJobs, and 390,000 rows in the tbljobsLineItems.  All fields with criteria have index, as well as both sides of the join.  Linked tables are on another server.

I tried running as query and DAO code, same speed both ways.

Public Function RefreshPullDown()
Dim x As Single
Dim sSQL As String
Dim Starting_Date As Date, Ending_Date As Date

Starting_Date = [Forms]![frmjobs]![SetCall]
Ending_Date = [Forms]![frmjobs]![fldRTSCall]
  ' xT = 0
   x = Timer
     
    Dim db As Database

    Set db = CurrentDb()
    Dim rs As dao.Recordset
    
    
        CurrentDb.Execute "DELETE tmpRaItemsInUseTotals.* FROM tmpRaItemsInUseTotals;"

Debug.Print (Timer - x); "delete secs"
       
        
  
       sSQL = "SELECT DISTINCTROW tblJobsLineItems.[Item Number], Sum(" & _
                "tblJobsLineItems.Quantity) AS SumOfQuantity " & _
                "FROM tblJobs " & _
                "INNER JOIN tblJobsLineItems ON tblJobs.Job_Num = " & _
                "tblJobsLineItems.Job_Num " & _
                "WHERE (((tblJobs.Confirmed)<>'no') AND ((tblJobs.SetCall) " & _
                "Between #" & Starting_Date & "# And #" & Ending_Date & "#)) OR" & _
                " (((tblJobs.Confirmed)<>'no') AND ((tblJobs.fldRTSCall) " & _
                "Between #" & Starting_Date & "# And #" & Ending_Date & "#)) OR " & _
                "(((tblJobs.Confirmed)<>'no') AND ((tblJobs.SetCall)<#" & _
                "" & Starting_Date & "#) AND ((tblJobs.fldRTSCall)>#" & _
                "" & Ending_Date & "#)) " & _
                "GROUP BY tblJobsLineItems.[Item Number];"
                
       ' Debug.Print sSQL
       ' Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
  
       '   Do While Not rs.EOF
       '     Debug.Print rs![Item Number]; rs![SumOfQuantity]
       '     rs.MoveNext
       ' Loop
        
       ' rs.Close
        Set db = Nothing
        
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "raItemsInUseTotalsAppend"
    DoCmd.SetWarnings True

    
Debug.Print (Timer - x); "secs"

xT = xT + (Timer - x)
Debug.Print (xT); "t secs"

End Function

Open in new window


Any thoughts?

Press
0
Comment
Question by:pressMac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39766282
see the article by Allen Brown when loading thousand items to a combo box

http://allenbrowne.com/ser-32.html

hope this helps
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39766351
how many items do you actually want to load into the combobox?
if it's more than 50, it will be a nightmare for the users, so you should change the GUI design in that regards...
0
 

Author Comment

by:pressMac
ID: 39766501
The return set is only around 5 to 30 or records that populate the combobox.  it is 12 seconds just to run this one query.  The query appends a tmpTable, usually about 200 records, and then that is joined with another table, and filtered.  Result to combobox is about 5 to 30 records.  The join and filling the combobox is practically instant.

does that clarfy?

Preston
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39766523
yes. still I have seen your described behavior a couple of times already...
0
 

Author Comment

by:pressMac
ID: 39767133
So, it is way slow before it hits the combo box.  I am looking for a more efficient query structure.

Press
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39767169
Have you tried adding indexes to the date fields?
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39767459
First, DISTINCTROW is not needed when you Group By.

Second, you could try moving table Job and all its filtering into a separate query, then join that to the other table.

Third, as noted, double-check the indexing.

/gustav
0
 

Author Comment

by:pressMac
ID: 39791133
On Gustavs recommendation, I put tbljobs in a sub query to narrow data in advance.  Blazing fast now.  .2 seconds to populate box

Press
0
 

Author Closing Comment

by:pressMac
ID: 39791134
Worked great, actually used same  query in other parts of app with great results
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39791256
Thanks for the feedback.

/gustav
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question