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
October 8, 2019 at 12:33 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 8, 2019 at 1:48 pm
Historically, I have found that Stored Procs out-perform functions such as these.
October 8, 2019 at 3:56 pm
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
October 8, 2019 at 4:09 pm
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
October 8, 2019 at 5:09 pm
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
October 8, 2019 at 5:22 pm
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
October 8, 2019 at 5:48 pm
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:
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;
October 8, 2019 at 6:15 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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-05GermanCreada
--2017-04-26GermanSe arreglo el bloqueo por doc faltantes en prv MONOTRIBUTISTAS
--2017-08-28GermanSe modifico IMP-FORM para que falta fecha en periodos antiguos desautorice como en f931
--2018-05-21GermanSe agrego Id_Oc para el calculo de pds
--2018-08-08CecilioSe agrego UNION ALL de Plantilla
--2019-01-07Germanse modifico las tablas para poner primary
--2019-07-01Germanse modifico para q use la funcion fEstadoEmpleadoMonoPorProveedorSplit
-- ========================================================================================================================================================
CREATE FUNCTION [dbo].[fEstadoProveedorSplit]
(
@proveedorNVARCHAR(MAX),
@id_edificioINT,
@id_ocINT,
@fechaDATETIME
)
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)
JOINproveedor 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)
JOINProveedor_Seguro ps WITH(nolock)
ON ps.Id_Proveedor_Seguro= pp.Id_Proveedor_Seguro
AND pp.Pagada = 0 -- No Pagada
AND ps.requiere = 1
JOINproveedor 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)
JOINProveedor_seguro ps WITH(nolock)
ON pm.Id_Proveedor_Seguro = ps.Id_Proveedor_Seguro
AND ps.requiere = 1
AND ISNULL(pm.Monto_Insuficiente,0) = 1
JOINproveedor 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.proveedorAS 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) A socialist is someone who will give you the shirt off *someone else's* back.
October 8, 2019 at 7:09 pm
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.
October 8, 2019 at 10:44 pm
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.
October 9, 2019 at 4:07 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply