Opening table - query running?

Hi Tim -

Removing the orderby property from the table sped up the opening of the table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your slow
loading?

B Wishes


Tim F

Just because you don't see a query running message doesn't mean there isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence since
Access reorders all tables into key sequence when the database is compacted.
This fools people into thinking that their data is always stored in key
sequence which is far from the truth. NEVER depend on order unless you
specifically include an order by clause in your query.

"patti" wrote in message
...
Hi Tim -

Removing the orderby property from the table sped up the opening of the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your slow
loading?

B Wishes


Tim F

Hi Pat-

Thanks for the help. I have never used orderby prop on tables - always on
queries & reports.

Out of curiosity, how does access decide the order of a table for
datasheetview?

thanks,
patti

"Pat Hartman (MVP)" wrote:

Just because you don't see a query running message doesn't mean there isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence since
Access reorders all tables into key sequence when the database is compacted.
This fools people into thinking that their data is always stored in key
sequence which is far from the truth. NEVER depend on order unless you
specifically include an order by clause in your query.

"patti" wrote in message
...
Hi Tim -

Removing the orderby property from the table sped up the opening of the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your slow
loading?

B Wishes


Tim F

I just told you. It is undefined. Jet retrieves rows as convenient.
Normally, the rows will be returned in primary key sequence because Jet
physically reorders every table when the database is compacted.. But if the
table has been updated since the compact, the key sequence is not
guaranteed.

"patti" wrote in message
news
Hi Pat-

Thanks for the help. I have never used orderby prop on tables - always on
queries & reports.

Out of curiosity, how does access decide the order of a table for
datasheetview?

thanks,
patti

"Pat Hartman (MVP)" wrote:

Just because you don't see a query running message doesn't mean there
isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve
response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence since
Access reorders all tables into key sequence when the database is
compacted.
This fools people into thinking that their data is always stored in key
sequence which is far from the truth. NEVER depend on order unless you
specifically include an order by clause in your query.

"patti" wrote in message
...
Hi Tim -

Removing the orderby property from the table sped up the opening of the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not
universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause
if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design
team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your
slow
loading?

B Wishes


Tim F

Pat-
Thanks for your time. Excuse my ignorance, I understand that access does not
have any order per se, i thought perhaps the underlying the operating system
or or some other computer workings decided how to return data. So system just
grabs the first piece of data that matches the criteria, then the 2nd, etc.
"Jet retrieves rows as convenient". Thanks again.
"Pat Hartman (MVP)" wrote:

I just told you. It is undefined. Jet retrieves rows as convenient.
Normally, the rows will be returned in primary key sequence because Jet
physically reorders every table when the database is compacted.. But if the
table has been updated since the compact, the key sequence is not
guaranteed.

"patti" wrote in message
news
Hi Pat-

Thanks for the help. I have never used orderby prop on tables - always on
queries & reports.

Out of curiosity, how does access decide the order of a table for
datasheetview?

thanks,
patti

"Pat Hartman (MVP)" wrote:

Just because you don't see a query running message doesn't mean there
isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve
response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence since
Access reorders all tables into key sequence when the database is
compacted.
This fools people into thinking that their data is always stored in key
sequence which is far from the truth. NEVER depend on order unless you
specifically include an order by clause in your query.

"patti" wrote in message
...
Hi Tim -

Removing the orderby property from the table sped up the opening of the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put one
on.

i inherited this db and don't see the need to sort the table itself;
i'd rather remove the table sort and do that in queries. does that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you don't
state what you'd like, the most likely order is that in which they are
recovered from disk. In the specific case of Access (or, Jet actually)
those are nearly always in Primary Key order: but this is not
universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY clause
if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care. And
create the appropriate indexes to support it. To be honest, setting an
Order By property on the table is another piece of the Access design
team
putting user-hostile traps in for new users who don't know their way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of your
slow
loading?

B Wishes


Tim F

No problem. That's actually the way relational databases, not just Access,
are designed to work. Recordsets are by definition unordered sets of data
unless you impose order by using a query with an order by clause.

"patti" wrote in message
...
Pat-
Thanks for your time. Excuse my ignorance, I understand that access does
not
have any order per se, i thought perhaps the underlying the operating
system
or or some other computer workings decided how to return data. So system
just
grabs the first piece of data that matches the criteria, then the 2nd,
etc.
"Jet retrieves rows as convenient". Thanks again.
"Pat Hartman (MVP)" wrote:

I just told you. It is undefined. Jet retrieves rows as convenient.
Normally, the rows will be returned in primary key sequence because Jet
physically reorders every table when the database is compacted.. But if
the
table has been updated since the compact, the key sequence is not
guaranteed.

"patti" wrote in message
news
Hi Pat-

Thanks for the help. I have never used orderby prop on tables - always
on
queries & reports.

Out of curiosity, how does access decide the order of a table for
datasheetview?

thanks,
patti

"Pat Hartman (MVP)" wrote:

Just because you don't see a query running message doesn't mean there
isn't
one. See my earlier responses. Especially the one to "Steve".

As I said in my first post, removing the order by should improve
response.
Without the order by, the table will be returned by the query in an
undetermined order. The order will be approximately key sequence
since
Access reorders all tables into key sequence when the database is
compacted.
This fools people into thinking that their data is always stored in
key
sequence which is far from the truth. NEVER depend on order unless
you
specifically include an order by clause in your query.

"patti" wrote in message
...
Hi Tim -

Removing the orderby property from the table sped up the opening of
the
table.
And there was no "query running" message.

Thanks again for all the help & insight.

patti

"Tim Ferguson" wrote:

=?Utf-8?B?cGF0dGk=?= wrote in
:


Is there an index on the field you use for sorting? If not, put
one
on.

i inherited this db and don't see the need to sort the table
itself;
i'd rather remove the table sort and do that in queries. does
that
make sense?

Yes it does make sense, but it's only half the question.

Records in a table have to be presented in _some_ order. If you
don't
state what you'd like, the most likely order is that in which they
are
recovered from disk. In the specific case of Access (or, Jet
actually)
those are nearly always in Primary Key order: but this is not
universally
true and it would be a Bad Idea to rely on it.

The lesson, then, is to use a query with an explicit ORDER BY
clause
if
you care which order the records come out it (which is what you say
above) and only use the default table ordering if you don't care.
And
create the appropriate indexes to support it. To be honest, setting
an
Order By property on the table is another piece of the Access
design
team
putting user-hostile traps in for new users who don't know their
way
round databases, along with AutoFill and LookUp Fields etc etc.

By the way, did any of this help you to track down the cause of
your
slow
loading?

B Wishes


Tim F