How to optimize this Select MAX() SQL query?

Question

In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this query?

SELECT MAX(StartDate) AS Expr1 
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1

I already tried with the following, which also takes 1 min to get the result

SELECT TOP(1) *
FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1
ORDER BY StartDate DESC

Is there another way to optimize the query?

Answer 1

As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now):

CREATE NONCLUSTERED INDEX IX_StartDate
ON dbo.ProductActivity (StartDate DESC);

Like any other index, this one will need to be maintained anytime a row is inserted into, or deleted from, the ProductActivity table. And anytime the StartDate column is updated (although that seems unlikely based on the name alone).

This doesn't seem like it would add a lot of overhead, but you should test with your workload to be sure. If the MAX(StartDate) is needed very often, it's probably worthwhile to keep this index around.

If there are lots and lots of writes to this table (hundreds or thousands per second), and the MAX(StartDate) isn't needed often, then you might be better off just periodically waiting the 1 minute or so for that query to finish rather than paying the overhead on each write.

Answer 2

Another possibility is to use an index you already have that is correlated with the StartDate.

For example, if you have an IDENTITY column as your primary key, and StartDate is filled in as GETDATE() when a new row is added and never updated, then you can get pretty much the same result with something like this:

SELECT 
StartDate  AS Expr1 
FROM 
[MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1

WHERE
ID = ( SELECT MAX(ID) FROM [MYSERVER].MYDB.dbo.ProductActivity AS ProductActivity_1 )

Alternatively, you can grab a small subsection of rows that you can feel confident that the right answer is contained within and then look the MAX StartDate within that.

Answer 3

Easiest way is to create an index that supports your query.

As you mentioned there is no supporting index. I suggest to use query hint option(maxdop noofcpucore) to make your query go parallel, where noofcpucore = numeric value, equal to the number of cpu cores that will be used by the query (for example 4).