As you already know, you can't use a MAX()
aggregate in an indexed view. In any case don't fool yourself into believing that creating an indexed view is a magical performance power button. Depending on your workload, maintaining the indexed view may cost you more performance than you're gaining from the queries against it.
A couple of alternatives might be:
(1) create a non-clustered index on dbo.TbDocActions(DocID, ID DESC)
or drop the existing clustered index and change it to DocID, ID DESC
. (If ID
is an identity column, you've probably also set it up to be the primary key and it will be clustered by default, but that doesn't have to be the case.)
(2) maintain a separate table with the DocID
as the primary key, and keep the highest ID
up to date using triggers (or a stored procedure if you are constraining writes to stored procedures only). You will have to have logic for all three DML types (insert
, update
and delete
) because, in theory, any of these could affect the MAX
calculation. (Less risk, obviously, if both ID
columns are IDENTITY
.) You could also add a column to the main table and keep that up to date the same way.
Note that the same caveats apply - adding indexes or maintaining values in other tables might benefit this specific query, but they are not magic - they might affect other parts of your workload. You should always take your entire business cycle into consideration when thinking about "fixing" any single query...