Raspunsul depinde de structura tabelelor tale, si de ce indexi exista disponibili la executie. De exemplu am facut acest DDL care sa satisfaca query-ul tau:

create table AddressType (AddressTypeId int identity(1,1) primary key,
AddressTypeName varchar(20));
go

create table Client (ClientId int identity(1,1) primary key,
  Name varchar(100));
go

create table [Address] (
  AddressId int identity(1,1) primary key,
  ClientId int,
  constraint fk_Address_ClientId foreign key (ClientId)
    references Client (ClientId));
go

create table [AddressXAddressType] (
  AddressId int,
  AddressTypeId int,
constraint pk_AddressXAddressType primary key (AddressId, AddressTypeId),
constraint fk_Address_AddressID foreign key (AddressId)
    references [Address](AddressId),
  constraint fk_Address_AddressTypeId foreign key (AddressTypeId)
    references AddressType (AddressTypeId));
go


Si cu 3 address type, 1500 de clienti si 2500 de AddressXAddressTypes ruleaza in 286 ms:

select (
SELECT
at.[AddressTypeName] AS [data()]
FROM
AddressType at
WHERE AddressTypeId in
(
select AddressTypeId
from [Address] a

join AddressXAddressType as axa on axa.AddressId = a.AddressId

where a.ClientId = Client.ClientId
) FOR XML PATH (''), type)
from Client

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'AddressXAddressType'. Scan count 0, logical reads 8994, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4500, logical reads 12031, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AddressType'. Scan count 1500, logical reads 3000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 289 ms.

Evident, in exemplul meu Address si Client sint foarte ingiste tabelelel, doar cu un Id. Dar ideea e ca problema nu poate fi raspunsa fara sa stim exact structura la toate tabelele, si ce indexi ai. Ce iese in evidenta foarte repede in cazul tau este ca la un scan count de ~1500 ai page reads de ~375k, deci tabela ta este mul mai lata si este in mod sigur accesata pe clustered index. Poate poti crea o cale de access mai ingusta, care sa nu trebuiasca sa citeasca atit de multe pagini, sau poate poti reformula query-ul in termeni care sa elimine nested loop-urile (care dau 1500 scan count). De exemplu elimini IN si-l transformi in JOIN:

select (
SELECT
at.[AddressTypeName] AS [data()]
FROM
AddressType at
join AddressXAddressType axa on axa.AddressTypeId = at.AddressTypeId
join Address a on axa.AddressId = a.AddressId
WHERE a.ClientId = Client.ClientId
FOR XML PATH (''), type)
from Client

SQL Server parse and compile time:
   CPU time = 16 ms, elapsed time = 17 ms.
Table 'AddressType'. Scan count 0, logical reads 5156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AddressXAddressType'. Scan count 1499, logical reads 3010, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1500, logical reads 6025, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 335 ms.

In afara de asta esti sigur ca vrei sa inlocuiesti toate spatiile cu ',' ? Nu o sa ai niciodata un address type cu spatiu in nume?

---

http://rusanu.com