Performance issue SELECT TOP

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hello
I have an Access database with table "Lines" with 200000 rows
When I run the query:
"SELECT TOP 100 * FROM Lines"
it flies....

However
"SELECT TOP 100 * FROM Lines ORDER BY LineNumber"
takes ages.
There is an index on LineNumber, so why does this make a difference?
How can I persuade SQL to use my index.

Tried the same on MSDE... There it is also slow. Query analyser tells me it
spends most of its time sorting the table (could have guessed that one...).

Suggestions are very welcome.
Peter.
 
In the first query, it just grabs the first 100 records and stops.

In the second query, it has to sort all 200000 rows and then grab the first 100
and stop.

As far as I know, there is no way for a top query to grab the "first 100" from
the index of a field. It would be an interesting problem to work out -
especially considering that the sort could be multiple levels, ascending vs
descending, etc.

In a normal SELECT (without the top clause) a WHERE clause (if it exists) is
executed before the Order By clause. A TOP with no criteria is something like
the reverse of this. ALL the records are selected, then sorted, then the
"where" (the Top Clause) is applied.
 
You could try something silly like:


select * from tblLines where id in (select top 100 id from tblLines)
order by LineNumber
 
Hello Albert,
This goes fast indeed, however it first selects the first 100 and then
performs the sort. That's not what I want.
Peter
 
Dear John,
Thanks for your reply.
Exactly as you said, the sorting does not take the index into account, which
is a silly thing from a performance point af view.
If you leave out the order by clause the sorting is by default that of the
primary index. Unfortunately, if one had multiple indexes, setting another
one as primary seems to rebuild the indexes.

Concerning the part about the where clause:
In fact the actual query reads:
"SELECT TOP 100 * FROM Lines ORDER BY LineNumber WHERE HeaderNumber = 56"
and the primary index is on HeaderNumber , LineNumber .
But in my question I simplified this to show the core of the problem.
Problem remains the same.
What I'm seeking to find is a generic paging algorithm for browsing very
large tables. In the good old day (in another millenium) VB6 with Jet
OpenTable the 'percentposition' property helped us. Although not really
accurate, (If was in earlier DAO releases but somehow lost accuracy over the
versions..) if gave a nice indication.
In VB.NET one tries to work with the OLEDB interface (as recommended). Works
fine, but not with larger tables expecially over the network.

The struggle continues.
Peter


----- Original Message -----
From: "John Spencer (MVP)" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Monday, August 09, 2004 4:15 PM
Subject: Re: Performance issue SELECT TOP
 
Back
Top