Wednesday, March 21, 2012

Query performance

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 ?

Just to clarify that I am using Access database

Thanks in advance
KDV

This forum is for SQL Server so you should post this in a MS Access forum. You should check if you have the required indexes on the table. For this example, it would help to have index on Company and DateTime columns.

No comments:

Post a Comment