Solved

Speed up Select Top n... Query

Posted on 2017-05-17
9
33 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
[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
  • 3
  • 2
9 Comments
 
LVL 34

Expert Comment

by:ste5an
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)
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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

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

Author Comment

by:Merlin-Eng
@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 34

Expert Comment

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

Author Comment

by:Merlin-Eng
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 34

Accepted Solution

by:
ste5an earned 500 total points
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
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Join & Write a Comment

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

737 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