Bloqueo de Procedimiento Almacenado

Tengo un problema con un procedimiento almacenado el cual utiliza un select que en algún momento del día se bloquea y no me envía el resultado del mismo, este procedimiento utiliza cursores 2 para ser exactos y tablas temporales 2 también, reemplace los stored procedures por ciclos while usando más tablas temporales y aun así ejecuto el query desde sql y todo funciona bien hasta que en cierto momento me bloquea, pasan y pasan minutos sin enviarme un solo registro... Ya use indices, ya use with (no lock), optimice la consulta reduciéndola a un solo cursor y a una sola temporal y aun así me bloquea, tendrá algo que ver que la consulta del cursor intervienen 2 tablas de 5 y 6 millones de registros y sigue aumentado día a día... Claro que usando los filtros correspondientes los registros se reducen... Dejo la estructura de las tablas y las consultas... Espero y me puedas ayudar
--Select del cursor
SELECT  ld.id_socio, ld.regpatronal, COUNT(*) AS dias, ld.baseem, ld.baseivcm, MIN(ld.fecha) AS fechamin,MAX(ld.fecha) AS fechamax,lda.cvecliente,lda.id_sucursal_cliente-- isnull(lda.cvecliente,@V_cvectedelphi),ISNULL(lda.id_sucursal_cliente,@V_id_sucursal_matriz)
    FROM CAPLISTADIARIA ld
    LEFT OUTER JOIN CAPLISTADIARIA_ASIGNADOS lda ON lda.id_plaza = ld.id_plaza and lda.id_sucursal = ld.id_sucursal and lda.id_empresa = ld.id_empresa and lda.fecha = ld.fecha AND lda.id_socio = ld.id_socio
    WHERE ld.id_plaza = @V_id_plaza AND ld.id_sucursal = @V_id_sucursal AND ld.id_empresa = @V_id_empresa AND ld.fecha >= @V_fechaini and ld.fecha <= @V_fechafin
    GROUP BY ld.id_socio, ld.regpatronal, ld.baseem, ld.baseivcm,lda.cvecliente,lda.id_sucursal_cliente
    ORDER BY ld.id_socio, ld.baseem
--FIN DEL SELECT DEL CURSOR
---TABLAS 1
CREATE TABLE [dbo].[CAPLISTADIARIA](
    [id_plaza] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_sucursal] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_empresa] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_socio] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [fecha] [datetime] NOT NULL,
    [regpatronal] [varchar](11) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [rendiario] [numeric](8, 2) NOT NULL CONSTRAINT [DF_CAPLISTADIARIA_rendiario]  DEFAULT ((0)),
    [baseem] [numeric](8, 2) NOT NULL,
    [baseivcm] [numeric](8, 2) NOT NULL,
    [status] [tinyint] NOT NULL CONSTRAINT [DF_CAPLISTA_DIARIA_status]  DEFAULT ((0)),
    [id_puesto] [varchar](17) COLLATE Modern_Spanish_CI_AS NULL,
    [zonasm] [varchar](1) COLLATE Modern_Spanish_CI_AS NULL,
    [clasert] [tinyint] NULL,
 CONSTRAINT [PK_CAPLISTA_DIARIA] PRIMARY KEY CLUSTERED
(
    [id_plaza] ASC,
    [id_sucursal] ASC,
    [id_empresa] ASC,
    [id_socio] ASC,
    [fecha] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--FIN DE TABLA 1
--TABLA 2
CREATE TABLE [dbo].[CAPLISTADIARIA_ASIGNADOS](
    [id_plaza] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_sucursal] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_empresa] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [cvecliente] [varchar](6) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [id_socio] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [fecha] [datetime] NOT NULL,
    [id_sucursal_cliente] [varchar](17) COLLATE Modern_Spanish_CI_AS NOT NULL,
    [cvesocio] [varchar](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_CAPLISTADIARIA_ASIGNADOS] PRIMARY KEY CLUSTERED
(
    [id_plaza] ASC,
[id_sucursal] ASC,
[id_empresa] ASC,
[cvecliente] ASC,
[id_socio] ASC,
[fecha] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--FIN DE TABLA 2
--Ejemplos de registros
--TABLA 1
INSERT INTO CAPLISTADIARIA VALUES('20061209102712153',    '20061209132936543',    '20061205101212920',    '20070209180559857'    ,'2007-05-01 00:00:00.000',    'E5354326103',    52.00,    52.00,    52.00,    0,    NULL,    NULL,    NULL)
--TABLA2
INSERT INTO CAPLISTADIARIA_ASIGNADOS VALUES('20061209102712153',    '20061209132936543',    '20061205101212920',    '000001',    '20070209180559857',    '2007-11-01 00:00:00.000',    '20070212120527750',    '0000001')
Respuesta
1
Me parece que no agregaste el codigo del procedure. Mandalo. Pregunta: en el select del curosr utilizas variables. Son los parametros del procedure?
Bueno, te dejo todo el código del procedimiento... he seguido investigando y me tope con los diferentes tipos de bloqueos, este procedimiento tiene un bloque de tipo esquema(Sch-S) no se si esto te pueda ayudar... gracias =)
---------------------------------------------------------------------------------------------------------------
alter procedure [dbo].[repdifsua_nomina]
@v_id_plaza                     varchar(17),
@v_id_sucursal               varchar(17),
@v_id_empresa             varchar(17),
@v_mesini                        varchar(2),
@v_mesfin                        varchar(2),
@v_fechaini                      datetime,
@v_fechafin                     datetime,
@v_diasperiodo              smallint,
@v_ejercicio                     varchar(4),
@v_cvectedelphi                           varchar(6),
@v_agrupado                  smallint
as
/*-------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
--Hecho por:
--Fecha:
--
--Función:
--Usado en:
---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------*/
begin
declare @v_id_socio                                                                    varchar(17)
Declare @v_regpatronal  varchar(11)
declare @v_dias                                                                                            smallint
declare @v_baseem                                                                    numeric(8,2)
Declare @v_baseivcm  numeric(8,2)
declare @v_fechamin                                                                 datetime
declare @v_fechamax                                                                datetime
declare @v_fechaimss                                                                datetime
declare @v_fechart                                                                      datetime
declare @v_fechasmg                                                                 datetime
declare @v_smgdf                                                                        numeric(8,2)
Declare @v_cvecliente  varchar(6)
declare @v_id_sucursal_cliente                                                             varchar(17)
declare @v_diasasignados                                                        smallint
declare @v_cvesocio                                                                   varchar(17)
declare @v_sdosysal                                                                   bit
declare @v_id_sucursal_matriz                                                              varchar(17)
--Variables para porcentajes del reg. patronal.
Declare @v_ppcf  numeric(8,5)
Declare @v_ppse  numeric(8,5)
Declare @v_pese  numeric(8,5)
Declare @v_pppe  numeric(8,5)
Declare @v_pepe  numeric(8,5)
Declare @v_pppd  numeric(8,5)
Declare @v_pepd  numeric(8,5)
Declare @v_ppg  numeric(8,5)
Declare @v_ppiv  numeric(8,5)
Declare @v_peiv  numeric(8,5)
Declare @v_ppcv  numeric(8,5)
Declare @v_pecv  numeric(8,5)
Declare @v_ppr  numeric(8,5)
Declare @v_ppf  numeric(8,5)
Declare @v_pprt  numeric(8,5)
--Variables para calcular las cuotas
Declare @v_cpcf  numeric(12,6)
Declare @v_cpse  numeric(12,6)
Declare @v_csse  numeric(12,6)
Declare @v_cppe  numeric(12,6)
Declare @v_cspe  numeric(12,6)
Declare @v_cppd  numeric(12,6)
Declare @v_cspd  numeric(12,6)
Declare @v_cpg  numeric(12,6)
Declare @v_cpiv  numeric(12,6)
Declare @v_csiv  numeric(12,6)
Declare @v_cpcv  numeric(12,6)
Declare @v_cscv  numeric(12,6)
Declare @v_cpr  numeric(12,6)
Declare @v_cpf  numeric(12,6)
Declare @v_cprt  numeric(12,6)
Declare @v_totalcp  numeric(12,6)
Declare @v_totalcs  numeric(12,6)
Declare @v_totalsua  numeric(12,6)
Declare @v_totalnom  numeric(12,6)
Declare @v_totalimss  numeric(12,6)
Declare @v_totalrcv  numeric(12,6)
Declare @v_totalfovi numeric(12,6)
Declare @v_rendiario numeric(12,6)
declare @v_diascotizados          smallint
declare @v_nss                                              varchar(20)
declare @v_isn                                               numeric(13,2)
declare @v_diasinc                        smallint
declare @v_tipold                          varchar(3)
declare @v_calcularfovi                              bit
declare @v_faltas                          int
declare @v_incapacidades         int
declare @tablatempm table (
[id_socio]                                                          [varchar] (17),
[cvesocio] [varchar] (7),
[dias] [smallint],
[baseem] [numeric] (8,2),
[baseivcm] [numeric] (8,2),
[totalimss] [numeric] (11,2),
[totalrcv] [numeric] (11,2),
[totalfovi] [numeric] (11,2),
[totalsua] [numeric] (11,2),
[totalnom] [numeric] (11,2),
[fechamin] [datetime],
[fechamax] [datetime],
[cvecliente] [varchar] (6),
[id_sucursal_cliente]                                    [varchar] (17),
[faltas] [int],
[incapacidades] [int])
select @v_sdosysal = sdosysal from catempresas where id_empresa = @v_id_empresa
declare cursocios cursor for
select  ld.id_socio, ld.regpatronal, count(*) as dias, ld.baseem, ld.baseivcm, min(ld.fecha) as fechamin,max(ld.fecha) as
fechamax,lda.cvecliente,lda.id_sucursal_cliente--
isnull(lda.cvecliente,@v_cvectedelphi),isnull(lda.id_sucursal_cliente,@v_id_sucursal_matriz)
from caplistadiaria ld with (nolock)
left outer join caplistadiaria_asignados lda with (nolock) on lda.id_plaza = ld.id_plaza and lda.id_sucursal = ld.id_sucursal and
lda.id_empresa = ld.id_empresa and lda.fecha = ld.fecha and lda.id_socio = ld.id_socio
where ld.id_plaza = @v_id_plaza and ld.id_sucursal = @v_id_sucursal and ld.id_empresa = @v_id_empresa and ld.fecha >= @v_fechaini and
ld.fecha <= @v_fechafin
group by ld.id_socio, ld.regpatronal, ld.baseem, ld.baseivcm,lda.cvecliente,lda.id_sucursal_cliente
order by ld.id_socio, ld.baseem
open cursocios
fetch next from cursocios into @v_id_socio, @v_regpatronal, @v_dias, @v_baseem, @v_baseivcm,
@v_fechamin,@v_fechamax,@v_cvecliente,@v_id_sucursal_cliente
while @@fetch_status = 0
begin
--inicializar variables
set @v_cpcf = 0
set @v_cpse = 0
set @v_csse = 0
set @v_cppe = 0
set @v_cspe = 0
set @v_cppd = 0
set @v_cspd = 0
set @v_cpg  = 0
set @v_cpiv = 0
set @v_csiv = 0
set @v_cpcv = 0
set @v_cscv = 0
set @v_cpr  = 0
set @v_cpf  = 0
set @v_cprt = 0
--para usar los dias del periodo de sua del delphi
if @v_dias > @v_diasperiodo
set @v_dias = @v_diasperiodo
select @v_fechaimss = max(fechaini) from tabcuotasimss with (nolock) where fechaini <= @v_fechamax
select @v_fechasmg  = max(fechaini) from tabsalariosminimos with (nolock) where fechaini <= @v_fechamax
select @v_fechart   = max(fechaini) from tabriesgodetrabajo with (nolock) where id_empresa = @v_id_empresa and id_plaza =
@v_id_plaza and id_sucursal = @v_id_sucursal and regpatronal = @v_regpatronal and fechaini <= @v_fechamax
--obtener porcentajes de descuento
select @v_ppcf = ppcf, @v_ppse = ppse, @v_pese = pese, @v_pppe = pppe, @v_pepe = pepe, @v_pppd = pppd, @v_pepd = pepd, @v_ppg =
ppg, @v_ppiv = ppiv, @v_peiv = peiv, @v_ppcv = ppcv, @v_pecv = pecv, @v_ppr = ppr, @v_ppf = ppf
from tabcuotasimss with (nolock)
where fechaini = @v_fechaimss
--obtener s.m.g.d.f.
select @v_smgdf = smgdf
from tabsalariosminimos with (nolock)
where fechaini = @v_fechasmg
--obtener porcentaje de riesgo de trabajo
select @v_pprt = pprt
from tabriesgodetrabajo with (nolock)
where id_empresa = @v_id_empresa and id_plaza = @v_id_plaza and id_sucursal = @v_id_sucursal and regpatronal = @v_regpatronal and
fechaini = @v_fechart
--inicializar las faltas e incapacidades
set @v_faltas                                   = 0
set @v_incapacidades  = 0
set @v_calcularfovi        = 1
--obtener clave del socio
select @v_cvesocio = cvesocio from catsocios_claves with (nolock)
where id_plaza = @v_id_plaza and id_sucursal = @v_id_sucursal and id_empresa = @v_id_empresa and id_socio = @v_id_socio
---------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
--Permite saber si el socio tuvo faltas o incapacidades en el periodo seleccionado
---------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
set @v_faltas    = (select count(fecha) from capfaltas with (nolock) where id_empresa = @v_id_empresa and cvesocio =
@v_cvesocio and (fecha between @v_fechamin and @v_fechamax) and id_plaza = @v_id_plaza and id_sucursal = @v_id_sucursal and cvecliente =
@v_cvectedelphi and ejercicio = @v_ejercicio)
set @v_incapacidades  =(select count(distinct b.fecha) from capincapacidades a  with (nolock)
left outer join capincapacidadesdetalle b with (nolock) on b.id_plaza = a.id_plaza and b.id_sucursal = a.id_sucursal and
b.id_empresa = a.id_empresa and b.regpatronal = a.regpatronal and b.cvesocio = a.cvesocio and b.folio = a.folio
where a.cvesocio = @v_cvesocio and (b.fecha between @v_fechamin and @v_fechamax) and a.id_plaza = @v_id_plaza and
a.id_sucursal = @v_id_sucursal and a.id_empresa = @v_id_empresa)
---------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
set @v_cpcf =(@v_smgdf * (@v_ppcf/100)) * (@v_dias - @v_incapacidades)
if @v_baseem > (3 * @v_smgdf)
begin
set @v_cpse = ((@v_baseem - (@v_smgdf * 3)) * (@v_ppse/100)) * (@v_dias - @v_incapacidades)
set @v_csse = ((@v_baseem - (@v_smgdf * 3)) * (@v_pese/100)) * (@v_dias - @v_incapacidades)
end
set @v_cppe = (@v_baseem * (@v_pppe/100)) * (@v_dias - @v_incapacidades)
set @v_cspe = (@v_baseem * (@v_pepe/100)) * (@v_dias - @v_incapacidades)
set @v_cppd = (@v_baseem * (@v_pppd/100)) * (@v_dias - @v_incapacidades)
set @v_cspd = (@v_baseem * (@v_pepd/100)) * (@v_dias - @v_incapacidades)
set @v_cpg  = (@v_baseem * (@v_ppg/100))    * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_cpiv = (@v_baseivcm * (@v_ppiv/100)) * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_csiv = (@v_baseivcm * (@v_peiv/100)) * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_cpcv = (@v_baseivcm * (@v_ppcv/100)) * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_cscv = (@v_baseivcm * (@v_pecv/100)) * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_cprt = (@v_baseem * (@v_pprt/100))   * (@v_dias - (@v_incapacidades + @v_faltas))
set @v_cpr = (@v_baseem * (@v_ppr/100)) * (@v_dias-@v_faltas)
if @v_sdosysal = 0
begin
if @v_calcularfovi = 1
set @v_cpf = (@v_baseivcm * (@v_ppf/100)) * (@v_dias-@v_faltas)
end
else
set @v_cpf = (@v_baseivcm * (@v_ppf/100)) * (@v_dias-@v_faltas)
set @v_totalsua  = 0
set @v_totalimss = 0
set @v_totalrcv  = 0
set @v_totalfovi = 0
set @v_totalimss = @v_cpcf + @v_cpse + @v_csse + @v_cppe + @v_cspe + @v_cppd + @v_cspd + @v_cpg + @v_cpiv + @v_csiv + @v_cprt
set @v_totalrcv  = @v_cpcv + @v_cscv + @v_cpr
set @v_totalfovi = @v_cpf
set @v_totalsua  = @v_totalimss + @v_totalrcv + @v_totalfovi
--
---------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------                              --agregar registro a tabla temporal
if not exists (select id_socio from @tablatempm where id_socio = @v_id_socio and cvecliente = @v_cvecliente and
id_sucursal_cliente = @v_id_sucursal_cliente)
begin
insert @tablatempm
(id_socio,cvesocio,dias,baseem,baseivcm,totalimss,totalrcv,totalfovi,totalsua,totalnom,fechamin,fechamax,cvecliente,id_sucursal_cliente,falta
s,incapacidades)
values (@v_id_socio,0, @v_dias, @v_baseem, @v_baseivcm,@v_totalimss, @v_totalrcv, @v_totalfovi, @v_totalsua,
0,@v_fechamin, @v_fechamax,@v_cvecliente,@v_id_sucursal_cliente,@v_faltas,@v_incapacidades)
end
else
begin
update @tablatempm set dias = dias + @v_dias, baseem = @v_baseem, baseivcm = @v_baseivcm, totalsua = totalsua +
@v_totalsua, totalimss = totalimss + @v_totalimss, totalrcv = totalrcv + @v_totalrcv, totalfovi = totalfovi + @v_totalfovi, fechamin =
@v_fechamin, fechamax = @v_fechamax,faltas = @v_faltas, incapacidades = @v_incapacidades
where id_socio = @v_id_socio and cvecliente = @v_cvecliente and id_sucursal_cliente = @v_id_sucursal_cliente
fetch next from cursocios into @v_id_socio, @v_regpatronal, @v_dias, @v_baseem, @v_baseivcm,@v_fechamin,
@v_fechamax,@v_cvecliente,@v_id_sucursal_cliente
continue
end
fetch next from cursocios into @v_id_socio, @v_regpatronal, @v_dias, @v_baseem, @v_baseivcm,
@v_fechamin,@v_fechamax,@v_cvecliente, @v_id_sucursal_cliente
end
close cursocios
deallocate cursocios
--fin de cursor de socios
--select a la tabla temporal
select * from @tablatempm
end
Hola Buen día ... gracias por tu interés problema solucionado... el bloqueo me lo hacia el select principal del cursor, utilice tablas temporales para evitar el join entre las 2 tablas enormes y ademas utilice transacciones separas... se me había cerado el mundo y la solución era evidente .. gracias de todas formas! Saludos

1 respuesta más de otro experto

Respuesta
1
SELECT  ld.id_socio, ld.regpatronal, COUNT(*) AS dias, ld.baseem, ld.baseivcm, MIN(ld.fecha) AS fechamin,MAX(ld.fecha) AS fechamax,lda.cvecliente,lda.id_sucursal_cliente-- isnull(lda.cvecliente,@V_cvectedelphi),ISNULL(lda.id_sucursal_cliente,@V_id_sucursal_matriz)
    FROM CAPLISTADIARIA ld (nolock)
    LEFT OUTER JOIN CAPLISTADIARIA_ASIGNADOS lda (nolock) ON lda.id_plaza = ld.id_plaza and lda.id_sucursal = ld.id_sucursal and lda.id_empresa = ld.id_empresa and lda.fecha = ld.fecha AND lda.id_socio = ld.id_socio
    WHERE ld.id_plaza = @V_id_plaza AND ld.id_sucursal = @V_id_sucursal AND ld.id_empresa = @V_id_empresa AND ld.fecha >= @V_fechaini and ld.fecha <= @V_fechafin
    GROUP BY ld.id_socio, ld.regpatronal, ld.baseem, ld.baseivcm,lda.cvecliente,lda.id_sucursal_cliente
    ORDER BY ld.id_socio, ld. Baseem
Prueba usando esos nolock...
Me comentas.
Ok, he seguido investigando sobre los tipos de bloque y la verdad hasta ahorita lo que he encontrado y alcanzado a compreender es el tipo de bloqueo que tengo en mi consulta. Ya utilice diferentes modos de saltarme el bloqueo pero este continua de todas formas, ya utilice el READPAST (omite registros), NOLOCK o READUNCOMMITTED, que parce ser tienen la misma funcionalidad pero repito aun así continua mi bloqueo. Ya rediseñe la el stored procedure eliminando cálculos dentro del cursor y enviándolos a otro procedimiento pero nada de esto funciona, elimine el ORDER BY, utilice un SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED *
    | READ COMMITTED *
    | SNAPSHOT *
    }
Y aun así nada de nada... estoy trabajando en rediseñar este query pero el detalle es que la info que necesito solo la puedo obtener de esas tablas.. . si puedes ayudarme de antemano gracias =)...
¿Cuéntame entre que procesos tienes bloqueos?
Debes usar el mismo orden siempre cuando haces transacciones sobre varias tablas, es decir: si actualizas primero la tabla y luego la TablaB siempre debe ser así en todos tus stores, revisa entre quien es el bloqueo y revisa los spids que lo generan usando el dbcc inputbuffer(spid)
Hola Buen día la verdad me había ciclado en mis posibles soluciones, después de varios días de estar investigando decidí deshacer el join entre las 2 tablas que te comente, inserte por separado en tablas temporales la información que necesitaba y posteriormente hice el join entre los temp, ademas utilice transacciones explicitas, elimine todos los cálculos que tenia dentro del cursor y las envíe a otro procedimiento. El error que podemos cometer muchos programadores es casarse con la misma idea y no buscar más alternativas (ojo con esto)... gracias por tu ayuda

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas