"order by" issue after compact database

[bucweat]

Hi,

I have a Access 2003 database. I run SQL querries via a homegrown
application that uses ADO. The database contains 20 tables, each with
the same schema, that contain 30,000 records each. There are about 15
fields....one, called ttime, is a double that represents time in
seconds.

I run a query on each table that is similar to the following and save
the result to a new table:

select f1,f2,f3,f4,ttime from

where f1=1 and f2 <> 0 and f3
<>0 and f4 <> 0 order by ttime

I run this query on all twenty tables, and each is sorted by ttime
correctly. If I then compact the database and rerun the same query on
the same 20 tables, 3-5 tables will not be sorted by ttime correctly.
If I re-run the query on the 20 tables again without a compact, all 20
tables will be sorted. I can contine to run the querries successfully
until I hit the 2GB limit, at which point I must compact the database.
The next set of querries will have 3-5 tables with unsorted ttime.

This is very repeatable and seems to be caused by the database
compact. Has anyone seen something similar to this issue?

charlie

 

[Marshall Barton]

I have a Access 2003 database. I run SQL querries via a homegrown
application that uses ADO. The database contains 20 tables, each with
the same schema, that contain 30,000 records each. There are about 15
fields....one, called ttime, is a double that represents time in
seconds.

I run a query on each table that is similar to the following and save
the result to a new table:

select f1,f2,f3,f4,ttime from

where f1=1 and f2 <> 0 and f3
<>0 and f4 <> 0 order by ttime

I run this query on all twenty tables, and each is sorted by ttime
correctly. If I then compact the database and rerun the same query on
the same 20 tables, 3-5 tables will not be sorted by ttime correctly.
If I re-run the query on the 20 tables again without a compact, all 20
tables will be sorted. I can contine to run the querries successfully
until I hit the 2GB limit, at which point I must compact the database.
The next set of querries will have 3-5 tables with unsorted ttime.

This is very repeatable and seems to be caused by the database
compact. Has anyone seen something similar to this issue?

I've never seen anything like that, but I do know that
Compact deletes all the query's optimization information.
This means that the first time you run a query after
compacting, the query must reanalyze the tables to establish
the best(?) strategy for the table's current data.

Maybe, it's so busy with that, it doesn't have time to sort
correctly ;-\

 

[John Spencer]

Records in TABLES do not have an order. If you are seeing the records in a
particular order then that is almost a coincidence. Access will use the
primary key to establish a display order when you display table data.

If you want to display information in a specific order in a table, you
probably should be using a query to specify the order.

I ***suspect*** the following.
When you compact Access doesn't care about the "Order" of the records in the
table and just copies them over in whatever is the most efficient way to
grab them from the storage medium. When you then display them in the table,
they are displayed in the new storage order.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

I see that you order by ttime and it's a double. I'm assuming that the query
sorts correctly. However once you pour that data into a table, all bets are
off. The only way to extract data from a table in a certain order is with a
query that is sorted such as the SQL that you used to populate the table. In
a pure relational database system tables just hold data and it's not until
you query the data that you get information such as what happened first,
second, etc. Access and some other databases seem to do things like ordering
records at table level, but don't depend on it.

I also have to wonder about a database with 20 tables all having the same
structure and this data being poured into other tables. Have you considered
one table with an added field to show that the record is a member of some
grouping such as a District table with 1, 2, 3, etc.?

 

[Jerry Whittle]

Hi March,

I had to read it over carefully twice before I noticed that it's not the
queries he's having trouble with. Basically they are make-table queries and
it's the tables which they create are not holding sort order after a compact
and repair. Normal actually.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a Access 2003 database. I run SQL querries via a homegrown
application that uses ADO. The database contains 20 tables, each with
the same schema, that contain 30,000 records each. There are about 15
fields....one, called ttime, is a double that represents time in
seconds.

I run a query on each table that is similar to the following and save
the result to a new table:

select f1,f2,f3,f4,ttime from

