Database query performance

[Guest]

I have a database table. The table has number of fields. Out of those fields
one is Company and
another is DateTime. The table has thousands of records. I want to get the
most recent
record for each company. In order to do that I am using the following query

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


Note: There is only one record exists for the given company on a given date

The problem is that this query is very slow. Am I doing something wrong or
there could be another
alternative way to improve it ?

Thanks in advance
KDV

 

[Chad Z. Hower aka Kudzu]

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


Note: There is only one record exists for the given company on a given
date

The problem is that this query is very slow. Am I doing something
wrong or there could be another
alternative way to improve it ?

Yes, you are running a sub query which is very slow. It has to rerun it each time. You shoudl see if you
can change it to a join if at all possible.

 

[WJ]

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company

1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[William \(Bill\) Vaughn]

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query plan
and see if the engine is doing a rowscan or an index hit. I would also avoid
use of SELECT *. It returns columns that may or may not be needed by your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company

1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[Guest]

Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I cannot use
sub queries then what are the alternatives. I have to make use all of the
columns so I have to use Select *. Indexing of DateTime column does not help
much. Can anybody explain with an example.

Thanks
KDV

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query plan
and see if the engine is doing a rowscan or an index hit. I would also avoid
use of SELECT *. It returns columns that may or may not be needed by your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company

1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[William \(Bill\) Vaughn]

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it could not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than are
needed), moving entire tables into memory, and other "bulk" operations are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET to be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I cannot
use
sub queries then what are the alternatives. I have to make use all of the
columns so I have to use Select *. Indexing of DateTime column does not
help
much. Can anybody explain with an example.

Thanks
KDV

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query
plan
and see if the engine is doing a rowscan or an index hit. I would also
avoid
use of SELECT *. It returns columns that may or may not be needed by your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[Guest]

It is tiny application only for single user. The table has only 90000
records. When I run a query which I mentioned in the previous post, it takes
about 20 sec to complete. The result set returned has only 2000 records. I
tried indexing Company and DateTime columns, but it did not help much. What
are ur suggestions

Thanks
KDV

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it could not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than are
needed), moving entire tables into memory, and other "bulk" operations are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET to be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I cannot
use
sub queries then what are the alternatives. I have to make use all of the
columns so I have to use Select *. Indexing of DateTime column does not
help
much. Can anybody explain with an example.

Thanks
KDV

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query
plan
and see if the engine is doing a rowscan or an index hit. I would also
avoid
use of SELECT *. It returns columns that may or may not be needed by your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________




SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[Rogas69]

Hi KDV,
You can also denormalize table by adding new column [MaxDate] which would be
updated for every record for given company after inserting new record for
this company. then your query would be like

select * from CompanyDetail where [DateTime] = [MaxDate]

the other way is to create new table CompanyDetailMaxDates (CompanyID,
MaxDate) and insert/update MaxDate field in this table for company
CompanyID. then your query would be

select CompanyDetail.* from
CompanyDetail a inner join CompanyDetailMaxDates b on a.CompanyID =
b.CompanyID
where b.[MaxDate] = a.[DateTime]

hmm the second idea is better imho.

HTH

Peter

 

[Guest]

Thanks Peter. I will try the ideas u have given. Actually I wanted to avoid
using another table. Anyway if this the way to go then no problem

Thanks
KDV

 

[William \(Bill\) Vaughn]

An index won't do any good if the query does not leverage the index. For
example, suppose you index on DateChanged. If the query says "look for the
highest date (max(DateChanged)) the only alternative for the query engine is
to search the entire table unless the engine is smart enough to know the
highest index points to the highest date. 20 seconds to scan 90,000 rows
sounds about right (or a tad long). I also expect the additional filter on
company means that JET has to search all of the rows looking for a match on
company. Is this column indexed as well? I would also take off the ORDER BY
clause and see if that's faster. Sometimes the query engine will do the
order-by first (sorting all rows) and then do the filter (picking out just
those companies that qualify).

