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
Solved

slow query with between operators

Posted on 2014-01-08
10
357 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 35

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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