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

=?Utf-8?B?S0RW?= <(E-Mail Removed)> wrote in
news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed):
> 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.


--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Blog: http://blogs.atozed.com/kudzu

"KDV" <(E-Mail Removed)> wrote in message
news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
>
> 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

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.
__________________________________


"WJ" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "KDV" <(E-Mail Removed)> wrote in message
> news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
>>
>> 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
>
>

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


"William (Bill) Vaughn" wrote:

> 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.
> __________________________________
>
>
> "WJ" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > "KDV" <(E-Mail Removed)> wrote in message
> > news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
> >>
> >> 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
> >
> >
>
>
>

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.
__________________________________


"KDV" <(E-Mail Removed)> wrote in message
news:3BFFBF73-6CC0-4DD3-8692-(E-Mail Removed)...
> 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
>
>
> "William (Bill) Vaughn" wrote:
>
>> 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.
>> __________________________________
>>
>>
>> "WJ" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >
>> > "KDV" <(E-Mail Removed)> wrote in message
>> > news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
>> >>
>> >> 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
>> >
>> >
>>
>>
>>

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

"William (Bill) Vaughn" wrote:

> 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.
> __________________________________
>
>
> "KDV" <(E-Mail Removed)> wrote in message
> news:3BFFBF73-6CC0-4DD3-8692-(E-Mail Removed)...
> > 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
> >
> >
> > "William (Bill) Vaughn" wrote:
> >
> >> 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.
> >> __________________________________
> >>
> >>
> >> "WJ" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >
> >> > "KDV" <(E-Mail Removed)> wrote in message
> >> > news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
> >> >>
> >> >> 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
> >> >
> >> >
> >>
> >>
> >>
>
>
>

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

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

"Rogas69" wrote:

> 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
>
>
>

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.
__________________________________

"KDV" <(E-Mail Removed)> wrote in message
news:94539385-25A3-4D34-B08A-(E-Mail Removed)...
> 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
>
> "William (Bill) Vaughn" wrote:
>
>> 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.
>> __________________________________
>>
>>
>> "KDV" <(E-Mail Removed)> wrote in message
>> news:3BFFBF73-6CC0-4DD3-8692-(E-Mail Removed)...
>> > 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
>> >
>> >
>> > "William (Bill) Vaughn" wrote:
>> >
>> >> 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.
>> >> __________________________________
>> >>
>> >>
>> >> "WJ" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >
>> >> > "KDV" <(E-Mail Removed)> wrote in message
>> >> > news:BCABD84C-69DF-45B0-B6DB-(E-Mail Removed)...
>> >> >>
>> >> >> 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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>