[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Speed up Select Top n... Query

Posted on 2017-05-17
9
Medium Priority
?
108 Views
Last Modified: 2017-05-17
I have table called Jobs. It has approx 200,000 records. The Primary Key field is JobNo. I have ascending and descending indexes on this field. For any given JobNo I need to be able to quickly find the next or previous JobNo's. So to find the next JobNo i use the following query:

SELECT TOP 1 JobNo  From Jobs WHERE JobNo > 193574 ORDER BY JobNo

This executes instantly and gives the correct result. To find the previous record, I am using this:

SELECT TOP 1 JobNo  From Jobs WHERE ((JobNo<193574)) ORDER BY JobNo Desc

This takes 10 seconds to open even though the JobNo field has a descending Index on it. I am thinking that Access is finding the PrimaryKey ascending index and doesn't look any further. So it doesn't find the descending Index. Can anyone suggest a way I can speed this up at all?

Ian
0
Comment
Question by:Merlin-Eng
  • 4
  • 3
  • 2
9 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 42138720
You did already what is necessary to optimize your query. So maybe a compact and repair helps..

Post the execution plan..

JetShowPlan, use the correct registry branch:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
 "JETSHOWPLAN"="ON"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 42138736
test this queries

SELECT Min(JobNo)  From Jobs WHERE JobNo > 193574


SELECT Max(JobNo)  From Jobs WHERE JobNo  < 193574
0
 

Author Comment

by:Merlin-Eng
ID: 42138788
That registry setting applies to 32bit installations. I found the correct setting for my 64bit system. I believe I got set, but how do I actually view the execution plan?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 42138791
did you try the queries I posted?
0
 

Author Comment

by:Merlin-Eng
ID: 42138802
@Rey Obrero:

Instantly:  SELECT Min(JobNo)  From Jobs WHERE JobNo > 193574

10 Secs: SELECT Max(JobNo)  From Jobs WHERE JobNo  < 193574

So it's the same behaviour.
0
 
LVL 36

Expert Comment

by:ste5an
ID: 42138838
It writes a file SHOWPLAN.OUT either in the current (database file) folder or documents folder.
0
 

Author Comment

by:Merlin-Eng
ID: 42138849
Ok. I found it now thanks. Here's what it gave me:


--- Query1 ---

- Inputs to Query -
Table 'JOBS'
    Database '\\avatar\jobscopy\cauldron.accdb'
- End inputs to Query -

01) Restrict rows of table JOBS
      using rushmore
      for expression "Jobs.JobNo<193574"
02) Sort result of '01)'
03) Compute Top of result of '02)'
0
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 42138863
The "using rushmore" indicates that it already uses it most effective algorithm to collect the data. So there is nothing you can do about this query.

Just check your indices again and run a compact and repair. And post a concise and complete sample file.
0
 

Author Closing Comment

by:Merlin-Eng
ID: 42138998
I compacted and repaired the front and back end databases and I decided to re-create the linked tablef object for the jobs table..... It fixed the problem.  So thank you for your help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

830 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