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