Solved

slow query with between operators

Posted on 2014-01-08
10
353 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 142

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
 
LVL 142

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

Expert Comment

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

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 49

Expert Comment

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

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now