where f1=1 and f2 <> 0 and f3
<>0 and f4 <> 0 order by ttime

I run this query on all twenty tables, and each is sorted by ttime
correctly. If I then compact the database and rerun the same query on
the same 20 tables, 3-5 tables will not be sorted by ttime correctly.
If I re-run the query on the 20 tables again without a compact, all 20
tables will be sorted. I can contine to run the querries successfully
until I hit the 2GB limit, at which point I must compact the database.
The next set of querries will have 3-5 tables with unsorted ttime.

This is very repeatable and seems to be caused by the database
compact. Has anyone seen something similar to this issue?

I've never seen anything like that, but I do know that
Compact deletes all the query's optimization information.
This means that the first time you run a query after
compacting, the query must reanalyze the tables to establish
the best(?) strategy for the table's current data.

Maybe, it's so busy with that, it doesn't have time to sort
correctly ;-\

 

[Marshall Barton]

Hi March,

I had to read it over carefully twice before I noticed that it's not the
queries he's having trouble with. Basically they are make-table queries and
it's the tables which they create are not holding sort order after a compact
and repair. Normal actually.

Sheesh, I missed that. Oh well, the rest of you got
buckwheat straightened out.

Thanks for straightening me out, Jerry.

 

[Jerry Whittle]

Now if I could just spell your first name right. With all the cold weather
here, I must have been thinking about Spring.

 

[Marshall Barton]

Now if I could just spell your first name right. With all the cold weather
here, I must have been thinking about Spring.

You and me both!!

 

[charlie]

I also have to wonder about a database with 20 tables all having the same

structure and this data being poured into other tables. Have you considered
one table with an added field to show that the record is a member of some
grouping such as a District table with 1, 2, 3, etc.?

This simplified example basically reproduces a problem I'm seeing at
work, the details of which I can't discuss here. The 20 similar tables
emulate the tables being generated during data collection runs. In the
real world data we have over 100 tables per database. All have the
same schema, and similar, but not the same, data. We process each run,
generating results which we summarize into a results table. Temporary
tables are generated and deleted, which increases the size of the
database. We need to use the compress function so that we do not hit
the .mdb 2 GB limit.

 

[charlie]

Hi March,

I had to read it over carefully twice before I noticed that it's not the
queries he's having trouble with. Basically they are make-table queries and
it's the tables which they create are not holding sort order after a compact
and repair. Normal actually.

Actually the compact is happening first. Basically I'm compacting the
database, then deleting the previously generated tables, and then
recreating each using the querry in my original post. When I do it
this way, most of the tables are sorted, but a couple are not. If I
skip the compact, then things work fine each and every time....until I
hit the 2GB database size limit.

I could understand if the table didn't hold sort order if you executed
a select with "order by" first and then compacted the db. My problem
is that the select with order by is failing when executed after the db
has been compacted.

 

[charlie]

Thanks everyone for responding to my request for help. In the process
of troubleshooting the issue, I think we've narrowed down the problem
a little bit...hence the....

***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE
***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE
***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE

As it turns out, it's not the compact that is causing the problem, but
rather the fact that a new database is being created as part of the
compact process. From what we've gathered, the problem occurs when a
new database is created...either via the compact command, executing a
"File/New/Blank Database" in Access, or via ADO/DAO/JRO. Once the
process has been run the first time on the new database (and issues
occur), that same database can then be cleaned out (e.g. all tables
deleted) and reused with no issues....until you hit the 2GB limit.

***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE
***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE
***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE ***** UPDATE *****
UPDATE

 

[Marshall Barton]

Now, I am really confused. Is the problem that a query is
not respecting it's ORDER BY clause when viewed in datasheet
view? If so, there is a real problem.

Or, is this an issue of using a query to populate a table,
but the table is not sorted (the same way as the query) when
the records are viewed in table view? If this is the issue,
it is not a problem, it is normal. The **only** way to view
records sorted in a specific order is to use a query with an
ORDER BY clause.