Ești sigur că problema este prezentă doar la CROSS APPLY ?

Pentru a optimiza ceva trebuie să știm ce să optimizam. Cu alte cuvinte trebuie să determinăm cauza/cauzele. Cauza poate sa fie un index lipsă, un index incomplet (care nu contine toate campurile necesare în interogare) sau un plan de execuție non-optim sau ... altceva (memorie insuficienta ...).

Uzual, trebuie verificat dacă în planul de execuție există următorii operatori:
- Clustered Index Scan,
- Index Scan,
- Key Lookup / Bookmark lookup. În general, aceștia sunt "baieții răi".

Printre motivele pentru care planul de execuție contine un operator CI/I Scan se pot enumera:
- Nu există indecși -> creezi indecși.
- Exista indecși dar aceștia nu conțin toate câmpurile necesare execuției interogarii -> incluzi câmpurile lipsă în indecși (fie ca și câmpuri indexate fie ca și câmpuri neindexate - INCLUDE).
- Există indecși, aceștia includ toate câmpurile dar se face Scan și nu Seek -> WITH(FORCE SEEK) (daca este SQL2008).
- Există indecși, aceștia includ toate câmpurile, dar nu sunt utilizați -> actualizezi
statisticile sau/si WITH(INDEX=...)
- Selectivitate redusă a condițiilor din WHERE/GROUP BY -> indecși filtrați (discutabil)
- Selectivitate variabilă a condițiilor (uzual asta presupune o filtrare dupa o data calendaristica; inițial anumiți utilizatori pot apela interogarea cu parametrii care au o selectivitate scăzută - [Clustered] Index Scan DataComanda BETWEEN '2001-01-21 00:00:00' AND '2011-03-24 23:59:59'- iar alți utilizatori pot apela interogarea cu niște parametrii care au o selectivitate mare - DataComanda BETWEEN '2011-03-23 00:00:00' AND '2011-03-24 23:59:59') -> OPTION(RECOMPILE) soluție problematică dacă interogarea are un numar mare de execuții.
- statistici neactualizate -> actulizezi statisticile
- tabela contine un volum mic de date.

Pentru a elimina operatorul Key lookup / Bookmark lookup trebuie să incluzi în index toate câmpurile care sunt necesare în interogare.

Observație: soluțiile prezentate anterior nu sunt întotdeauna soluțiile optime. Trebuie avut în vedere chiar opțiunea rescrierii (parțiale sau totale) interogării.

Pentru evaluarea unei soluții de optimizare poți să te raportezi în primul rând la volumul datelor citite (logical reads: SET STATISTICS IO ON) și mai puțin la timp (SET STATISTICS TIME ON). Timpul de execuție poate fi influențat negativ de execuția altor task-uri.

Alte informații: http://www.sommarskog.se/query-plan-mysteries.html