Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

slow query with between operators

Posted on 2014-01-08
10
Medium Priority
?
369 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 40

Expert Comment

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

Accepted Solution

by:
Gustav Brock earned 2000 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 52

Expert Comment

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

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

971 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