physically sort records in a table

[John Vinson]

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in order
to speed response.

I do so, very frequently.

I use Access JET B-Tree indexes - which implement a VERY efficient
binary search - on any fields which are to be used for searching or
sorting.

Doing so is MUCH more efficient than sorting the table and writing
your own VBA code to try to beat Jet's search engine.

John W. Vinson[MVP]

 

[Albert D.Kallal]

How can you say a "table has no order" when an autoincrement column added
to
the table always numbers the rows in the same sequence - that is, the
sequence the rows were entered in.

Yes, the auto increment will increase by a number for each record. However,
that number is COMPLIRY different then the physical order of the records.
The autonumber order is NOT related to the order that records are returned
in if you open a table. Often, they are the same order, but this is only
LUCK of the draw!!



In other words, if you write out 4 records to disk (or you enter 4 records
to disk, THERE IS NOT GARENTEE THAT THESE records will be returned in that
order. EVEN WHEN YOU HAVE A AUTONUMBER field, the records STILL ARE NOT
retuned in the order you entered them. (most of the time they are..but not
always).



In addition, if you have 4 people entering records, then order of entry is
often not much use. What good is to look at the last 3 records, when they
might have been entered by 3 different people? (Likely, each of those
individuals might need to see their last records.but the physical order is
not much use.is it?)



So, the ONLY WAY to ensure order is to FORCE THE ORDER. Simply put, base the
form, or report, or sub-form on a query THAT SETS THE ORDER. And, a good
field to to use to set that order is the autonumber field, since it does
increase by one each time. However, DO NOT CONFUSE the order of the
autonumber field with that of the order records placed on the disk
drive...as they are NOT THE same.



So, to answer your question, simply use a auto number field, and base the
form (or whatever) on a query that sets the order by the auto number field.
You will NEVER have to set the order, NEVER have to export and NEVER has to
sort the form in question if you do this.



However, using this query will set the order for you, but it will NOT change
the physical order of the records on the disk drive, and as mentioned, in a
database system, the data is un-ordered...it is YOUR CHOICE to set the way
you want the data engine to return the order..but there is NO physical order
that is *related* to the actual order of data entry....

What you seem to be saying is that Access does not give me any direct way
to
control the "natural" sequence that it uses for some operations. If true,
that is disappointing.

No, what they are saying that if you need a order, YOU must specify it. The
most common order is by the autonubmer, and the other possible is by a
timestamp field. However, you can NOT relay on the order of entry, as it is
not consistent.

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in
order
to speed response.

True, but then we also have balanced btrees, and they are going to work MUCH
faster then if you hand code the retrieval of records anyway. And, you
likely can't write code any faster then want the optimized indexing systems
that are built in anyway.

If I can't control the physical sequence of rows in the
table and had to use a logical view to get the records into sequence, the
system could be forced to do literally ten to one hundred times the disk
accesses to find my data compared to accessing a well-ordered physical
set.

That is true. Sql server does have a concept of cluster indexes, and this
does force data to be grouped together on the disk drive (it don't force
overall order.but does group data together). So, clustered indexes would not
help the above.

So, there are definately times when you need to be able to "rely" on a
table
to be in the order you set it in - or pay a huge price in other areas.
"Unordered buckets of data" are fine for simple, low-usage applications,
but
it is a pity if that is the only option Access provides. I'm still hoping
someone else will tell me how to control physical order in the cases where
it
is important.

I don't believe that Oracle, sql-server, Mysql, Sybase, or ANY of the major
database vendors have anything different that that of a bunch of buckets of
data. So, every major database system in the marketplace today works just
like ms-access does. So, what people here are trying to point out is that a
database system DOES NOT force order, nor does it retain some special
ordering of data on the disk for you. YOU must tell the data engine HOW you
want the data back.



However, having said the above, for optimizing purposes, when you do a
compact and repair in ms-access, the data is *physically* ordered BY primary
key. So, you can do a compact and repair to set the records in primary key
order. So, you could thus make the autonumber the primary key (which is
often the case), and when you compact, your data would be in order. However,
as others mentioned, you can NOT rely on this table order, but STILL MUST
use a query to set the order. The reason for this is the INSTACNT you edit,
delete, or even add a record, that record may not be OUT of physical order.



So, you do have the ability to order the data by simply compacting the
database. However, your code, your forms, your data processing routines,
your export routines, virtually everything you do STILL MUST set the order.
In other words, as all posters here said, you MUST set and define the order
in which you want the data engine to retrieve data....you can NOT leave this
issue up in the air.....



This is an important concept to grasp, since you might be retrieving data
from your desktop database, or a large corporate system in which 100's of
records may have been added between you adding ONE record. So, in today's
world, order of data is NOT maintained due to practical design issues that
make the whole concept a moot point. In the old days, we have punched cards,
or even magnetic tapes, or sequential files on a disk drive that DID IN FACT
preserve order. However, these systems were NOT muti-user. So, in modern
database systems, there is no order!!. There is several reasons for this,
once of which in fact is that today records are VARAIBLE length. You can't
know that the 5th record is going to resolve to a particular spot on the
disk drive (in old systems, like old FoxPro, or even earlier dbaseIII
systems, records were fixed length, and you could calculate the 5th record
by a offset from the START of the file on the disk drive by going:



Record position = Record Number X record size



Today, that file, or data might not actually even exists on your computer,
but perhaps on a corporate data server half way around the world (with 500
other people using it). The physical order is not of your concern. IF you
need a particular order of data, then you MUST DESING this concept into your
appcation. So, about 15, or 20 years ago, data order was preserved in most
systems, but the brave new world of client to server changed all this. This
is as much a conceptual topic as it simply means you can't approach data
like you did with FORTARN or even simply text files to store data.



Anyway, doing a compact an repair would put your data in the same order as
the primary key, and obviously a query that asks for this order would be
speeded up substantially in this case. So, do remember that you can by
compacting force a physical order, but you still have to use that query to
reliably return those records in that order.



And, since an auto number is only an internal number, you can't expose this
number to your users, since when you export, or move the data, it has no
meaning. Further, due to canceled additions, often the auto number will have
gaps in it.



So, you *CAN* use the auto number to set the order of data, but you can't
give the auto number meaning, such as making it a customer number etc.



The above advice applies to ANY of the major database systems that you will
use.......get used to it.....

 

[Albert D.Kallal]

How can you say a "table has no order" when an autoincrement column added
to
the table always numbers the rows in the same sequence - that is, the
sequence the rows were entered in.

Yes, the auto increment will increase by a number for each record. However,
that number is COMPLIRY different then the physical order of the records.
The autonumber order is NOT related to the order that records are returned
in if you open a table. Often, they are the same order, but this is only
LUCK of the draw!!



In other words, if you write out 4 records to disk (or you enter 4 records
to disk, THERE IS NOT GARENTEE THAT THESE records will be returned in that
order. EVEN WHEN YOU HAVE A AUTONUMBER field, the records STILL ARE NOT
retuned in the order you entered them. (most of the time they are..but not
always).



In addition, if you have 4 people entering records, then order of entry is
often not much use. What good is to look at the last 3 records, when they
might have been entered by 3 different people? (Likely, each of those
individuals might need to see their last records.but the physical order is
not much use.is it?)



So, the ONLY WAY to ensure order is to FORCE THE ORDER. Simply put, base the
form, or report, or sub-form on a query THAT SETS THE ORDER. And, a good
field to to use to set that order is the autonumber field, since it does
increase by one each time. However, DO NOT CONFUSE the order of the
autonumber field with that of the order records placed on the disk
drive...as they are NOT THE same.



So, to answer your question, simply use a auto number field, and base the
form (or whatever) on a query that sets the order by the auto number field.
You will NEVER have to set the order, NEVER have to export and NEVER has to
sort the form in question if you do this.



However, using this query will set the order for you, but it will NOT change
the physical order of the records on the disk drive, and as mentioned, in a
database system, the data is un-ordered...it is YOUR CHOICE to set the way
you want the data engine to return the order..but there is NO physical order
that is *related* to the actual order of data entry....

What you seem to be saying is that Access does not give me any direct way
to
control the "natural" sequence that it uses for some operations. If true,
that is disappointing.

No, what they are saying that if you need a order, YOU must specify it. The
most common order is by the autonubmer, and the other possible is by a
timestamp field. However, you can NOT relay on the order of entry, as it is
not consistent.

Imagine you had a reference table that was going to be accessed tens of
thousands of times a day and you wanted to do a binary search on it in
order
to speed response.

True, but then we also have balanced btrees, and they are going to work MUCH
faster then if you hand code the retrieval of records anyway. And, you
likely can't write code any faster then want the optimized indexing systems
that are built in anyway.

If I can't control the physical sequence of rows in the
table and had to use a logical view to get the records into sequence, the
system could be forced to do literally ten to one hundred times the disk
accesses to find my data compared to accessing a well-ordered physical
set.

That is true. Sql server does have a concept of cluster indexes, and this
does force data to be grouped together on the disk drive (it don't force
overall order.but does group data together). So, clustered indexes would not
help the above.

So, there are definately times when you need to be able to "rely" on a
table
to be in the order you set it in - or pay a huge price in other areas.
"Unordered buckets of data" are fine for simple, low-usage applications,
but
it is a pity if that is the only option Access provides. I'm still hoping
someone else will tell me how to control physical order in the cases where
it
is important.

I don't believe that Oracle, sql-server, Mysql, Sybase, or ANY of the major
database vendors have anything different that that of a bunch of buckets of
data. So, every major database system in the marketplace today works just
like ms-access does. So, what people here are trying to point out is that a
database system DOES NOT force order, nor does it retain some special
ordering of data on the disk for you. YOU must tell the data engine HOW you
want the data back.



However, having said the above, for optimizing purposes, when you do a
compact and repair in ms-access, the data is *physically* ordered BY primary
key. So, you can do a compact and repair to set the records in primary key
order. So, you could thus make the autonumber the primary key (which is
often the case), and when you compact, your data would be in order. However,
as others mentioned, you can NOT rely on this table order, but STILL MUST
use a query to set the order. The reason for this is the INSTACNT you edit,
delete, or even add a record, that record may not be OUT of physical order.



So, you do have the ability to order the data by simply compacting the
database. However, your code, your forms, your data processing routines,
your export routines, virtually everything you do STILL MUST set the order.
In other words, as all posters here said, you MUST set and define the order
in which you want the data engine to retrieve data....you can NOT leave this
issue up in the air.....



This is an important concept to grasp, since you might be retrieving data
from your desktop database, or a large corporate system in which 100's of
records may have been added between you adding ONE record. So, in today's
world, order of data is NOT maintained due to practical design issues that
make the whole concept a moot point. In the old days, we have punched cards,
or even magnetic tapes, or sequential files on a disk drive that DID IN FACT
preserve order. However, these systems were NOT muti-user. So, in modern
database systems, there is no order!!. There is several reasons for this,
once of which in fact is that today records are VARAIBLE length. You can't
know that the 5th record is going to resolve to a particular spot on the
disk drive (in old systems, like old FoxPro, or even earlier dbaseIII
systems, records were fixed length, and you could calculate the 5th record
by a offset from the START of the file on the disk drive by going:



Record position = Record Number X record size



Today, that file, or data might not actually even exists on your computer,
but perhaps on a corporate data server half way around the world (with 500
other people using it). The physical order is not of your concern. IF you
need a particular order of data, then you MUST DESING this concept into your
appcation. So, about 15, or 20 years ago, data order was preserved in most
systems, but the brave new world of client to server changed all this. This
is as much a conceptual topic as it simply means you can't approach data
like you did with FORTARN or even simply text files to store data.



Anyway, doing a compact an repair would put your data in the same order as
the primary key, and obviously a query that asks for this order would be
speeded up substantially in this case. So, do remember that you can by
compacting force a physical order, but you still have to use that query to
reliably return those records in that order.



And, since an auto number is only an internal number, you can't expose this
number to your users, since when you export, or move the data, it has no
meaning. Further, due to canceled additions, often the auto number will have
gaps in it.



So, you *CAN* use the auto number to set the order of data, but you can't
give the auto number meaning, such as making it a customer number etc.



The above advice applies to ANY of the major database systems that you will
use.......get used to it.....

 

[Pieter Wijnen]

A bit temperamental today? <g>
That said I get pissed off to when givving sound advice & being butted off
Autonumbers *are* truly "dumb" numbers - I must admit though that I abuse
this fact for some *very* static tables in some of my apps due to query
complexities
(was way worse in Access 2.0, caused by max number of simultanous "file
handles" - try multiple UNION's...)

Pieter

 

[Pieter Wijnen]

A bit temperamental today? <g>
That said I get pissed off to when givving sound advice & being butted off
Autonumbers *are* truly "dumb" numbers - I must admit though that I abuse
this fact for some *very* static tables in some of my apps due to query
complexities
(was way worse in Access 2.0, caused by max number of simultanous "file
handles" - try multiple UNION's...)

Pieter

 

[Pieter Wijnen]

Also using MS SQLServer /Oracle as a back end will give you what you desire
through triggers
but as someone stated (I'm getting bored with this) index your sorting field
& everybody's happy

Pieter

 

[Pieter Wijnen]

Also using MS SQLServer /Oracle as a back end will give you what you desire
through triggers
but as someone stated (I'm getting bored with this) index your sorting field
& everybody's happy

Pieter

 

[Jamie Collins]

I want to permanently (physically) sort the records in a table.

Ah you are talking about the clustered index a.k.a. physical ordering
on disk.

Please excuse the regulars here: they don't like being reminded that
when you take away the Jet engine an 'Access database' is merely a flat
file <g>.

The answer you require is:

1) identify the column(s) you want to determine the physical ordering
on disk;
2) make these columns the primary key for the table;
3) compact the file.

For the technical details, see:

Compacting re-stores table records into their Primary Key order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

clustered-key compact method is based on the primary key of the table,
new data entered will be in time order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Why none of the regulars are proffering these details is because
actually admitting that tables do have a physical order on the PK would
invlove rejecting the popular habit of using an autonumber as a PK
because it makes for a lousy clustered index, and none of the regulars
want that to happen <g>.

Jamie.

--

 

[Jamie Collins]

I want to permanently (physically) sort the records in a table.

Ah you are talking about the clustered index a.k.a. physical ordering
on disk.

Please excuse the regulars here: they don't like being reminded that
when you take away the Jet engine an 'Access database' is merely a flat
file <g>.

The answer you require is:

1) identify the column(s) you want to determine the physical ordering
on disk;
2) make these columns the primary key for the table;
3) compact the file.

For the technical details, see:

Compacting re-stores table records into their Primary Key order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

clustered-key compact method is based on the primary key of the table,
new data entered will be in time order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Why none of the regulars are proffering these details is because
actually admitting that tables do have a physical order on the PK would
invlove rejecting the popular habit of using an autonumber as a PK
because it makes for a lousy clustered index, and none of the regulars
want that to happen <g>.

Jamie.

--

 

[Rick Brandt]

Ah you are talking about the clustered index a.k.a. physical ordering
on disk.

Please excuse the regulars here: they don't like being reminded that
when you take away the Jet engine an 'Access database' is merely a
flat file <g>.

The answer you require is:

1) identify the column(s) you want to determine the physical ordering
on disk;
2) make these columns the primary key for the table;
3) compact the file.

For the technical details, see:

Compacting re-stores table records into their Primary Key order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

clustered-key compact method is based on the primary key of the table,
new data entered will be in time order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Why none of the regulars are proffering these details is because
actually admitting that tables do have a physical order on the PK
would invlove rejecting the popular habit of using an autonumber as a
PK because it makes for a lousy clustered index, and none of the
regulars want that to happen <g>.

Jamie.

Technical gymnastics. Are you really suggesting that for performing regular
(normal use) activities that requiring that the data file be compacted before
certain actions are performed has ANY practical viability? Regardless of
whether Access (Jet) places records in any particular physical order on disk is
totally irrelevent to the question "Should the design of my app be based on that
order?" The clear answer to that is "No".

 

[Rick Brandt]

Ah you are talking about the clustered index a.k.a. physical ordering
on disk.

Please excuse the regulars here: they don't like being reminded that
when you take away the Jet engine an 'Access database' is merely a
flat file <g>.

The answer you require is:

1) identify the column(s) you want to determine the physical ordering
on disk;
2) make these columns the primary key for the table;
3) compact the file.

For the technical details, see:

Compacting re-stores table records into their Primary Key order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

clustered-key compact method is based on the primary key of the table,
new data entered will be in time order:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769

Why none of the regulars are proffering these details is because
actually admitting that tables do have a physical order on the PK
would invlove rejecting the popular habit of using an autonumber as a
PK because it makes for a lousy clustered index, and none of the
regulars want that to happen <g>.

