Table Function becomes slow

  • Hi folks, I have a 2012 sql and it works very fast, one of the most used queries is a table function. This function over time becomes slow and heavy. If I add OPTION (HASH JOIN) the function is again fast (maybe faster), but only for a couple of days.

    I leave you an example

     

  • balestragerman wrote:

    Hi folks, I have a 2012 sql and it works very fast, one of the most used queries is a table function. This function over time becomes slow and heavy. If I add OPTION (HASH JOIN) the function is again fast (maybe faster), but only for a couple of days.

    I leave you an example

    I see no example.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I'm sorry, it seems it was a upload error

    Attachments:
    You must be logged in to view attached files.
  • Historically, I have found that Stored Procs out-perform functions such as these.

    • Try converting the function to a proc
    • Try updating stats on the tables being referenced by the function.
  • We have a sql job that reorganizes / rebuilds the indexes every day. This same JOB also updates the statistics of the tables with fragmentation greater than 10. I will try changing the function to a stored procedure. Thank you

     

  • I can see you are using a string split function - the longer the string, the worse that function will perform.

    although the number of UNION ALL statements in there will also cause problems... have you tried getting an "actual execution plan" - I recommend taking all of the code out of the function and getting a plan based on the SQL alone (not inside a create function)

    possible you have missing indexes, or perhaps the data changes so rapidly that your stats are out of date (sp_updatestats….. does that fix it)

    if you could put this on a sql2016 server and run it until it  gets slow then you could use the query store to see if you are getting a bad query plan and then force a good plan (you might even be able to do something similar with OPTION NORECOMPILE)

    I'm with Des on the procs front on this, but it's a pain in the backside dumping it into a temp table whereas a table value function can be used like a table

    MVDBA

  • Hi MVDBA, yes i use a split function. Normally not wold take more than 10 splitted ID.

    Also i was take all the code and run real execution plan and create all the missing index.

    That function just know doesnt take between 1 & 3 seconds. But after 2 week (for example) it will be slowest. This funciton is one of the more used in the website. Sometimes it runs up to 10 times at the same time.

    There are one way to set one permanent execution plan for the function?

    -Another observation, this function runs within a stored procedure that executes a text query

  • This is a actual execution plan, i take all the code and comment all union lines. I change the extension of .sql to .txt for upload

  • You're throwing 500 rows of code into a forum without understanding where's the problem and expect people to solve it without giving sample data, execution plans or data definitions? You won't get much help, because people can't offer it.

    On the nice side, I can give you some advices:

    1. Stop using multi-statement table-valued functions. They significantly slow down code. In the following example, my testing shows that it makes the query run at least 200 times slower.
    2. Don't use functions on columns in the WHERE clause, they prevent the possibility of using indexes or statistics correctly.
    3. Change the UNION ALL to multiple INSERTs to measure each statement individually and identify the problematic ones to prioritize them.
    4. Look for DelimitedSplit8k or DelimitedSplit8K_LEAD to learn how to create an efficient split function that you can  use in your code.
    CREATE FUNCTION ScalarPerformanceTest( @Par int)
    RETURNS int
    AS
    BEGIN
    RETURN @Par;
    END
    GO

    CREATE FUNCTION MSTVPerformanceTest( @Par int)
    RETURNS @Table TABLE(Par int)
    AS
    BEGIN
    INSERT INTO @Table VALUES(@Par);

    RETURN;
    END
    GO

    CREATE FUNCTION ITVPerformanceTest( @Par int)
    RETURNS TABLE
    AS
    RETURN
    SELECT @Par AS Par;

    GO

    WITH
    E(n) AS(
    SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
    ),
    E2(n) AS(
    SELECT a.n FROM E a, E b
    ),
    E6(n) AS(
    SELECT a.n FROM E2 a, E2 b, E2 c
    ),
    cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E6
    )
    SELECT n
    INTO #Tally
    FROM cteTally

    DECLARE @n int,
    @Time datetime2 = SYSDATETIME(),
    @elapsed int = 0;


    SET @Time = SYSDATETIME();
    SELECT @n = n
    FROM #Tally
    SET @elapsed = DATEDIFF(ms, @Time, SYSDATETIME())
    PRINT 'Dry run - ' + RIGHT( REPLICATE( ' ', 10) + CAST( @elapsed as varchar(10)), 10) + ' miliseconds'

    SET @Time = SYSDATETIME();

    SELECT @n = dbo.ScalarPerformanceTest(n)
    FROM #Tally;

    SET @elapsed = DATEDIFF(ms, @Time, SYSDATETIME())
    PRINT 'Scalar Function - ' + RIGHT( REPLICATE( ' ', 10) + CAST( @elapsed as varchar(10)), 10) + ' miliseconds'

    SET @Time = SYSDATETIME();

    SELECT @n = Par
    FROM #Tally
    CROSS APPLY dbo.ITVPerformanceTest(n);

    SET @elapsed = DATEDIFF(ms, @Time, SYSDATETIME())
    PRINT 'Inline Function - ' + RIGHT( REPLICATE( ' ', 10) + CAST( @elapsed as varchar(10)), 10) + ' miliseconds'

    SET @Time = SYSDATETIME();

    SELECT @n = Par
    FROM #Tally
    CROSS APPLY dbo.MSTVPerformanceTest(n);

    SET @elapsed = DATEDIFF(ms, @Time, SYSDATETIME())
    PRINT 'Multi-statement Function - ' + RIGHT( REPLICATE( ' ', 10) + CAST( @elapsed as varchar(10)), 10) + ' miliseconds'

    SET @Time = SYSDATETIME();

    GO
    DROP TABLE #Tally
    DROP FUNCTION ScalarPerformanceTest, ITVPerformanceTest, MSTVPerformanceTest;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • balestragerman wrote:

    This is a actual execution plan, i take all the code and comment all union lines. I change the extension of .sql to .txt for upload

    .SQL files are not execution plans. Please read this, for example.

    https://www.sqlshack.com/sql-server-query-execution-plan-beginners-types-options/

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • An inline TVF should perform better, although how much better it's very difficult to estimate.  I've done the best I can converting this in a short time, you may need to tweak/adjust it some, but I think it's at least pretty close if not there.

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fEstadoProveedorSplit')
    BEGIN
    DROP FUNCTION [fEstadoProveedorSplit]
    END
    GO

    -- ========================================================================================================================================================
    -- Descripción: Devuelve el estado de proveedores
    -- Realizado Usuario
    -- ---------- ----------- ------------------------------------------------ ------------------------------------------------
    -- 2016-03-05 German Creada
    -- 2017-04-26 German Se arreglo el bloqueo por doc faltantes en prv MONOTRIBUTISTAS
    -- 2017-08-28 German Se modifico IMP-FORM para que falta fecha en periodos antiguos desautorice como en f931
    -- 2018-05-21 German Se agrego Id_Oc para el calculo de pds
    -- 2018-08-08 Cecilio Se agrego UNION ALL de Plantilla
    -- 2019-01-07 German se modifico las tablas para poner primary
    -- 2019-07-01 German se modifico para q use la funcion fEstadoEmpleadoMonoPorProveedorSplit
    -- ========================================================================================================================================================

    CREATE FUNCTION [dbo].[fEstadoProveedorSplit]
    (
    @proveedor NVARCHAR(MAX),
    @id_edificio INT,
    @id_oc INT,
    @fecha DATETIME
    )
    RETURNS TABLE
    AS
    RETURN (
    WITH PROVEEDORES AS (
    SELECT Item FROM dbo.fnSplit(@proveedor,',')
    ),
    PLANTILLAS AS (
    SELECT p.Id_Proveedor , ISNULL(Id_Plantilla, 0) AS Id_Plantilla
    FROM dbo.Proveedor p WITH(NOLOCK)
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    LEFT JOIN dbo.Proveedor_Anexo pa WITH(NOLOCK) ON p.Id_Proveedor = pa.Id_Proveedor
    WHERE Id_Empresa = (
    SELECT TOP (1) x.Id_Proveedor
    FROM PROVEEDORES x
    WHERE X.Id_Proveedor = P.Id_Proveedor
    ORDER BY ISNULL(Id_Plantilla, 0)
    )
    )
    SELECT Id_Proveedor, MAX(Estado) as 'Estado'
    FROM (
    --2014-05-06 validacion en empleados MONO
    SELECT p.Id_Proveedor as 'Id_Proveedor', fee.Estado as 'Estado'
    FROM dbo.proveedor AS p WITH(NOLOCK)
    INNER JOIN dbo.empleado AS e WITH(NOLOCK) ON p.Id_Proveedor = e.id_proveedor
    INNER JOIN dbo.fEstadoEmpleadoMonoPorProveedorSplit(@proveedor,@id_edificio,@id_oc,@fecha) AS fee ON e.Id_Empleado = fee.Id_Empleado
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE p.Estado = 1
    AND p.Cuit = e.Nro_inscripto AND p.Id_Tipo_Contratado = 1 --MONOTRIBUTISTA


    UNION ALL
    -- Validación en: proveedor
    SELECT p.Id_Proveedor as 'Id_Proveedor', p.Estado + 100 as 'Estado'
    FROM Proveedor p WITH(NOLOCK)
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE p.Estado > 1

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor', 1 as 'Estado'
    FROM oc WITH(nolock)
    JOIN oc_proveedor ocp WITH(nolock)
    ON oc.Id_oc = ocp.Id_Oc
    JOIN proveedor p WITH(nolock)
    ON ocp.Id_Proveedor = p.Id_Proveedor
    JOIN dbo.empresa_setup AS es WITH(nolock) ON es.id_empresa = p.id_Empresa AND id_dato = 18 AND es.estado = 1
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE p.Estado = 1 -- Habilitado
    AND @id_oc > 0
    AND dbo.EstadoFinalDelOC(oc.Id_oc, 0, @fecha) <> 0

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1
    THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),dsh.Fecha_Habilitacion)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM proveedor_doc_sh dsh WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON dsh.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    AND ((dsh.Opcion <> 1 AND dsh.Id_Tipo <> 8) OR (dsh.Id_Tipo = 8 AND dsh.Opcion = 2) ) --1 = No requiere
    JOIN edificio e WITH(nolock)
    ON dsh.Id_Edificio = e.Id_Edificio
    AND (@id_edificio = 0 OR (@id_edificio > 0 AND dsh.Id_Edificio = @id_edificio))
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock) ON cei.Id_Empresa = p.Id_Empresa AND cei.Id_Combo = 15 --_lbUCSeguridadHigieneTituloProveedor
    AND cei.Tabla = 'Tipo_Seguridad_Higiene_Proveedor'
    AND cei.Id_Item = dsh.Id_Tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE
    (dsh.Fecha_Habilitacion IS NULL
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),dsh.Fecha_Habilitacion) AS DATE) < CAST(@fecha as DATE))
    AND ISNULL(cei.Visible, 1) = 1
    AND ((dsh.Id_Doc_SH = ( SELECT top 1 x.Id_Doc_SH
    FROM proveedor_doc_sh x WITH(nolock)
    WHERE x.id_proveedor = dsh.id_proveedor AND x.id_Tipo = dsh.id_Tipo AND x.Id_Edificio = dsh.Id_Edificio
    AND ISNULL(x.Id_Oc,0) = ISNULL(dsh.Id_Oc,0) --2018-05-21 ID_OC
    ORDER BY x.Fecha_Habilitacion DESC, cc_fecha DESC)))

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor', 1 as 'Estado'
    FROM proveedor p WITH(nolock)
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE @id_edificio > 0
    AND p.Estado = 1 -- Habilitado
    AND NOT EXISTS (SELECT 1
    FROM proveedor_doc_sh dsh WITH(nolock)
    WHERE dsh.Id_Proveedor = p.Id_Proveedor
    AND dsh.Id_edificio = @id_edificio)

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor', 1 as 'Estado'
    FROM proveedor p WITH(nolock)
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE @id_edificio = 0
    AND p.Estado = 1 -- Habilitado
    AND NOT EXISTS (SELECT 1
    FROM proveedor_doc_sh dsh WITH(nolock)
    WHERE dsh.Id_Proveedor = p.Id_Proveedor)
    --GROUP BY p.Id_Proveedor

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1
    THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),pri.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM dbo.proveedor_impuesto pri WITH(nolock)
    JOIN dbo.proveedor p WITH(nolock)
    ON pri.id_proveedor = p.id_proveedor
    AND p.Estado = 1 -- Habilitado
    AND pri.Requiere = 1
    LEFT JOIN dbo.Combo_Empresa_Item cei WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 16 -- _lbUCImpuestoFormularioTituloImpuesto
    AND cei.Tabla = 'Tipo_Impuesto'
    AND cei.Id_Item = pri.Tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE NOT EXISTS (SELECT 1
    FROM dbo.proveedor_impuesto x WITH(nolock)
    WHERE x.id_proveedor = pri.id_proveedor AND x.Tipo = pri.Tipo
    AND x.Fecha_Vencimiento > pri.Fecha_Vencimiento)
    AND (pri.Fecha_Vencimiento is null
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),pri.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))
    AND ISNULL(cei.Visible, 1) = 1

    UNION ALL
    -- Validación en proveedor_impuesto : items bloqueantes que no fueron cargados
    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM dbo.Combo_Empresa_Item cei WITH(nolock)
    JOIN dbo.proveedor p WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND p.Estado = 1 -- Habilitado
    JOIN PLANTILLAS pl ON pl.Id_Proveedor = p.Id_Proveedor AND pl.Id_Plantilla = 0
    JOIN dbo.Tipo_Impuesto ti WITH(nolock)
    ON cei.Id_Item = ti.Id
    AND cei.Id_Combo = 16 -- _lbUCImpuestoFormularioTituloImpuesto
    AND cei.Tabla = 'Tipo_Impuesto'
    LEFT JOIN dbo.proveedor_impuesto pri WITH(nolock)
    ON pri.id_proveedor = p.id_proveedor
    AND pri.Tipo = cei.Id_Item
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND pri.Id_Proveedor IS NULL
    AND p.Id_Tipo_Contratado = 2 --2017-04-26 EMPLEADORES
    --GROUP BY p.Id_Proveedor

    --2018-08-08 AGREGADO PARA PLANTILLA
    UNION ALL
    -- Validación en proveedor_impuesto : items bloqueantes que no fueron cargados
    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Plantilla_Item cei WITH(nolock)
    JOIN PLANTILLAS pl ON pl.Id_Plantilla = cei.Id_Plantilla
    JOIN proveedor p WITH(NOLOCK)
    ON pl.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    JOIN dbo.Tipo_Impuesto ti WITH(nolock)
    ON cei.Id_Item = ti.Id
    AND cei.Id_Combo = 16 -- _lbUCImpuestoFormularioTituloImpuesto

    LEFT JOIN dbo.proveedor_impuesto pri WITH(nolock)
    ON pri.id_proveedor = p.id_proveedor
    AND pri.Tipo = cei.Id_Item
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND pri.Id_Proveedor IS NULL
    AND p.Id_Tipo_Contratado = 2 --2017-04-26 EMPLEADORES
    --GROUP BY p.Id_Proveedor
    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),pf.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM proveedor_formulario pf WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON pf.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    AND pf.Requiere = 1
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 17 -- _lbUCImpuestoFormularioTituloFormulario
    AND cei.Tabla = 'Tipo_Formulario'
    AND cei.Id_Item = pf.id_Tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE NOT EXISTS (SELECT 1
    FROM dbo.proveedor_formulario x WITH(nolock)
    WHERE x.id_proveedor = pf.id_proveedor AND x.Id_Tipo = pf.Id_Tipo
    AND x.Fecha_Vencimiento > pf.Fecha_Vencimiento)
    AND (pf.Fecha_Vencimiento is null
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),pf.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))
    AND ISNULL(cei.Visible, 1) = 1

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Combo_Empresa_Item cei WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND p.Estado = 1 -- Habilitado
    JOIN PLANTILLAS pl ON pl.Id_Proveedor = p.Id_Proveedor AND pl.Id_Plantilla = 0
    JOIN Tipo_Formulario tf WITH(nolock)
    ON cei.Id_Item = tf.Id
    AND cei.Id_Combo = 17 -- _lbUCImpuestoFormularioTituloFormulario
    AND cei.Tabla = 'Tipo_Formulario'
    LEFT JOIN proveedor_formulario pf WITH(nolock)
    ON pf.id_proveedor = p.id_proveedor
    AND pf.Id_Tipo = cei.Id_Item
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND pf.Id_Proveedor IS NULL
    AND (p.Id_Tipo_Contratado = 2 OR (p.Id_Tipo_Contratado = 1 AND cei.Id_Item NOT IN (1,2,4,5,6,10))) --2017-04-26 monotributistas las doc distintas a esos id
    --GROUP BY p.Id_Proveedor

    UNION ALL
    --2018-08-08 AGREGADO PARA PLANTILLAS
    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Plantilla_Item cei WITH(nolock)
    JOIN PLANTILLAS pl ON pl.Id_Plantilla = cei.Id_Plantilla
    JOIN proveedor p WITH(NOLOCK)
    ON pl.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    JOIN Tipo_Formulario tf WITH(nolock)
    ON cei.Id_Item = tf.Id
    AND cei.Id_Combo = 17 -- _lbUCImpuestoFormularioTituloFormulario
    LEFT JOIN proveedor_formulario pf WITH(nolock)
    ON pf.id_proveedor = p.id_proveedor
    AND pf.Id_Tipo = cei.Id_Item
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND pf.Id_Proveedor IS NULL
    AND (p.Id_Tipo_Contratado = 2 OR (p.Id_Tipo_Contratado = 1 AND cei.Id_Item NOT IN (1,2,4,5,6,10))) --2017-04-26 monotributistas las doc distintas a esos id
    --GROUP BY p.Id_Proveedor

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),fp.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END as 'Estado'
    FROM proveedor p WITH(nolock)
    JOIN proveedor_f931 pf WITH(nolock)
    ON p.id_proveedor = pf.id_proveedor
    JOIN f931_items fi WITH(nolock)
    ON pf.Id_F931 = fi.Id_F931
    JOIN f931_planes fp WITH(nolock)
    ON fi.Id_F931_Item = fp.Id_F931_Item
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock) ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 28 -- _lbUCF931CuotaTituloProveedor
    AND cei.Tabla = 'Tipo_F931_Cuota' AND cei.Id_Item = 1

    WHERE (fp.Fecha_Vencimiento IS NULL
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),fp.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))
    AND fp.Estado = 2 AND fi.estado = 3 --3=plan de pago
    AND fi.requiere = 1 AND p.Estado = 1 -- Habilitado
    AND p.Id_Tipo_Contratado = 2 AND p.Id_Empresa = p.Id_Empresa
    --GROUP BY p.Id_Proveedor

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),pf.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END as 'Estado'
    FROM proveedor_f931 pf WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON pf.id_proveedor = p.id_proveedor
    AND pf.requiere = 1
    AND p.Estado = 1 -- Habilitado
    JOIN f931_items fi WITH(nolock)
    ON pf.Id_F931 = fi.Id_F931
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock) ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 27 -- _lbUCF931ItemTituloProveedor
    AND cei.Tabla = 'Tipo_Item_F931'
    AND cei.Id_Item = fi.Tipo
    WHERE fi.requiere = 1 AND p.Id_Tipo_Contratado = 2
    AND (fi.Estado = 2 AND ((ISNULL(cei.Dias_Prorroga, 0) = 0) OR ((ISNULL(cei.Dias_Prorroga, 0) > 0) AND PF.Fecha_Vencimiento IS NULL
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),PF.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))))
    --GROUP BY p.Id_Proveedor

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),pf.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM proveedor_f931 pf WITH(nolock)
    JOIN proveedor p WITH(nolock) --, f931_items fi
    ON pf.id_proveedor = p.id_proveedor
    AND p.Estado = 1 -- Habilitado
    AND pf.Requiere = 1
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock) ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 18 -- _lbUCF931TituloProveedor
    AND cei.Tabla = 'Tipo_F931'
    AND cei.Id_Item = 1
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE NOT EXISTS (SELECT 1
    FROM proveedor_f931 x WITH(nolock)
    WHERE x.id_proveedor = pf.id_proveedor
    AND x.Fecha_Vencimiento > pf.Fecha_Vencimiento)
    AND (pf.Fecha_Vencimiento is null
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),pf.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))
    AND ISNULL(cei.Visible, 1) = 1 AND p.Id_Tipo_Contratado = 2
    --GROUP BY p.Id_Proveedor
    UNION ALL

    -- Validación en proveedor_f931 : items bloqueantes que no fueron cargados
    SELECT DISTINCT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Combo_Empresa_Item cei WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND p.Estado = 1 -- Habilitado
    JOIN Tipo_F931 tf WITH(nolock)
    ON cei.Id_Item = tf.Id
    AND cei.Id_Combo = 18 -- _lbUCF931TituloProveedor
    AND cei.Tabla = 'Tipo_F931'
    LEFT JOIN (SELECT pf.Id_Proveedor
    FROM proveedor_f931 pf WITH(nolock)
    JOIN proveedor p WITH(nolock) ON pf.Id_Proveedor = p.Id_Proveedor AND p.Estado = 1
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    GROUP BY pf.Id_Proveedor ) pf
    ON pf.id_proveedor = p.id_proveedor
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1 AND ISNULL(cei.Bloquea, 0) = 1 AND cei.Requiere = 1 AND pf.Id_Proveedor IS NULL
    AND p.Id_Empresa = p.Id_Empresa AND p.Id_Tipo_Contratado = 2

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),ps.Vigencia_Hasta)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM proveedor_seguro ps WITH(nolock)
    JOIN proveedor p WITH(nolock)
    ON ps.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    AND ps.requiere = 1
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 19 -- _lbUCSegurosTituloProveedor
    AND cei.Tabla = 'Tipo_Seguro_Proveedor'
    AND cei.Id_Item = ps.id_tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND (ps.Vigencia_Hasta IS NULL
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),ps.Vigencia_Hasta) AS DATE) < CAST(@fecha as DATE)
    OR ((ISNULL(ps.Clausula,0) = 0 AND ps.id_tipo <> 2)) )

    UNION ALL
    ---Chequea el estado en Vehiculo SEGURO para los items QUE BLOQUEAN Y NO FUERON CARGADOS
    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Combo_Empresa_Item cei WITH(nolock)
    JOIN dbo.proveedor AS p WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Tabla = 'Tipo_Seguro_Proveedor'
    AND cei.Id_Combo = 19 -- _lbUCSegurosTituloProveedor
    JOIN PLANTILLAS pl ON pl.Id_Proveedor = p.Id_Proveedor AND pl.Id_Plantilla = 0
    JOIN dbo.Tipo_Seguro_Proveedor AS ti WITH(nolock)
    ON cei.Id_Item = ti.Id
    LEFT JOIN dbo.proveedor_seguro AS ps WITH(nolock)
    ON cei.Id_Item = ps.id_tipo
    AND ps.Id_Proveedor = p.Id_Proveedor
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND p.estado = 1
    AND ISNULL(cei.Visible, 1) = 1
    AND ps.Id_Proveedor IS NULL
    --GROUP BY p.Id_Proveedor
    --2018-08-08 AGREGADO PARA PLANTILLA
    UNION ALL
    ---Chequea el estado en Vehiculo SEGURO para los items QUE BLOQUEAN Y NO FUERON CARGADOS
    SELECT p.Id_Proveedor as 'Id_Proveedor', 5 as 'Estado'
    FROM Plantilla_Item cei WITH(nolock)
    JOIN PLANTILLAS pl ON pl.Id_Plantilla = cei.Id_Plantilla
    JOIN proveedor p WITH(NOLOCK)
    ON pl.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    AND cei.Id_Combo = 19 -- _lbUCSegurosTituloProveedor
    JOIN dbo.Tipo_Seguro_Proveedor AS ti WITH(nolock)
    ON cei.Id_Item = ti.Id
    LEFT JOIN dbo.proveedor_seguro AS ps WITH(nolock)
    ON cei.Id_Item = ps.id_tipo
    AND ps.Id_Proveedor = p.Id_Proveedor
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Bloquea, 0) = 1
    AND cei.Requiere = 1
    AND p.estado = 1
    AND ISNULL(cei.Visible, 1) = 1
    AND ps.Id_Proveedor IS NULL
    --GROUP BY p.Id_Proveedor
    UNION ALL


    SELECT p.Id_Proveedor as 'Id_Proveedor',
    Estado = CASE WHEN ISNULL(cei.Bloquea, 0) = 1 THEN 5
    ELSE CASE WHEN CAST(DATEADD(dd,ISNULL(cei.Dias_Corte, 0),pp.Fecha_Vencimiento)AS DATE) < CAST(@fecha AS DATE) AND ISNULL(cei.Dias_Corte, 0) > 0
    THEN 5 ELSE 1 END
    END
    FROM Proveedor_Seguro_Plan pp WITH(nolock)
    JOIN Proveedor_Seguro ps WITH(nolock)
    ON ps.Id_Proveedor_Seguro= pp.Id_Proveedor_Seguro
    AND pp.Pagada = 0 -- No Pagada
    AND ps.requiere = 1
    JOIN proveedor p WITH(nolock)
    ON ps.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 19 -- _lbUCSegurosTituloProveedor
    AND cei.Tabla = 'Tipo_Seguro_Proveedor'
    AND cei.Id_Item = ps.id_tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    AND (pp.Fecha_Vencimiento IS NULL
    OR CAST(DATEADD(dd,ISNULL(cei.Dias_Prorroga, 0),pp.Fecha_Vencimiento) AS DATE) < CAST(@fecha as DATE))

    UNION ALL

    SELECT p.Id_Proveedor as 'Id_Proveedor', CASE WHEN ISNULL(cei.Bloquea, 0) = 0 THEN 1 ELSE 5 END as 'Estado'
    FROM Proveedor_seguro_monto pm WITH(nolock)
    JOIN Proveedor_seguro ps WITH(nolock)
    ON pm.Id_Proveedor_Seguro = ps.Id_Proveedor_Seguro
    AND ps.requiere = 1
    AND ISNULL(pm.Monto_Insuficiente,0) = 1
    JOIN proveedor p WITH(nolock)
    ON ps.Id_Proveedor = p.Id_Proveedor
    AND p.Estado = 1 -- Habilitado
    LEFT JOIN Combo_Empresa_Item cei WITH(nolock)
    ON cei.Id_Empresa = p.Id_Empresa
    AND cei.Id_Combo = 19 -- _lbUCSegurosTituloProveedor
    AND cei.Tabla = 'Tipo_Seguro_Proveedor'
    AND cei.Id_Item = ps.id_tipo
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE ISNULL(cei.Visible, 1) = 1
    --GROUP BY p.Id_Proveedor, ISNULL(cei.Bloquea, 0)

    UNION ALL

    /*2014-06-23 Proveedores autorizados con 0 (en realidad todos)*/
    SELECT p.Id_Proveedor AS 'Id_Proveedor', 0 AS 'Estado'
    FROM dbo.proveedor AS p WITH(NOLOCK)
    JOIN PROVEEDORES AS p1 ON p.Id_Proveedor = p1.Id_Proveedor
    WHERE p.Estado = 1
    ) AS t
    GROUP BY t.Id_Proveedor
    --OPTION (HASH GROUP)

    );
    /*end of func*/
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Glad to see that someone rewrote the multi-step tvf to an in-line tvf.  That really should help the performance, but of course ymmv.

     

  • Hi Scott, right now your functions are in production to be tested. At the moment there were no improvements in response times but the percentage of CPU is the lowest in a long time.

    Thanks a lot.

  • Thanks for the follow up.

    Now it's back to the old approach -- you have to go thru the query plan query by query and tune each separately (and perhaps consolidate one or two?).  At least it's a very short SELECT list, so the results won't be that large no matter what.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply