Slow Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Slow Stored Procedure

Author

Topic

ddd1988
Starting Member

1 Post

Posted - 2014-12-04 : 17:42:50

Good day guys!

I am working with a simple stored procedure, which checks every record from the table "TEMP_Consolidado_Docs" and updates the correspondent record on the table Consolidado_Docs.
In consolidado_docs I have invoices with the number like '123456' and in temp_consolidado_docs the same invoice is like '0000123456' so the only goal is update the original record with the leading zeros. But this takes a lot of time.

It has been runing for 7 minutos and it has updated just 2819 records.

Both tables have around 500k records

This is the SP:

alter Procedure sp_Actualizar_Secuencial
As
Begin
set Nocount on;

--Variables del documento
declare @ID_Doc_Temp int, @NombreEmpresa varchar(25), @Serie varchar(25), @Secuencial varchar(25), @AFCDocumento varchar (25);
declare @ID_Doc int;
--Variables auxiliares
declare @Docs_Procesados int;

set @Docs_Procesados = 0;

print '----------------------------------------------------PROCESANDO REGISTROS----------------------------------------------------'
print 'Actualizando Secuenciales UCs'

declare @Contador int;
set @Contador = 0;
declare @Fin bit;
set @Fin = 'false'

declare @TOTAL int;
set @TOTAL = (Select count(ID_Documento) from TEMP_Consolidado_Docs);

print 'Total de registros a actualizar: [' + Convert(varchar(25), @TOTAL) + ']';

if (@TOTAL = 0)
Set @Fin = 'True';

print '----------------------------------------------------PROCESANDO REGISTROS----------------------------------------------------';
print 'Buscando Documentos a actualizar';

--exec sp_Actualizar_Secuencial
While @Fin = 'false'
Begin
(select Top (1) @ID_Doc_Temp = ID_Documento, @NombreEmpresa = NombreEmpresa, @Serie = Serie, @Secuencial = Secuencial, @AFCDocumento = AFCDocumento from TEMP_Consolidado_Docs);
/*set @NombreEmpresa = (select NombreEmpresa from TEMP_Consolidado_Docs where ID_Documento = @ID_Doc_Temp);
set @Serie = (select Serie from TEMP_Consolidado_Docs where ID_Documento = @ID_Doc_Temp);
set @Secuencial = (select Secuencial from TEMP_Consolidado_Docs where ID_Documento = @ID_Doc_Temp);
set @AFCDocumento = (select AFCDocumento from TEMP_Consolidado_Docs where ID_Documento = @ID_Doc_Temp);*/

print 'Datos TEMP-----------------------------------------------------------------------------------------------------';
print 'ID : ' + convert(varchar(25), @ID_Doc_Temp);
print 'Empresa : ' + @NombreEmpresa;
print 'Serie : ' + @Serie;
print 'Secuencial : ' + @Secuencial;
print 'AFC : ' + @AFCDocumento;

--Para comprobar que hay documento para actualizar
set @ID_Doc = isNull((select top(1) ID_Documento from Documentos where Doc_Index_1 = @NombreEmpresa and Doc_Index_2 = @AFCDocumento and Doc_Index_3 = @Serie and Doc_Index_4 = convert(varchar(25), convert(int, @Secuencial))) , 0)

print '@ID_Doc a actualizar: [' + convert(varchar(25), @ID_Doc) + ']';

if (@ID_Doc = 0)
Begin
print('Documento no encontrado o ya actualizado, borrando registro en temp_consolidado_docs')
delete TEMP_Consolidado_Docs where Id_Documento = @ID_Doc_Temp;
End

if (@ID_Doc != 0)
Begin

BEGIN TRY
Begin Transaction

update Documentos set Doc_Index_4 = @Secuencial where ID_Documento = @ID_Doc;
delete TEMP_Consolidado_Docs where ID_Documento = @ID_Doc_Temp;

commit transaction

print 'DOCUMENTO ACTUALIZADO ID: [' + convert(varchar(25), @ID_Doc) + '], Nombre: [' + @NombreEmpresa + '], Serie: [' + @Serie + '], Secuencial: [' + @Secuencial + '], AFC: [' + @AFCDocumento + ']';
set @Docs_Procesados = @Docs_Procesados + 1;
END TRY

BEGIN CATCH
--se deshace los inserts de la transacción
rollback transaction
print 'No se actualízó el secuencial!'
EXECUTE usp_GetErrorInfo;
END CATCH

print '';
End

set @Contador = @Contador + 1;
if (@Contador = @TOTAL) set @Fin = 'true';
print '# [' + convert(varchar(25), @Contador) + ']';

End

print 'Actualización de secuencial finalizado, documentos actualizados : ' + Convert(varchar(25),@Docs_Procesados ) + ' de ' + Convert(varchar(25), @Contador)

End

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 17:49:41

Simple stored procedure? NO.

Check the execution plan to determine where the performance problem is. Post the showplan xml if you'd like us to help. You are likely missing an index, or you need to change your code so that it doesn't do RBAR.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources