Optimizar consulta SQL

[carlos-paredes]

Que tal amigos, resulta que tengo unos problemas con unos querys de SQL en un paquete de SSIS, en SQL Server 2005 ó lo que se llamaba DTS en SQL Server 2000, el caso es que uno de los querys esta tardando mucho en hacer las operaciones, la tabla contra la que hago el FULL JOIN estoy probando con 1200 registros, se esta tardando aproximadamente 6 minutos el query, y queria saber de que forma lo puedo optimizar para que tarde menos, he intentado varias cosas como ahorrar unos SUBSTRING que debo utilizar para sacar cierto número de digitos y meterlos en otra tabla antes de hacer el join pero aún asi tarda mucho, no estoy usando SELECT * y el DISTINCT lo debo utilizar que he leido que son las cosas que hay que evitar a la hora de optimizar consultas, entre otras cosas. aqui dejo el query para mas o menos poder ilustrar la operación, saludos

if exists (select cod_centro from cencostos)
begin
INSERT CuentasCeco
select DISTINCT SUBSTRING(cod_centro,1,7) + SUBSTRING(RTRIM(LTRIM(ISNULL(ACTNUMBR_1,'')))+RTRI M(LTRIM(ISNULL(ACTNUMBR_2,'')))+RTRIM(LTRIM(ISNULL (ACTNUMBR_3,'')))+RTRIM(LTRIM(ISNULL(ACTNUMBR_4,'' )))+RTRIM(LTRIM(ISNULL(ACTNUMBR_5,'')))+RTRIM(LTRI M(ISNULL(ACTNUMBR_6,'')))+RTRIM(LTRIM(ISNULL(ACTNU MBR_7,'')))+RTRIM(LTRIM(ISNULL(ACTNUMBR_8,''))),8, 17)
,SUBSTRING(ACTDESCR,1,51)
,SUBSTRING(RTRIM(LTRIM(ACCTTYPE)),1,3)
,SUBSTRING(cod_centro,43,4)
,SUBSTRING(cod_centro,1,7)
from cencostos FULL JOIN gl00100 Cu ON 1=1
WHERE NOT EXISTS(SELECT ACTINDX FROM gl00100 WHERE RTRIM(LTRIM(ACTNUMBR_1))+RTRIM(LTRIM(ACTNUMBR_2))+ RTRIM(LTRIM(ACTNUMBR_3))+RTRIM(LTRIM(ACTNUMBR_4))+ RTRIM(LTRIM(ACTNUMBR_5))+RTRIM(LTRIM(ACTNUMBR_6))+ RTRIM(LTRIM(ACTNUMBR_7))+RTRIM(LTRIM(ACTNUMBR_8)) = SUBSTRING(cod_centro,1,7) + SUBSTRING(RTRIM(LTRIM(cu.ACTNUMBR_1))+RTRIM(LTRIM( cu.ACTNUMBR_2))+RTRIM(LTRIM(cu.ACTNUMBR_3))+RTRIM( LTRIM(cu.ACTNUMBR_4))+RTRIM(LTRIM(cu.ACTNUMBR_5))+ RTRIM(LTRIM(cu.ACTNUMBR_6))+RTRIM(LTRIM(cu.ACTNUMB R_7))+RTRIM(LTRIM(cu.ACTNUMBR_8)),8,17))
and SUBSTRING(cod_centro,1,7) + SUBSTRING(RTRIM(LTRIM(ISNULL(ACTNUMBR_1,'')))+RTRI M(LTRIM(ISNULL(ACTNUMBR_2,'')))+RTRIM(LTRIM(ISNULL (ACTNUMBR_3,'')))+RTRIM(LTRIM(ISNULL(ACTNUMBR_4,'' )))+RTRIM(LTRIM(ISNULL(ACTNUMBR_5,'')))+RTRIM(LTRI M(ISNULL(ACTNUMBR_6,'')))+RTRIM(LTRIM(ISNULL(ACTNU MBR_7,'')))+RTRIM(LTRIM(ISNULL(ACTNUMBR_8,''))),8, 17) not in (select Expr1 from CuentasCeco)
end

INSERT INTO CuentasCeco
SELECT 'CODIGO_COMPLETO','DESCRIPCION','BCE','CC1','CC2'

nota: la tabla cencostos apenas posee 33 registros y gl00100 posee 1200 registros

[Andres95]

Podrias postear la estructura de tus tablas, no me queda muy claro a quien pertenecen los campos que estas seleccionando.

trate de identar el query, podrias agregarle los alias correspondientes para que sea mas claro??

segun entendi, lo que necesitas es insertar en cuentaseco todas las combinaciones que resulten de cencostos y gl00100 que no hallan sido insertadas antes segun la concatenacion de strings... estoy en lo cierto?

Código:

if exists (select cod_centro from cencostos)
begin
INSERT CuentasCeco
SELECT DISTINCT 
 SUBSTRING(cod_centro,1,7) + 
 SUBSTRING(
         RTRIM(LTRIM(ISNULL(ACTNUMBR_1,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_2,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_3,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_4,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_5,''))) + 
         RTRIM(LTRIM(ISNULL(ACTNUMBR_6,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_7,''))) +
         RTRIM(LTRIM(ISNULL(ACTNUMBR_8,''))),8, 17)
,SUBSTRING(ACTDESCR,1,51)
,SUBSTRING(RTRIM(LTRIM(ACCTTYPE)),1,3)
,SUBSTRING(cod_centro,43,4)
,SUBSTRING(cod_centro,1,7)
from cencostos 
FULL JOIN 
     gl00100 Cu ON 1=1 
WHERE NOT EXISTS(SELECT ACTINDX 
                 FROM   gl00100 
                 WHERE  RTRIM(LTRIM(ACTNUMBR_1)) +
                        RTRIM(LTRIM(ACTNUMBR_2)) + 
                        RTRIM(LTRIM(ACTNUMBR_3)) +
                        RTRIM(LTRIM(ACTNUMBR_4)) + 
                        RTRIM(LTRIM(ACTNUMBR_5)) +
                        RTRIM(LTRIM(ACTNUMBR_6)) + 
                        RTRIM(LTRIM(ACTNUMBR_7)) +
                        RTRIM(LTRIM(ACTNUMBR_8)) = 
                        SUBSTRING(cod_centro,1,7) + 
                        SUBSTRING(
                              RTRIM(LTRIM(cu.ACTNUMBR_1)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_2)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_3)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_4)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_5)) + 
                              RTRIM(LTRIM(cu.ACTNUMBR_6)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_7)) +
                              RTRIM(LTRIM(cu.ACTNUMBR_8)), 8, 17))
                     and 
                        SUBSTRING(cod_centro,1,7) + 
                        SUBSTRING(
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_1,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_2,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_3,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_4,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_5,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_6,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_7,''))) +
                              RTRIM(LTRIM(ISNULL(ACTNUMBR_8,''))), 8, 17) 
                        not in (select Expr1 from CuentasCeco)
end

Saludos!

[carlos-paredes]

esta es la estructura de la tabla cencostos:

create table cencostos(
cod_centro varchar(255)
);

esta es la estructura de la tabla cuentas:

create table cuentas(
numero varchar(255)
);

esta es la estructura de la tabla CuentasCeco:

create table CuentasCeco(
Expr1 varchar(24),
Expr2 varchar(102),
Expr3 varchar(3),
Expr4 varchar(4),
Expr5 varchar(7)
)

esta es la estructura de la tabla gl00100:

CREATE TABLE [dbo].[GL00100](
[ACTINDX] [int] NOT NULL,
[ACTNUMBR_1] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_2] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_3] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_4] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_5] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_6] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_7] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_8] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_9] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTNUMBR_10] [char](7) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACTALIAS] [char](21) COLLATE Modern_Spanish_CI_AS NOT NULL,
[MNACSGMT] [char](67) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ACCTTYPE] [smallint] NOT NULL,
[ACTDESCR] [char](51) COLLATE Modern_Spanish_CI_AS NOT NULL,
[PSTNGTYP] [smallint] NOT NULL,
[ACCATNUM] [smallint] NOT NULL,
[ACTIVE] [tinyint] NOT NULL,
[TPCLBLNC] [smallint] NOT NULL,
[DECPLACS] [smallint] NOT NULL,
[FXDORVAR] [smallint] NOT NULL,
[BALFRCLC] [smallint] NOT NULL,
[DSPLKUPS] [binary](4) NOT NULL,
[CNVRMTHD] [smallint] NOT NULL,
[HSTRCLRT] [numeric](19, 7) NOT NULL,
[NOTEINDX] [numeric](19, 5) NOT NULL,
[CREATDDT] [datetime] NOT NULL,
[MODIFDT] [datetime] NOT NULL,
[USERDEF1] [char](21) COLLATE Modern_Spanish_CI_AS NOT NULL,
[USERDEF2] [char](21) COLLATE Modern_Spanish_CI_AS NOT NULL,
[PostSlsIn] [smallint] NOT NULL,
[PostIvIn] [smallint] NOT NULL,
[PostPurchIn] [smallint] NOT NULL,
[PostPRIn] [smallint] NOT NULL,
[ADJINFL] [tinyint] NOT NULL,
[INFLAREV] [int] NOT NULL,
[INFLAEQU] [int] NOT NULL,
[ACCTENTR] [tinyint] NOT NULL,
[USRDEFS1] [char](31) COLLATE Modern_Spanish_CI_AS NOT NULL,
[USRDEFS2] [char](31) COLLATE Modern_Spanish_CI_AS NOT NULL,
[DEX_ROW_ID] [int] NOT NULL
) ON [PRIMARY]

estas en lo correcto, esa es la combinación, la aplicación importa dos archivos de texto con información de cuentas y centros de costos, luego eso se combina con la tabla gl00100, y de alli se importa otro archivo de texto, que es el producto final que arroja el paquete

[carlos-paredes]

el archivo de costos.txt tiene una estructura como la siguiente:

3100000VP. EJE. PERSONAS Y NEG COMERCIALES0510

claro tiene varios registros, esa información se inserta en la tabla cencostos, a través de un BULK INSERT

el archivo de cuentas.txt tiene un estructura como la siguiente:

44702100061000044DEPRECIACION DE EQUIPOS AIRE ACONDICIONADO DET

de igual forma esa información se inserta en la tabla cuentas con el BULK INSERT, antes del query que coloque al principio, hay 2 que le anteceden, me avisas si quieres que los coloque para que entiendas mejor la aplicación, gracias

[Andres95]

Como comentario, Si utilizas Bulk insert puedes formatear directamente el archivo para que te lo cargue en una tabla con campos separados y no tener que hacer uso de substrings....

Por otro lado, no se si te serviria un cross join, el cual es un producto cartesiano entre dos tablas.

Asi se hace el producto cartesiano de los registros y se insertan todos aquellos que no hallan sido insertado antes...


Y con lo dicho quedaria algo como:

Código:

if (select count(1) from cencostos) > 0 
BEGIN
   INSERT CuentasCeco(Expr1, Expr2, Expr3, Expr4, Expr5)
   Select S.Expr1, S.Expr2, S.Expr3, S.Expr4, S.Expr5
   FROM  (SELECT DISTINCT 
          SUBSTRING(cs.cod_centro,1,7) + 
          SUBSTRING(
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_1,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_2,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_3,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_4,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_5,''))) + 
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_6,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_7,''))) +
                  RTRIM(LTRIM(ISNULL(Cu.ACTNUMBR_8,''))),8, 17) AS Expr1
         ,SUBSTRING(Cu.ACTDESCR,1,51)                           AS Expr2
         ,SUBSTRING(RTRIM(LTRIM(Cu.ACCTTYPE)),1,3)              AS Expr3
         ,SUBSTRING(cs.cod_centro,43,4)                         AS Expr4
         ,SUBSTRING(cs.cod_centro,1,7)                          AS Expr5
         FROM cencostos  cs
         CROSS JOIN 
              gl00100 Cu ) S
   Left outer join
        CuentasCeco c
   On S.Expr1  = c.Expr1
   Where c.Expr1 is null
END

Cuenta como te fue....

Saludos!

[carlos-paredes]

no sabia que se podia formatear el archivo y dejarlo con campos separados en la tabla, voy a probar con eso, mil gracias, se aceptan mas sugerencias

[carlos-paredes]

help!!!!!!!!

[Andres95]

Comenta como te fue con el query!

no te sirvio?

que ajustes hay que hacerle??

Saludos!