优化数据比较 [英] Optimizing DATA COMPARED

查看：86
本文介绍了优化数据比较的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

HI GUYS
有人可以告诉我我做的这对吗?
还是wich是最佳实践CLUSTERED OR NOT CLUSTERED
现在我有一个程序，其主要目标是比较5,4534,345行
vs 74,000行
这种比较需要更少的2小时..
但是我很茫然.

这是代码


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


---- 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

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




另一种方法?
在此先感谢

算了吧.
上面显示的所有内容都在"While"周期内
将74,000行分配给@PrefijoPapel和@FolioPapel
一对一




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

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




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

推荐答案

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!!
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!!


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:
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:
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.


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


这篇关于优化数据比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