Optimizing sorted table for join In our asset tracking system, I have the following 2 tables:

item
item_location_date


Where item_location_date has the following fields:

ild_item (FK to Item)
ild_location (FK to location)
ild_pallet (Nullable FK to pallet id)
ild_created_when (creation timestamp)


To get an item's current location, it's something like this:

select *
from item
cross apply (
    select top 1 * 
    from item_location_date 
    where ild_item = itm_id 
    order by ild_date_created desc
) ild


This, however is relatively slow. Looking at the execution plan, the issue is with the sorting of the item_location_date table, it is using the correct clustered index, which I've defined as:

ALTER TABLE [dbo].[item_location_date] ADD  CONSTRAINT [PK_item_location_date] PRIMARY KEY CLUSTERED 
(
    [ild_date_created] DESC,
    [ild_item] DESC,
    [ild_location] DESC
)


However, performance is still poor. Changing the query to:

select *
from 
item inner join
(select * 
 from (
        select *, 
                ROW_NUMBER() over (PARTITION by ild_item order by ild_date_created desc) rn 
        from item_location_date
    ) ild 
    where rn = 1
) itm_location
on itm_location.ild_item = itm_id


Yields better results, changing the sort from a top-N-Sort to a normal sort. 

This database is still very small and easilty fits in RAM with plenty to spare, but I'm concerned about the performance as the quantity of data increases.

Is there anything I'm missing? Do I have the index set up to return to return the results as efficiently as possible for this scenario?
In this query:

select *
from item
cross apply (
    select top 1 * 
    from item_location_date 
    where ild_item = itm_id 
    order by ild_date_created desc
) ild


The correlated subquery is first filtered by ild_item and then ordered by ild_date_created to get the TOP 1. With this in mind, the index you need has to have ild_item first and ild_date_created second:

CREATE NONCLUSTERED INDEX NCI_item_location_date_item_date_created 
    ON [dbo].[item_location_date] (ild_item, ild_date_created)


Do you really need to select all columns with *? If it's just a few, you could also INCLUDE these on the index so it becomes a covering index and reduces additional pages retrievals.

Your current clustered index is ([ild_date_created], [ild_item], [ild_location]), if you don't mind changing it to ([ild_item], [ild_date_created], [ild_location]) then it will be as fast as you could get, since it's ordered in the manner you need and being it clustered you won't require additional lookups to get the data. Make sure there aren't other queries that depend on the current order if you consider making this change.