Jamie.

Technical gymnastics. Are you really suggesting that for performing regular
(normal use) activities that requiring that the data file be compacted before
certain actions are performed has ANY practical viability? Regardless of
whether Access (Jet) places records in any particular physical order on disk is
totally irrelevent to the question "Should the design of my app be based on that
order?" The clear answer to that is "No".

 

[Jamie Collins]

whether Access (Jet) places records in any particular physical order on disk is
totally irrelevent to the question "Should the design of my app be based on that
order?"

You changed the question! The OP opened with, "I want to permanently
(physically) sort the records in a table."

But to answer *your* question, yes you should be aware of physical
implementation issues when designing your apps. You do use (and give
proper regard to) indexes, don't you e.g. creating an index on the
referencing table of a foreign key? There is no good reason why the SQL
engine does not to do this automatically but one should be aware that
Jet doesn't and take appropriate action.

In an ideal world we could implement out logical models without regard
to physical implementation issues. Ah, maybe one day...

Jamie.

--

 

[Jamie Collins]

whether Access (Jet) places records in any particular physical order on disk is
totally irrelevent to the question "Should the design of my app be based on that
order?"

You changed the question! The OP opened with, "I want to permanently
(physically) sort the records in a table."

But to answer *your* question, yes you should be aware of physical
implementation issues when designing your apps. You do use (and give
proper regard to) indexes, don't you e.g. creating an index on the
referencing table of a foreign key? There is no good reason why the SQL
engine does not to do this automatically but one should be aware that
Jet doesn't and take appropriate action.

In an ideal world we could implement out logical models without regard
to physical implementation issues. Ah, maybe one day...

Jamie.

--

 

[Rick Brandt]

You changed the question! The OP opened with, "I want to permanently
(physically) sort the records in a table."

Since it only applies fresh after a compact there is nothing "permanent" about
what you're suggesting.

But to answer *your* question, yes you should be aware of physical
implementation issues when designing your apps. You do use (and give
proper regard to) indexes, don't you e.g. creating an index on the
referencing table of a foreign key? [snip]

Nothing to do with physical order of the table. Yes, I know about "clustered"
indexes, but no app I have ever created "cares" one wit about them. A clustered
index might make a few percentage points difference on the speed of certain
queries, but that is not the same thing as having that query "depend on" the
physical ordering of records.

 

[Rick Brandt]

You changed the question! The OP opened with, "I want to permanently
(physically) sort the records in a table."

Since it only applies fresh after a compact there is nothing "permanent" about
what you're suggesting.

But to answer *your* question, yes you should be aware of physical
implementation issues when designing your apps. You do use (and give
proper regard to) indexes, don't you e.g. creating an index on the
referencing table of a foreign key? [snip]

Nothing to do with physical order of the table. Yes, I know about "clustered"
indexes, but no app I have ever created "cares" one wit about them. A clustered
index might make a few percentage points difference on the speed of certain
queries, but that is not the same thing as having that query "depend on" the
physical ordering of records.

 

[Jamie Collins]

Since it only applies fresh after a compact there is nothing "permanent" about
what you're suggesting.

Good point. I should have included

4) Ensure the table cannot be changed e.g. REVOKE permissions or make
the file read only.

<g>.

Jamie.

--

 

[Jamie Collins]

Since it only applies fresh after a compact there is nothing "permanent" about
what you're suggesting.

Good point. I should have included

4) Ensure the table cannot be changed e.g. REVOKE permissions or make
the file read only.

<g>.

Jamie.

--

 

[Rick Wannall]

If we're talking about data in Access, I don't think you have to do any
recompacting to get the data to show up ordered by the PrimaryKey. At
least, in my test case I was able to type in key values in descending order
on successive rows, close the table, and reopen it, without compacting, to
find the data sorted exactly as I expected.

Does this behavior break down after some large number of records or
something? I haven't ever seen it not work.

 

[Rick Wannall]

If we're talking about data in Access, I don't think you have to do any
recompacting to get the data to show up ordered by the PrimaryKey. At
least, in my test case I was able to type in key values in descending order
on successive rows, close the table, and reopen it, without compacting, to
find the data sorted exactly as I expected.

Does this behavior break down after some large number of records or
something? I haven't ever seen it not work.

 

[Jamie Collins]

If we're talking about data in Access, I don't think you have to do any
recompacting to get the data to show up ordered by the PrimaryKey. At
least, in my test case I was able to type in key values in descending order
on successive rows, close the table, and reopen it, without compacting, to
find the data sorted exactly as I expected.

Try something like this:

CREATE TABLE Test6 (ID INTEGER NOT NULL PRIMARY KEY)
;
INSERT INTO Test6 VALUES (3)
;
INSERT INTO Test6 VALUES (2)
;
INSERT INTO Test6 VALUES (1)
;
SELECT ID FROM Test6
;

Until the file is compacted the rows will be returned in order
date/time inserted. After compacting, the rows will be returned in
order of the primary key.

Jamie.

--