SQL Server Performance

Best Codification?

Discussion in 'Performance Tuning for DBAs' started by Luis Martin, Aug 15, 2005.

  1. Luis Martin Moderator

    With profiler I find the next SQL query:

    select distinct Ma.CodMarca as Codigo, Ma.Descripcion from AW_SISPECAL_PedidosDef as PeDf join AW_SISPECAL_PedidosDet as PeDt on PeDf.CodPedido = PeDt.CodPedido join AW_SISPECAL_Articulos as Ar on PeDt.CodArt = Ar.CodArt join AW_SISPECAL_Marcas as Ma on Ar.CodMarca = Ma.CodMarca where PeDf.CodEvento = '20050808095914840' order by Ma.Descripcion

    In QA, the executio time was: 1'13 minuts.
    The statistics show:

    46 row(s) affected)

    Table 'Worktable'. Scan count 16821, logical reads 136335, physical reads 0, read-ahead reads 0.
    Table 'AW_SISPECAL_PedidosDef'. Scan count 1, logical reads 72, physical reads 0, read-ahead reads 72.
    Table 'AW_SISPECAL_PedidosDet'. Scan count 325, logical reads 306358, physical reads 2, read-ahead reads 1002.
    Table 'AW_SISPECAL_Articulos'. Scan count 76, logical reads 1602, physical reads 0, read-ahead reads 15.
    Table 'AW_SISPECAL_Marcas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.

    Before find good indexes to improve performance, I use SQL Tuning from Quest. The suggestion was the next code modification:

    select distinct Ma.CodMarca as Codigo,
    Ma.Descripcion
    from AW_SISPECAL_PedidosDef as PeDf,
    AW_SISPECAL_PedidosDet as PeDt,
    AW_SISPECAL_Articulos as Ar,
    AW_SISPECAL_Marcas as Ma
    WHERE COALESCE(PeDf.CodEvento, PeDf.CodEvento) = '20050808095914840'
    AND Ar.CodMarca = Ma.CodMarca
    AND PeDt.CodArt = Ar.CodArt
    AND PeDf.CodPedido = PeDt.CodPedido
    order by Ma.Descripcion
    OPTION (HASH JOIN)

    The execution time was 1 second and statistics show:

    (46 row(s) affected)

    Table 'AW_SISPECAL_PedidosDet'. Scan count 1, logical reads 964, physical reads 0, read-ahead reads 975.
    Table 'AW_SISPECAL_PedidosDef'. Scan count 1, logical reads 72, physical reads 0, read-ahead reads 0.
    Table 'AW_SISPECAL_Articulos'. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0.
    Table 'AW_SISPECAL_Marcas'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.


    Comparing both statistics is very easy to know why, the 2nd codification take only 1 second.

    After create adecuate indexes, both execution times was 1 second.

    Since I can not change 3rd party software, indexes was the solution to the problem.

    Now, the question is without indexes: Why the second is so better than the first?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  2. Adriaan New Member

    What happens if you use the second query, but without the join hint?

    Not sure why they suggest syntax without JOINs - that's no longer ANSI compliant.

    EDIT:
    Ah - and what happens if you clean out cache & buffers before running the second query?
  3. Luis Martin Moderator

    "What happens if you use the second query, but without the join hint?"

    Same time than 1st query. (1'13 minuts)


    "Ah - and what happens if you clean out cache & buffers before running the second query?"

    1 second. (with joint hint)



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. FrankKalis Moderator

    quote:Originally posted by Adriaan

    What happens if you use the second query, but without the join hint?

    Not sure why they suggest syntax without JOINs - that's no longer ANSI compliant.
    It still is in case of INNER JOINs, AFAIK. Execution plans should be the same for both statements.

    Can you post the execution plans, Luis?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. Adriaan New Member

    quote:Originally posted by LuisMartin
    [br"Ah - and what happens if you clean out cache & buffers before running the second query?"

    1 second. (with joint hint)
    And without?
  6. Luis Martin Moderator

    1st query:<br /><br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[Descripcion] ASC))<br /> |--Nested Loops(Left Semi Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[CodMarca]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Marcas] AS [Ma]))<br /> |--Nested Loops(Inner Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodPedido]=[PeDt].[CodPedido]))<br /> |--Nested Loops(Inner Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDt].[CodArt]=[Ar].[CodArt]))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Articulos] AS [Ar]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodMarca]=[Ma].[CodMarca]))<br /> | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDet].[PK_AW_SISPECAL_PedidosDet] AS [PeDt]))<br /> |--Table Spool<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDef] AS [PeDf]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodEvento]='20050808095914840'))<br /><br />2nd query:<br /><br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[Descripcion] ASC))<br /> |--Hash Match(Left Semi Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[CodMarca])=([Ar].[CodMarca]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodMarca]=[Ma].[CodMarca]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Marcas] AS [Ma]))<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodArt])=([PeDt].[CodArt]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDt].[CodArt]=[Ar].[CodArt]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Articulos] AS [Ar]))<br /> |--Hash Match(Inner Join, HASH<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodPedido])=([PeDt].[CodPedido]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodPedido]=[PeDt].[CodPedido]))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDef] AS [PeDf]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />If 1 then [PeDf].[CodEvento] else [PeDf].[CodEvento]='20050808095914840'))<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDet] AS [PeDt]))<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  7. Luis Martin Moderator

    "And without?"<br /><br />1'13 seconds.<br /><br />One by one guys[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  8. Luis Martin Moderator

    I'm working in my server and forum at the same time (holliday in ARG) , so I can try anythink you want.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  9. FrankKalis Moderator

  10. Luis Martin Moderator

    I can´t follow you Frank (remember I´m no a developer).


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  11. FrankKalis Moderator


    select distinct Ma.CodMarca as Codigo
    , Ma.Descripcion from AW_SISPECAL_PedidosDef as PeDf
    INNER MERGE join AW_SISPECAL_PedidosDet as PeDt on PeDf.CodPedido = PeDt.CodPedido
    INNER MERGE join AW_SISPECAL_Articulos as Ar on PeDt.CodArt = Ar.CodArt
    INNER MERGE join AW_SISPECAL_Marcas as Ma on Ar.CodMarca = Ma.CodMarca
    where PeDf.CodEvento = '20050808095914840'
    order by Ma.Descripcion

    Something like this

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. Luis Martin Moderator

    Yeap!!!!!!!<br /><br />1 second.<br /><br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[Descripcion] ASC))<br /> |--Stream Aggregate(GROUP BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[CodMarca]) DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[Descripcion]=ANY([Ma].[Descripcion])))<br /> |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodMarca])=([Ma].[CodMarca]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ma].[CodMarca]=[Ar].[CodMarca]))<br /> |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodMarca] ASC))<br /> | |--Merge Join(Inner Join, MANY-TO-MANY MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDt].[CodArt])=([Ar].[CodArt]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodArt]=[PeDt].[CodArt]))<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDt].[CodArt] ASC))<br /> | | |--Merge Join(Inner Join, MERGE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodPedido])=([PeDt].[CodPedido]), RESIDUAL<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDt].[CodPedido]=[PeDf].[CodPedido]))<br /> | | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodPedido] ASC))<br /> | | | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDef] AS [PeDf]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[PeDf].[CodEvento]='20050808095914840'))<br /> | | |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_PedidosDet].[PK_AW_SISPECAL_PedidosDet] AS [PeDt]), ORDERED FORWARD)<br /> | |--Sort(ORDER BY<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Ar].[CodArt] ASC))<br /> | |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Articulos] AS [Ar]))<br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1003]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Marcas] AS [Ma]))<br /> |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[SisPeCal].[dbo].[AW_SISPECAL_Marcas].[PK_AW_SISPECAL_Marcas] AS [Ma]), ORDERED FORWARD)<br /><br /><br />Now, I'm all ears to know why.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  13. FrankKalis Moderator

  14. Luis Martin Moderator

    Thanks a lot.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



Share This Page