Solved

slow query with between operators

Posted on 2014-01-08
10
365 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 38

Expert Comment

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

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 51

Expert Comment

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

/gustav
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

630 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