Speed up Select Top n... Query

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
Merlin-EngWorks ManagerAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Rey Obrero (Capricorn1)Commented:
test this queries

SELECT Min(JobNo)  From Jobs WHERE JobNo > 193574


SELECT Max(JobNo)  From Jobs WHERE JobNo  < 193574
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Merlin-EngWorks ManagerAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
did you try the queries I posted?
0
 
Merlin-EngWorks ManagerAuthor Commented:
@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
 
ste5anSenior DeveloperCommented:
It writes a file SHOWPLAN.OUT either in the current (database file) folder or documents folder.
0
 
Merlin-EngWorks ManagerAuthor Commented:
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
 
Merlin-EngWorks ManagerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.