Now if this was SQL Server (MSDE/SQL Express) you could run the Query
Analyzer against the SQL to see just what indexes are being used and where
all the time is consumed. That's another reason I don't recommend JET for
serious work. While it's free, the expense to tune and develop against it
(on top of the support expense) makes it too expensive to recommend.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

It is tiny application only for single user. The table has only 90000
records. When I run a query which I mentioned in the previous post, it
takes
about 20 sec to complete. The result set returned has only 2000 records. I
tried indexing Company and DateTime columns, but it did not help much.
What
are ur suggestions

Thanks
KDV

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it could
not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than are
needed), moving entire tables into memory, and other "bulk" operations
are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET to
be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I
cannot
use
sub queries then what are the alternatives. I have to make use all of
the
columns so I have to use Select *. Indexing of DateTime column does not
help
much. Can anybody explain with an example.

Thanks
KDV


:

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query
plan
and see if the engine is doing a rowscan or an index hit. I would also
avoid
use of SELECT *. It returns columns that may or may not be needed by
your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________




SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[Guest]

Thanks William. Now I am thinking to add another table to the database which
will get updated with the most recent transaction for each company. I know
that this table will never hold more than 3000 transactions, so running query
against this will be fine. My previous table will keep the complete history
of the transaction and the new table with the most recent ones.

Thanks
KDV

An index won't do any good if the query does not leverage the index. For
example, suppose you index on DateChanged. If the query says "look for the
highest date (max(DateChanged)) the only alternative for the query engine is
to search the entire table unless the engine is smart enough to know the
highest index points to the highest date. 20 seconds to scan 90,000 rows
sounds about right (or a tad long). I also expect the additional filter on
company means that JET has to search all of the rows looking for a match on
company. Is this column indexed as well? I would also take off the ORDER BY
clause and see if that's faster. Sometimes the query engine will do the
order-by first (sorting all rows) and then do the filter (picking out just
those companies that qualify).

Now if this was SQL Server (MSDE/SQL Express) you could run the Query
Analyzer against the SQL to see just what indexes are being used and where
all the time is consumed. That's another reason I don't recommend JET for
serious work. While it's free, the expense to tune and develop against it
(on top of the support expense) makes it too expensive to recommend.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

It is tiny application only for single user. The table has only 90000
records. When I run a query which I mentioned in the previous post, it
takes
about 20 sec to complete. The result set returned has only 2000 records. I
tried indexing Company and DateTime columns, but it did not help much.
What
are ur suggestions

Thanks
KDV

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it could
not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than are
needed), moving entire tables into memory, and other "bulk" operations
are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET to
be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________


Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I
cannot
use
sub queries then what are the alternatives. I have to make use all of
the
columns so I have to use Select *. Indexing of DateTime column does not
help
much. Can anybody explain with an example.

Thanks
KDV


:

I would also change the column name from DateTime (a reserved word) to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the query
plan
and see if the engine is doing a rowscan or an index hit. I would also
avoid
use of SELECT *. It returns columns that may or may not be needed by
your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________




SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in a
heart-beat since you are only interested in the lattest one ?

John

 

[William \(Bill\) Vaughn]

This sounds like a great strategy. Let us know how it works out.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Thanks William. Now I am thinking to add another table to the database
which
will get updated with the most recent transaction for each company. I know
that this table will never hold more than 3000 transactions, so running
query
against this will be fine. My previous table will keep the complete
history
of the transaction and the new table with the most recent ones.

Thanks
KDV

An index won't do any good if the query does not leverage the index. For
example, suppose you index on DateChanged. If the query says "look for
the
highest date (max(DateChanged)) the only alternative for the query engine
is
to search the entire table unless the engine is smart enough to know the
highest index points to the highest date. 20 seconds to scan 90,000 rows
sounds about right (or a tad long). I also expect the additional filter
on
company means that JET has to search all of the rows looking for a match
on
company. Is this column indexed as well? I would also take off the ORDER
BY
clause and see if that's faster. Sometimes the query engine will do the
order-by first (sorting all rows) and then do the filter (picking out
just
those companies that qualify).

Now if this was SQL Server (MSDE/SQL Express) you could run the Query
Analyzer against the SQL to see just what indexes are being used and
where
all the time is consumed. That's another reason I don't recommend JET for
serious work. While it's free, the expense to tune and develop against it
(on top of the support expense) makes it too expensive to recommend.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

It is tiny application only for single user. The table has only 90000
records. When I run a query which I mentioned in the previous post, it
takes
about 20 sec to complete. The result set returned has only 2000
records. I
tried indexing Company and DateTime columns, but it did not help much.
What
are ur suggestions

Thanks
KDV

:

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it
could
not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than
are
needed), moving entire tables into memory, and other "bulk" operations
are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET
to
be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________


Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I
cannot
use
sub queries then what are the alternatives. I have to make use all
of
the
columns so I have to use Select *. Indexing of DateTime column does
not
help
much. Can anybody explain with an example.

Thanks
KDV


:

I would also change the column name from DateTime (a reserved word)
to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the
query
plan
and see if the engine is doing a rowscan or an index hit. I would
also
avoid
use of SELECT *. It returns columns that may or may not be needed
by
your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________




SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail
WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is
indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in
a
heart-beat since you are only interested in the lattest one ?

John

 

[Guest]

That works great. Now I don't have to worry about the query performance even
the history data is growing, because query is run against different table.

Thanks
KDV

This sounds like a great strategy. Let us know how it works out.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Thanks William. Now I am thinking to add another table to the database
which
will get updated with the most recent transaction for each company. I know
that this table will never hold more than 3000 transactions, so running
query
against this will be fine. My previous table will keep the complete
history
of the transaction and the new table with the most recent ones.

Thanks
KDV

An index won't do any good if the query does not leverage the index. For
example, suppose you index on DateChanged. If the query says "look for
the
highest date (max(DateChanged)) the only alternative for the query engine
is
to search the entire table unless the engine is smart enough to know the
highest index points to the highest date. 20 seconds to scan 90,000 rows
sounds about right (or a tad long). I also expect the additional filter
on
company means that JET has to search all of the rows looking for a match
on
company. Is this column indexed as well? I would also take off the ORDER
BY
clause and see if that's faster. Sometimes the query engine will do the
order-by first (sorting all rows) and then do the filter (picking out
just
those companies that qualify).

Now if this was SQL Server (MSDE/SQL Express) you could run the Query
Analyzer against the SQL to see just what indexes are being used and
where
all the time is consumed. That's another reason I don't recommend JET for
serious work. While it's free, the expense to tune and develop against it
(on top of the support expense) makes it too expensive to recommend.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

It is tiny application only for single user. The table has only 90000
records. When I run a query which I mentioned in the previous post, it
takes
about 20 sec to complete. The result set returned has only 2000
records. I
tried indexing Company and DateTime columns, but it did not help much.
What
are ur suggestions

Thanks
KDV

:

I'm often frustrated with developers who complain about Access/JET
performance. It's like returning a bicycle to the store because it
could
not
deliver a ton of coal from the basket on its handlebars.
Just a thought. All too often returning too many rows (far more than
are
needed), moving entire tables into memory, and other "bulk" operations
are
the cause of the issues. Is your design suffering from a single-use
application or many users attempting to use the data. I've found JET
to
be
adequate (and pretty fast) when used within its limits and wisely.




--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________


Just to clarify that the database is Access.

I still could not figure out how to improve the performance. If I
cannot
use
sub queries then what are the alternatives. I have to make use all
of
the
columns so I have to use Select *. Indexing of DateTime column does
not
help
much. Can anybody explain with an example.

Thanks
KDV


:

I would also change the column name from DateTime (a reserved word)
to
Date_Time or some other spelling.
To see how the server is handling the query, you can turn on the
query
plan
and see if the engine is doing a rowscan or an index hit. I would
also
avoid
use of SELECT *. It returns columns that may or may not be needed
by
your
application.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________




SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail
WHERE
Company=X.Company)
ORDER BY Company


1. I would index the field [DateTime]. Very fast if it is
indexed.

2. I would revise the above SQL script to

"select top 1 * from CompanyDetail order by [DateTime] desc"

this query script (run on MS/SQL Server) would return 1 record in
a
heart-beat since you are only interested in the lattest one ?

John

 

[Mark Hayden]

Just came across this thread so sorry for the late reply (if you are
still reading this).

Denormalising your schema or adding tables that represent duplicate
information is generally bad practise and should be avoided, so neither
solution KDV suggested is optimal (I agree the latter is better than the
former but neither is *best*).

If you have not gone down the path of creating another table I strongly
suggest using the "aggreagte functions with GROUP BY clause" method.
For example:

SELECT X.field1, [...], X.fieldN, MAX([X.DateTime]) AS LastDate
FROM CompanyDetail X
WHERE [...]
GROUP BY X.field1, [...], X.fieldN

I dont know what fields are in your table as you use * to specify all
fields, but it is often better to pick and choose only the fields you
want as it is more efficient. You just have to make sure that all the
fields you SELECT that are NOT used in an aggregate function like MIN,
MAX, SUM, AVG and so on are also specified in the GROUP BY clause.

Not sure why there was all this discussion around other more elabourate
solutions--perhaps you tried GROUP BY before but had reasons not to use
it. BTW to increase performance make sure you index fields like
DateTime etc if you often use them in WHERE clauses or with MIN and MAX.

 

[MSH]

In the database world in general, 90,000 records in a single table is
indeed tiny. In the world of MS Access however, a single table with
that many records is getting in the realm of taxing the system. When I
first started using MS Access I concluded that it was worthless for
applications that needed to manage more than 100,000 records,
particularly if the database resided on a network drive.

Hardware and MS Access itself have improved but if an application is
going to apporach a million records I still start looking to migrate to
another database engine. Like one poster mentioned, MS Access is a good
DESKTOP database and to use it for larger applications (lots of data
and/or simultaneous users) is like trying to haul a ton of coal with a
bicycle.

Here is an example of my experience: A query in Access 97 on a table of
over 200,000 records took several MINUTES to run on a P166 desktop. I
"upsized" the app by moving the database to another P166 machine running
Linux with PostgreSQL 6.5 and modifying the Access app to use the Linux
box via ODBC and linked tables and passthrough queries. the result was
literally a ONE HUNDRED-FOLD IMPROVEMENT--it only took a few seconds
instead of many minutes. The results would've been similar had I used
MS SQL Server or Oracle or another "real" database as well.

I already posted a suggestion using GROUP BY in another post. Someone
mentioned using a join as well. Such a join might look like this:

SELECT X.*, Y.LastDT
FROM CompanyDetail X
INNER JOIN (SELECT Company, MAX([DateTime] AS LastDT FROM CompanyDetail
GROUP BY Company)) Y ON X.Company = Y.Company

I'm not sure if Access supports subqueries in a join construct, so you
might have to make another view/query called Y:

SELECT Company, MAX([DateTime] AS LastDT FROM CompanyDetail GROUP BY
Company)

and have a main query like this:

SELECT X.*, Y.LastDT
FROM CompanyDetail X
INNER JOIN Y ON X.Company = Y.Company

AS mentioned before, Company and [DateTime] should be indexed. Also, as
I mentioned Access performance degrades considerably if the MDB file is
on a network folder/drive rather than on the local disk. If the network
drive is on a very remote machine and has to go over a WAN, or there is
any point where the bandwith goes to 10 Mb/s or slower then querying
90,000 record tables is already quite large for access. If it really IS
a single-user app then put it on his local hard drive. If nothing here
has helped or there is some reason you cannot put it on the local drive
then seriously consider upgrading to another database like SQL server or
even a free alternative like PostgreSQL (which runs well on Windows now,
unlike in the late 90s).