SQL Server Performance

what's wrong with this?

Discussion in 'T-SQL Performance Tuning for Developers' started by GermanC4, Apr 26, 2004.

  1. GermanC4 New Member

    CREATE PROCEDURE spBuscador <br />(<br />@cve_ColorINT = null,<br />@cve_InstitucionINT = null,<br />@cve_ModeloINT = null,<br />@cve_MarcaINT = null,<br />@AnioINT = null,<br />@numInventarioINT = null,<br />@PlacasVARCHAR(10) = null,<br />@NumeroSerieVARCHAR(50) = null,<br />@cve_SituacionActualVARCHAR(1) = null,<br />@cve_Concesionaria INT = null,<br />@FromTo10INT<br />)<br />AS<br />BEGIN<br />IF @cve_Color = '' SET @cve_Color = null;<br />IF @cve_Institucion = '' SET @cve_Institucion = null;<br />IF @cve_Modelo= '' SET @cve_Modelo = null;<br />IF @cve_Marca= '' SET @cve_Marca = null;<br />IF @Anio= '' SET @Anio = null;<br />IF @numInventario= '' SET @numInventario = null;<br />IF @Placas= '' SET @Placas = null;<br />IF @NumeroSerie= '' SET @NumeroSerie = null;<br />IF @cve_Concesionaria = '' SET @cve_Concesionaria = null;<br />IF ISNUMERIC(@cve_SituacionActual) = 0 SET @cve_SituacionActual = null;<br /><br />SELECT TOP 10<br />ISNULL(inv.Placas,'N/D') AS Placas,<br />ISNULL(mod.Descripcion,'N/D') AS Modelo,<br />ISNULL(mar.Descripcion,'N/D') AS Marca,<br />ISNULL(inv.Ano,'N/D') AS Anio,<br />ISNULL(col.Descripcion,'N/D') AS Color,<br />ISNULL(inv.NumeroSerie,'N/D') AS NumeroSerie,<br />ISNULL(inv.numInventario,'N/D') AS numInventario,<br />ISNULL(con.Nombre,'N/D') AS Concesionaria,<br />ISNULL(sit.Descripcion,'N/D') AS SituacionActual<br />FROM Inventario inv<br />LEFT JOIN Modelo mod ON inv.cve_Modelo = mod.cve_Modelo<br />LEFT JOIN Marca mar ON mod.cve_Marca = mar.cve_Marca<br />LEFT JOIN Color col ON inv.cve_Color = col.cve_Color<br />LEFT JOIN Concesionaria con ON inv.cve_Concesionaria = con.cve_Concesionaria<br />LEFT JOIN SituacionActual sit ON inv.cve_SituacionActual = sit.cve_SituacionActual<br />WHERE inv.cve_Color = ISNULL(@cve_Color,inv.cve_Color) and<br />inv.cve_Institucion = ISNULL(@cve_Institucion,inv.cve_Institucion) and<br />inv.cve_Modelo = ISNULL(@cve_Modelo,inv.cve_Modelo) and<br />mod.cve_Marca = ISNULL(@cve_Marca,mar.cve_Marca) and<br />inv.Ano = ISNULL(@Anio,inv.Ano) and<br />inv.numInventario = ISNULL(@numInventario,inv.numInventario) and<br />inv.Placas = ISNULL(@Placas,inv.Placas) and<br />inv.NumeroSerie LIKE '%' + ISNULL (@NumeroSerie,inv.NumeroSerie) and<br />inv.cve_SituacionActual = ISNULL(@cve_SituacionActual,inv.cve_SituacionActual) and<br />inv.cve_Concesionaria = ISNULL(@cve_Concesionaria,inv.cve_Concesionaria) and<br />inv.cve_Inventario &gt; @FromTo10<br />ORDER BY Convert(INT,numInventario)<br />END<br />GO<br /><br /><br />it works... but it's too slow on the query analizer, and much much slower in the end application<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  2. bradmcgehee New Member

    Can you send us the execution plan? It would be very helpful.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. ChrisFretwell New Member

    A few things (not directly related to performance)
    You left join to Modelo mod but explicitly where on it mod.cve_Marca = ISNULL(@cve_Marca,mar.cve_Marca) so it will only find full join columns, and if your @cve_marca is null, it will use mar.cve_marca and you'll end up only getting fully joined records here.

    Is there anyway you can order by your column directly rather tahn the int version of it? It has to process the sort to then pick the top 10, and you convert your column, so even if the field is indexed, it wont work fast.

    Just a few quick things.
  4. GermanC4 New Member

    1. how do I see the execution plan? i can see the graphic version of it, bus how do i send it to you?<br /><br />2. the column is varchar(good knows why, it was not my doing) so if I order them just as they are it will give me something like... 1,10,100,2,20,200 and that's not what I want, i'll try and convert the table<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. GermanC4 New Member

    no wait, changing the column type would involve changing alot of SP so I'll just order them by the ID<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  6. GermanC4 New Member

    found it<br /><br /> |--Top(10)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[inv].[Placas]=[inv].[Placas], [Expr1007]=isnull([mod].[Descripcion], 'N/D'), [Expr1008]=isnull([mar].[Descripcion], 'N/D'), [inv].[Ano]=[inv].[Ano], [Expr1010]=isnull([col].[Descripcion], 'N/D'), [inv].[NumeroSerie]=[inv].[NumeroSerie], [inv].[numInventario]=[inv].[numInventario], [Expr1013]=isnull([con].[Nombre], 'N/D'), [Expr1014]=isnull([sit].[Descripcion], 'N/D')))<br /> |--Nested Loops(Left Outer Join, WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[inv].[cve_SituacionActual]=[sit].[cve_SituacionActual]))<br /> |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[inv].[cve_Concesionaria]))<br /> | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[inv].[cve_Color]))<br /> | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[mod].[cve_Marca]=isnull([@cve_Marca], [mar].[cve_Marca])))<br /> | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[mod].[cve_Marca]))<br /> | | | |--Nested Loops(Inner Join, OUTER REFERENCES<img src='/community/emoticons/emotion-6.gif' alt=':(' />[inv].[cve_Modelo]))<br /> | | | | |--Filter(WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />like([inv].[NumeroSerie], '%'+isnull([@NumeroSerie], [inv].[NumeroSerie]), NULL)))<br /> | | | | | |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[Inventario].[PK_Inventario] AS [inv]), WHERE<img src='/community/emoticons/emotion-6.gif' alt=':(' />(((((([inv].[cve_Color]=isnull([@cve_Color], [inv].[cve_Color]) AND [inv].[cve_Institucion]=isnull([@cve_Institucion], [inv].[cve_Institucion])) AND [inv].[cve_Modelo]=isnull([@cve_Modelo], [inv].[cve_Modelo])) AND [inv].[Ano]=isnull([@Anio], [inv].[Ano])) AND Convert([inv].[numInventario])=isnull([@numInventario], Convert([inv].[numInventario]))) AND [inv].[Placas]=isnull([@Placas], [inv].[Placas])) AND [inv].[cve_SituacionActual]=Convert(isnull([@cve_SituacionActual], Convert([inv].[cve_SituacionActual])))) AND [inv].[cve_Concesionaria]=isnull([@cve_Concesionaria], [inv].[cve_Concesionaria])) ORDERED FORWARD)<br /> | | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[Modelo].[PK_Modelo] AS [mod]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[mod].[cve_Modelo]=[inv].[cve_Modelo]) ORDERED FORWARD)<br /> | | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[Marca].[PK_Marca] AS [mar]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[mar].[cve_Marca]=[mod].[cve_Marca]) ORDERED FORWARD)<br /> | | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[Color].[PK_Color] AS [col]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[col].[cve_Color]=[inv].[cve_Color]) ORDERED FORWARD)<br /> | |--Clustered Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[Concesionaria].[PK_Concesionaria] AS [con]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[con].[cve_Concesionaria]=[inv].[cve_Concesionaria]) ORDERED FORWARD)<br /> |--Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[SituacionActual] AS [sit]))<br /><br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. ChrisFretwell New Member

    Okay, so look for things like this<br /><br /><br />Table Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[bwCorralon].[dbo].[SituacionActual]...<br />Table scans are not good performance. Is there an index on the cve_SituacionActual field in the SituacionActual table?<br /><br />Just a start....
  8. GermanC4 New Member

    There where 2 indexed columns, cve_Inventario as an ID (this one I droped cus' it had no reason to be there and sort of helped) the other one on cve_SituacionActual is still there, cus' there are certain values of that field that i don't need to see, in the search engine, i only want to see those rows containing a specific value in that column, is it good to have an index there?<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  9. GermanC4 New Member

    have no idea why it's not in the top anymore, so i'll just post so it apears again and someone can help [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page