Welcome to Sign in | Help
in Search

Concatenare randuri pentru o coloana in view - probleme performanta

Last post 06-24-2009, 9:56 AM by rremus. 1 replies.
Sort Posts: Previous Next
  •  06-23-2009, 9:54 AM 7333

    Concatenare randuri pentru o coloana in view - probleme performanta

    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 ?
  •  06-24-2009, 9:56 AM 7341 in reply to 7333

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

    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
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems