Optimizing DATA COMPARED

Question

0.00/5 (No votes)

See more:

HI GUYS
someone can tell me if this that i made is correct?
or wich is the best practice CLUSTERED OR NOT CLUSTERED
right now i have a procedure that the main goal is to compare 5,4534,345 rows
vs 74,000 rows
this comparation take more less 2 hrs..
But im loss.

Here is the code

SQL

---- THIS TEMP TABLE WILL SET : 5,453,345 ROWS


CREATE TABLE #int_registros_procesar_paso_2 (  id_registro_int int NOT NULL,
cod_ramo int NOT NULL,
cod_agente int NOT NULL,
cod_prefijo_company varchar(10) NOT NULL,
nro_pol_compania varchar(30),
nro_endoso_papel varchar(10),
CONSTRAINT PK_int_registros_procesar_paso_2 primary key clustered(cod_ramo asc,cod_agente asc,cod_prefijo_company asc,nro_pol_compania asc,nro_endoso_papel asc,id_registro_int asc)
)
ON [PRIMARY]
----HERE IS THE QUERY THAT FILL THE TEMP TABLE WITH 5,453,345 ROWS
INSERT INTO #int_registros_procesar_paso_2 
SELECT id_registro_int,cod_ramo,cod_agente,LTRIM(RTRIM(cod_prefijo_company)),
LTRIM(RTRIM(nro_pol_compania)),nro_endoso_papel 
FROM int_registros_procesar WITH(NOLOCK) 
WHERE cod_tipo_interface IN (1)
AND cod_ramo in (123) 
AND cod_grupo_endo=1
GROUP BY id_registro_int,cod_ramo,cod_agente,cod_prefijo_company,
         nro_pol_compania,nro_endoso_papel 

ORDER BY id_registro_int,cod_ramo,cod_agente,cod_prefijo_company,
         nro_pol_compania,nro_endoso_papel

---HERE IS WHERE THE DATA IS COMPARED

SQL

IF  EXISTS(SELECT 1 FROM #int_registros_procesar_paso_2 WITH(NOLOCK)
WHERE cod_ramo=123
AND cod_agente IS NOT NULL
AND cod_prefijo_company=LTRIM(RTRIM(@PrefijoPapel))--@PrefijoPapel='ABC' for example
AND nro_pol_compania=LTRIM(RTRIM(@FolioPapel)) --@FolioPapel='1234567' for example
AND nro_endoso_papel=0
AND id_registro_int IS NOT NULL)
BEGIN

another way to do this??
Thanks in advance

Forget this.
All shown above is inside of a "While" cycle
the 74,000 rows are assigned to the @PrefijoPapel AND @FolioPapel
One by One

SQL

ALTER PROC [dbo].[sp_EMITIR_CANCELACION_AUTO_ASEGURADORA_NUSIS3_SHORT]
@Fecha1 as DateTime,
@Fecha2 as DateTime
AS
BEGIN
SET NOCOUNT ON;
			DECLARE @Prefijo as varchar(3)
			DECLARE @Registro as int
			DECLARE @Folio as int
			DECLARE @Vehiculo as int
			DECLARE @Endoso as int
			DECLARE @MovEndoso as int
			DECLARE @iCant as int
			DECLARE @FolioMinimoCan as integer
			DECLARE @FolioMaximoCan as integer
			DECLARE @FechaIniCan as DateTime
			DECLARE @FechaFinCan as DateTime
			DECLARE @Prefijo_web_cancelacion as varchar(3)
			DECLARE @Folio_Papel as varchar(40)
			DECLARE @TotalP as integer
			DECLARE @TipoCancelacion as int
			DECLARE @ImpPrimaCobertura as float
			DECLARE @ImpPrimaPolicyFee as float
			DECLARE @ImpPrimaAsistencia as float
			DECLARE @ComDerecho as Float
			DECLARE @ComPrima as Float
			DECLARE @ComAsistencia as Float
			DECLARE @TotalVehiculos as int
			DECLARE @Producto as varchar(10)
			DECLARE @PrefijoPapel as Varchar(5)
			DECLARE @FolioPapel as Varchar(30)
			DECLARE @EndosoPapel as smallint
			DECLARE @PrimaXVehiculo as Float
			DECLARE @ErrorValor as int
			DECLARE @MensajeError as varchar(500)
			SET @FechaIniCan = @Fecha1
			SET @FechaFinCan = @Fecha2
			SET @Prefijo_web_cancelacion = 'AXW'
			SET @iCant = 1
					
					
					
			---------INICIAMOS LA CREACION DE TABLAS TEMPORALES
			CREATE TABLE #EndCan
			(
				Prefijo varchar(5) NOT NULL,
				Poliza int NOT NULL,
				Fecha DateTime NOT NULL,
				Usuario int NULL,
				Motivo int NULL,
				Comentarios nvarchar(510) NULL,
				Monto_devolver money NULL,
				Identificador int NOT  NULL,
				Fecha_Cancelacion DateTime NULL,
				Tipo int NULL,
				Comision_prima money NULL,
				Comision_derecho money NULL,
				Endoso_Poliza int NOT Null,
				Monto_Endoso money NOT NULL,
				Derecho_Póliza_devuelto money Null,
				COM_PRIMA float NULL,
				COM_DERECHO float NULL,
				Asistencia_devuelta Float NUll,
				CONSTRAINT PK_CancelacionesAutosNUIC primary key clustered(Prefijo asc,Poliza asc,Endoso_Poliza asc,Identificador asc)
			)
			ON [PRIMARY]
			--------------------------------------------------------------------------------------------------------------------------
			CREATE TABLE #PolizasCanceladas 
			(
				Prefijo varchar(5) NOT NULL,
				Folio int NOT NULL,
				Estado_de_Entrada varchar(4)  NULL,
				Motivo_de_Visita int NULL,
				Fecha_de_Inicio DateTime NOT NULL,
				Fecha_de_Emisión DateTime NOT NULL,
				Hora_de_Inicio varchar(5)  NULL,
				Nombre varchar(100)  NULL,
				Calle varchar(70) NULL,
				Colonia varchar(50) NULL,
				Código_Postal int NULL,
				Lada varchar(10) NULL,
				Teléfono varchar(20) NULL,
				Producto varchar(4) NULL,
				Estado varchar(2) NULL,
				Municipio varchar(50) NULL,
				Agente int NOT NULL,
				Prima money NULL,
				Derecho_de_Póliza MONEY NULL,
			    Derecho_de_Póliza_Original MONEY NULL,
				Asistencia MONEY NULL,
				Descuento FLOAT NULL,
				ExtraPrima MONEY NULL,
				Vigencia SMALLINT NULL,
				Fecha_de_Terminación DATETIME NULL,
				Comisión_prima DECIMAL(4,2) NULL,
				Comisión_derecho DECIMAL(4,2) NULL,
				Prefijo_papel VARCHAR(3),
				Folio_papel INT NULL,
				SubAgente INT NULL,
				Folio_papel_texto CHAR(20) NULL,
				CONSTRAINT PK_CanAutosNUIC primary key clustered(Prefijo asc,Folio asc,Agente asc)
			)
			ON [PRIMARY]
			-------------------------------------------------------------------------------------------------------------------------
			CREATE TABLE #VehiculoPolizaDetalles 
			(
				Prefijo varchar(5) NOT NULL,
				Folio int NOT NULL,
				Año int NOT NULL,
				Marca int NULL,
				Modelo varchar(255) NULL,
				Número_de_Serie varchar(20) NULL,
				Placas varchar(15) NULL,
				Estado_de_las_Placas varchar(2) NULL,
				Remolque bit NULL,
				Prima MONEY NULL,
				Estado_de_Entrada varchar(2) NULL,
				DP_adicional Money NULL,
				Vehículo smallint NOT NULL,
				CONSTRAINT PK_CancelacionAutosNUIC primary key clustered(Prefijo asc,Folio asc,Vehículo asc)
			)
			ON [PRIMARY]
			-------------------------------------------------------------------------------------------------------------------------
			CREATE TABLE #int_registros_procesar_paso_2 (  id_registro_int int NOT NULL,
			cod_ramo int NOT NULL,
			cod_agente int NOT NULL,
			cod_prefijo_company varchar(10) NOT NULL,
			nro_pol_compania varchar(30),
			nro_endoso_papel varchar(10),
			CONSTRAINT PK_int_registros_procesar_paso_2 primary key clustered(cod_ramo asc,cod_agente asc,cod_prefijo_company asc,nro_pol_compania asc,nro_endoso_papel asc,id_registro_int asc)
			)
			ON [PRIMARY]
			-------------------------------------------------------------------------------------------------------------------------
			CREATE TABLE #pv_datos_adic_paso_cancelacion (id_pv int NOT NULL,
			nro_pol_extendido varchar(30) NOT NULL,
			nro_endoso_papel int NOT NULL,
			cod_ramo int NOT NULL,
			cod_agente int NOT NULL,
			CONSTRAINT PK_pv_datos_adic_paso_cancelacion primary key clustered(cod_ramo asc,cod_agente asc,nro_pol_extendido asc,nro_endoso_papel asc,id_pv asc)
			)
			ON [PRIMARY]
			-------------------------------------------------------------------------------------------------------------------------
			
			INSERT INTO #EndCan		
			SELECT	EC.Prefijo,
					EC.Poliza ,
					EC.Fecha,
					EC.Usuario ,
					EC.Motivo,
					EC.Comentarios ,
					EC.Monto_devolver,
					EC.Identificador,
					EC.Fecha_Cancelacion,
					EC.Tipo,
					EC.Comision_prima,
					EC.Comision_derecho,
					EC.Endoso_Poliza ,
					EC.Monto_Endoso,
					EC.Derecho_Póliza_devuelto,
					EC.COM_PRIMA,
					EC.COM_DERECHO,
					EC.Asistencia_devuelta
			FROM NUSIS3.dbo.Endoso_Cancelacion EC
			WHERE EC.Fecha BETWEEN @FechaIniCan AND @FechaFinCan  AND Ec.Monto_Endoso > 0 
			AND EC.Fecha BETWEEN @FechaIniCan AND @FechaFinCan
			order by EC.Poliza,EC.identificador DESC
		
			INSERT INTO #PolizasCanceladas
			SELECT	SUBSTRING(Prefijo,1,3),
					Folio,
					SUBSTRING(Estado_de_Entrada,1,2),
					Motivo_de_Visita,
					Fecha_de_Inicio,
					Fecha_de_Emisión,
					Hora_de_Inicio,
					SUBSTRING(Nombre,1,100),
					SUBSTRING(Calle,1,70),
					SUBSTRING(Colonia,1,50),
					Código_Postal,
					SUBSTRING(Lada,1,5),
					SUBSTRING(Teléfono,1,20),
					SUBSTRING(Producto,1,4),
					SUBSTRING(Estado,1,2),
					SUBSTRING(Municipio,1,50),
					Agente,
					Prima,
					Derecho_de_Póliza,
					Derecho_de_Póliza_Original,
					Asistencia,
					Descuento,
					ExtraPrima,
					Vigencia,
					Fecha_de_Terminación,
					Comisión_prima,
					Comisión_derecho,
					Prefijo_papel,
					Folio_papel,
					SubAgente,
					Folio_papel_texto
    		FROM NUSIS3.dbo.PolizasParaCancelaciones
    		WHERE Prefijo=@Prefijo_web_cancelacion 
    		AND Folio in (SELECT DISTINCT Poliza FROM  NUSIS3.dbo.Endoso_Cancelacion EC WHERE Ec.Monto_Endoso > 0 
																					AND EC.Fecha BETWEEN @FechaIniCan AND @FechaFinCan)
    		
			INSERT INTO #VehiculoPolizaDetalles
      		SELECT	Prefijo ,
					Folio,
					Año,
					Marca,
					Modelo,
					Número_de_Serie,
					Placas,
					Estado_de_las_Placas,
					Remolque,
					Prima,
					Estado_de_Entrada,
					DP_adicional,
					Vehículo
    		FROM NUSIS3.dbo.VehiculosParaCancelaciones
    		WHERE Prefijo=@Prefijo_web_cancelacion 
    		AND Folio in (SELECT DISTINCT Poliza FROM  NUSIS3.dbo.Endoso_Cancelacion EC WHERE Ec.Monto_Endoso > 0 
																					AND EC.Fecha BETWEEN @FechaIniCan AND @FechaFinCan)
    		
    		SELECT *
    		INTO #ConductorPolizaCan
    		FROM NUSIS3.dbo.ConductoresParaCancelaciones 
    		WHERE Prefijo=@Prefijo_web_cancelacion 
    		AND Folio in (SELECT DISTINCT Poliza FROM  NUSIS3.dbo.Endoso_Cancelacion EC WHERE Ec.Monto_Endoso > 0 
																					AND EC.Fecha BETWEEN @FechaIniCan AND @FechaFinCan)
			
			--SELECT *
			--INTO #VehiculoCancelacionAutos
			--FROM SQLMONTERREY.NUSIS.dbo.VehiculosCancelacionAuto_Proporcional WHERE Prefijo=@Prefijo_web_cancelacion 
			--AND Folio IN (SELECT Folio FROM #PolizasCanceladas)
			-----VehiculoCancelacionAutos
			SELECT *
			INTO #Marca
			FROM NUSIS3.dbo.Marca_de_Auto 
			WHERE Subtipo_de_producto=1 AND Exclusivo_National=0
			
			SELECT *
			INTO #ProductoNUICSISE
			FROM TPROD_NUICSISE	
			
			SELECT *
			INTO #ConductorFake
			FROM dbo.ConductorFake
						

			
			INSERT INTO #int_registros_procesar_paso_2 
			SELECT id_registro_int,cod_ramo,cod_agente,LTRIM(RTRIM(cod_prefijo_company)),LTRIM(RTRIM(nro_pol_compania)),nro_endoso_papel 
			FROM int_registros_procesar WITH(NOLOCK) 
			WHERE cod_tipo_interface IN (1)
			AND cod_ramo in (123) 
			AND fec_emision>='20110101'
			AND cod_grupo_endo=1
			GROUP BY id_registro_int,cod_ramo,cod_agente,cod_prefijo_company,nro_pol_compania,nro_endoso_papel
			ORDER BY id_registro_int,cod_ramo,cod_agente,cod_prefijo_company,nro_pol_compania,nro_endoso_papel 

			
			


			INSERT INTO #pv_datos_adic_paso_cancelacion
			SELECT ph.id_pv,pda.nro_pol_extendido,Isnull(nro_endoso_papel,0) as nro_endoso_papel,cod_ramo,cod_agente
			FROM pv_datos_adic pda WITH(NOLOCK), pv_header ph WITH(NOLOCK)
			WHERE ph.id_pv = pda.id_pv
			AND ph.cod_ramo in (123,125,126,127)   
			AND ph.fec_emi>='20110901'
			
					
						
			SELECT 'EMPIEZA LA EMISION'
			SELECT @TotalP=COUNT(Poliza) FROM #EndCan
			WHILE (@iCant<=@TotalP)
			BEGIN
-----CONSULTA SI POLIZA EXISTE----------------------------------------------
SELECT TOP 1 @Folio=ECT.Poliza FROM #EndCan ECT
SELECT TOP 1 @Endoso=Identificador,
@ImpPrimaCobertura=Monto_Endoso,@ImpPrimaPolicyFee=Derecho_Póliza_Devuelto,@ImpPrimaAsistencia=Asistencia_Devuelta,
@ComPrima=Com_Prima,@ComDerecho=Com_Derecho,@EndosoPapel=Endoso_Poliza 
FROM #EndCan WHERE Prefijo=@Prefijo_web_cancelacion  
AND Poliza=@Folio AND Endoso_Poliza IS NOT NULL AND Identificador IS NOT NULL 
							
-------------------------------------------------------------------------------
SELECT  @Folio_Papel=cast(CONVERT(nvarchar(5),PA.Prefijo_Papel)+'-'+ CONVERT(nvarchar(20),PA.Folio_Papel_Texto) as nvarchar(50)),@Producto=PA.Producto,@PrefijoPapel=PA.Prefijo_Papel,@FolioPapel=PA.Folio_Papel_TextoFROM #PolizasCanceladas PA 
WHERE  PA.Prefijo=@Prefijo_web_cancelacion 
AND PA.Folio=@Folio
AND PA.Agente IS NOT NULL
							
SELECT @TotalVehiculos=count(1) FROM #VehiculoPolizaDetalles VP 
WHERE VP.Folio=@Folio 
AND VP.Prefijo=@Prefijo_web_cancelacion
AND VP.Vehículo IS NOT NULL

IF  EXISTS(SELECT 1 FROM #int_registros_procesar_paso_2 WITH(NOLOCK)
WHERE cod_ramo=123
AND cod_agente IS NOT NULL
AND cod_prefijo_company=LTRIM(RTRIM(@PrefijoPapel))--@PrefijoPapel='ABC' for example
AND nro_pol_compania=LTRIM(RTRIM(@FolioPapel)) --@FolioPapel='1234567' for example
AND nro_endoso_papel=0
AND id_registro_int IS NOT NULL)
BEGIN

the code shown above. is when i create and fill all the tmp tables. i think that in this part is the problem..
Thanks in advance

Posted 6-Dec-11 6:53am

Rene Bustos

Updated 6-Dec-11 14:38pm

Eduard Lu

v5

Add a Solution

Comments

FastEvo8 6-Dec-11 13:59pm

Where are @PrefijoPapel AND @FolioPapel coming from?
Are they in a table?

Rene Bustos 6-Dec-11 17:26pm

there i post the code where i get the value for the params :D thanks

[no name] 6-Dec-11 20:39pm

EDIT: added code block

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rene Bustos · Accepted Answer · 2012-02-23T07:40:00

Solution 2

reviewing the code, I realize, in the querys I did not need all the columns and I reduced the columns and greatly improved response time
Thanks people!!

Posted 23-Feb-12 7:40am

Rene Bustos

Wendelius · Accepted Answer · 2011-12-06T07:04:00

IMO the best way to enhance the comparison is to do this using sets. Since you didn't post where the values for your variables are actually coming, it's quite impossible to modify this correctly, but you could perhaps use something like:

SQL

SELECT *
FROM #int_registros_procesar_paso_2 t1, theOtherTable t2
WHERE t1.cod_ramo=123
AND t1.cod_agente IS NOT NULL
AND t1.cod_prefijo_company=LTRIM(RTRIM(t2.PrefijoPapel))
AND nro_pol_compania=LTRIM(RTRIM(t2.FolioPapel)) 
AND t1.nro_endoso_papel=0
AND t1.id_registro_int IS NOT NULL
AND other possible conditions to t2

Something like that should give you the matches for those tables.