Improve performance on SQL query with select top 1-statement before from-statement -

i have long complex sql query (in microsoft sql server 2012) need improve performance. have 1 issue sql query has 'select top 1' before from-statement. hard explain, below text have wrote example sql query issue:

select      player.firstname,      player.lastname,     lastgamegoals =        (select top 1 goals        playersummary         playersummary.playerid = player.playerid         order playersummaryid desc) player 

the sql above hockey players firstname , lastname , number of goals in last played game specific player. since database large slow because of select top 1-statement.

i can of course add index better performance, can avoid 'select top 1' on every player row?

how can improve performance?

for statement:

lastgamegoals =    (select top 1 goals    playersummary     playersummary.playerid = player.playerid     order playersummaryid desc) 

you want index. best index playersummery(playerid, playersummaryid, goals). covering index subquery, should have big impact on performance.

i don't see why want avoid index. can rewrite query using window functions:

select p.*,        ps.goals lastgamegoals player p left join     (select ps.*,             row_number() on (partition playerid order playersummaryid desc) seqnum      playersummary ps     ) ps     on p.playerid = ps.playerid , ps.seqnum = 1; 

you should test, guess index faster.