Re: Concatenare randuri pentru o coloana in view - probleme performanta

rremus — Wed, 24 Jun 2009 06:56:13 GMT

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?

Concatenare randuri pentru o coloana in view - probleme performanta

sirrocco — Tue, 23 Jun 2009 06:54:16 GMT

Salutare,

Problema mea este urmatoarea :

Am un tabel Client, care are mai multe Address si fiecare adresa are unul sau mai multe tipuri AddressType. Acum, se vrea o data afisarea in tabelul de clienti a tuturor AddressType-urilor pe care le are setate pe adresele sale. Iar mai apoi trebuie sa filtrez clienti dupa AddressType.

Am facut astfel :

AllAddressTypesNames = REPLACE(
    (
    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 = dbo.Client.ClientId
    ) FOR XML PATH ('') ), ' ', ',')

Mai sus e doar o protiune din view , mai sunt vreo cateva coloane care se concateneaza tot in stilul acesta iar tabelul are vreo 1500 inregistrari.
Problema este ca dureaza destul de mult.
Am pus SET STATISTICS IO ON si ce sare in ochi ar fi :

Table 'Worktable'. Scan count 1467, logical reads 326676,
Table 'Client'. Scan count 2923, logical reads 374144,

Daca scot concatenarile , pe Worktable cifrele sunt similare, dar pe client scade logical reads la vreo 200 si se misca simtitor mai rapid. Am alta posibilitate sa concatenez acele coloane ? sa mai imbunatatesc performanta un pic ?

T-SQL

Re: Concatenare randuri pentru o coloana in view - probleme performanta

Concatenare randuri pentru o coloana in view - probleme performanta