CROSS maciça JOIN no SQL Server 2005

votos
2

Eu sou portando um processo que cria um maciço CROSS JOINde duas tabelas. A tabela resultante contém registros 15m (parece que o processo faz uma cruz 30m juntar-se com uma mesa de 2,600 linha e uma mesa 12000 linha e, em seguida, faz algum agrupamento que deve dividi-lo ao meio). As linhas são relativamente estreitas - apenas 6 colunas. Tem sido executado por 5 horas com nenhum sinal de conclusão. Eu só notei a discrepância contagem entre o conhecido bom e que eu poderia esperar para o junção cruzada, por isso a minha saída não tem o agrupamento ou deduping que irá reduzir pela metade a mesa final - mas isso ainda parece que ele não está indo para completar qualquer tão cedo.

Primeiro eu vou olhar para eliminar esta tabela do processo, se possível - obviamente ele poderia ser substituído por aderir a ambas as tabelas individualmente, mas agora eu não tenho a visibilidade em todos os outros lugares ele é usado.

Mas dado que o processo existente faz isso (em menos tempo, em uma máquina menos potente, usando a linguagem FOCUS), existem opções para melhorar o desempenho de grandes CROSS JOINs em SQL Server (2005) (hardware não é realmente uma opção, esta caixa é um 64-bit 8-forma com 32-GB de memória RAM)?

detalhes:

Ele é escrito desta forma, em FOCUS (estou tentando produzir a mesma saída, que é um CROSS JOIN em SQL):

JOIN CLEAR *
DEFINE FILE COSTCENT
  WBLANK/A1 = ' ';
  END
TABLE FILE COSTCENT
  BY WBLANK BY CC_COSTCENT
  ON TABLE HOLD AS TEMPCC FORMAT FOCUS
  END

DEFINE FILE JOINGLAC
  WBLANK/A1 = ' ';
  END
TABLE FILE JOINGLAC
  BY WBLANK BY ACCOUNT_NO BY LI_LNTM
  ON TABLE HOLD AS TEMPAC FORMAT FOCUS INDEX WBLANK

JOIN CLEAR *
JOIN WBLANK IN TEMPCC TO ALL WBLANK IN TEMPAC
DEFINE FILE TEMPCC
  CA_JCCAC/A16=EDIT(CC_COSTCENT)|EDIT(ACCOUNT_NO);
  END
TABLE FILE TEMPCC
  BY CA_JCCAC BY CC_COSTCENT AS COST CENTER BY ACCOUNT_NO
  BY LI_LNTM
  ON TABLE HOLD AS TEMPCCAC
  END

Assim, a saída necessária é realmente um CROSS JOIN (é aderir a uma coluna em branco de cada lado).

Em SQL:

CREATE TABLE [COSTCENT](
       [COST_CTR_NUM] [int] NOT NULL,
       [CC_CNM] [varchar](40) NULL,
       [CC_DEPT] [varchar](7) NULL,
       [CC_ALSRC] [varchar](6) NULL,
       [CC_HIER_CODE] [varchar](20) NULL,
 CONSTRAINT [PK_LOOKUP_GL_COST_CTR] PRIMARY KEY NONCLUSTERED
(
       [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [JOINGLAC](
       [ACCOUNT_NO] [int] NULL,
       [LI_LNTM] [int] NULL,
       [PR_PRODUCT] [varchar](5) NULL,
       [PR_GROUP] [varchar](1) NULL,
       [AC_NAME_LONG] [varchar](40) NULL,
       [LI_NM_LONG] [varchar](30) NULL,
       [LI_INC] [int] NULL,
       [LI_MULT] [int] NULL,
       [LI_ANLZ] [int] NULL,
       [LI_TYPE] [varchar](2) NULL,
       [PR_SORT] [varchar](2) NULL,
       [PR_NM] [varchar](26) NULL,
       [PZ_SORT] [varchar](2) NULL,
       [PZNAME] [varchar](26) NULL,
       [WANLZ] [varchar](3) NULL,
       [OPMLNTM] [int] NULL,
       [PS_GROUP] [varchar](5) NULL,
       [PS_SORT] [varchar](2) NULL,
       [PS_NAME] [varchar](26) NULL,
       [PT_GROUP] [varchar](5) NULL,
       [PT_SORT] [varchar](2) NULL,
       [PT_NAME] [varchar](26) NULL
) ON [PRIMARY]

CREATE TABLE [JOINCCAC](
       [CA_JCCAC] [varchar](16) NOT NULL,
       [CA_COSTCENT] [int] NOT NULL,
       [CA_GLACCOUNT] [int] NOT NULL,
       [CA_LNTM] [int] NOT NULL,
       [CA_UNIT] [varchar](6) NOT NULL,
 CONSTRAINT [PK_JOINCCAC_KNOWN_GOOD] PRIMARY KEY CLUSTERED
(
       [CA_JCCAC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Com o código SQL:

INSERT IGNORE   INTO [JOINCCAC]
       (
        [CA_JCCAC]
       ,[CA_COSTCENT]
       ,[CA_GLACCOUNT]
       ,[CA_LNTM]
       ,[CA_UNIT]
       )
       SELECT  Util.PADLEFT(CONVERT(varchar, CC.COST_CTR_NUM), '0',
                                     7)
               + Util.PADLEFT(CONVERT(varchar, GL.ACCOUNT_NO), '0',
                                       9) AS CC_JCCAC
              ,CC.COST_CTR_NUM AS CA_COSTCENT
              ,GL.ACCOUNT_NO % 900000000 AS CA_GLACCOUNT
              ,GL.LI_LNTM AS CA_LNTM
              ,udf_BUPDEF(GL.ACCOUNT_NO, CC.COST_CTR_NUM, GL.LI_LNTM, 'N') AS CA_UNIT
       FROM   JOINGLAC AS GL
       CROSS JOIN COSTCENT AS CC

Dependendo de como esta tabela é usada posteriormente, deve ser capaz de ser eliminado do processo, simplesmente juntando para as tabelas originais usados para construí-lo. No entanto, esta é uma extremamente grande esforço portabilidade, e eu não poderia encontrar o uso da mesa por algum tempo, então eu queria saber se havia alguma truques para CROSS JOINing grandes mesas como que em tempo hábil (especialmente tendo em conta que o processo existente em foco é capaz de fazê-lo mais rapidamente). Dessa forma, eu poderia validar a correção do meu prédio da consulta de substituição e depois levar para fora com vistas ou o que quer.

Eu também estou considerando factoring o UDFs e seqüência de manipulação e realizando a CROSS JOIN primeiro a quebrar o processo um pouco.

Os resultados até agora:

Acontece que as UDFs não contribuir muito (negativamente) para o desempenho. Mas há também parece ser uma grande diferença entre uma linha 15m junção cruzada e uma 30m linha junção cruzada. Eu não tenho direitos SHOWPLAN (boo hoo), então não posso dizer se o plano que está usando é melhor ou pior depois de mudar índices. Eu não refeito ainda, mas estou esperando a tabela inteira para ir embora em breve.

Publicado 10/12/2008 em 04:01
fonte usuário
Em outras línguas...                            


3 respostas

votos
2

Continuando o que os outros um ditado, funções de banco de dados que continham perguntas que são usados ​​em um seleto sempre fiz minhas consultas extremamente lento. Em cima da minha cabeça, eu acredito que eu tinha uma consulta executado em 45 segundos, então eu removi a função, e em seguida resultado foi 0 segundos :)

Portanto, verifique udf_BUPDEF não está fazendo qualquer dúvida.

Respondeu 10/12/2008 em 07:00
fonte usuário

votos
2

Examinando que consulta mostra apenas uma coluna utilizada a partir de uma tabela, e apenas duas colunas utilizadas da outra tabela. Devido aos números muito baixos de colunas utilizadas, esta consulta pode ser facilmente aumentada com cobrindo índices:

CREATE INDEX COSTCENTCoverCross ON COSTCENT(COST_CTR_NUM)
CREATE INDEX JOINGLACCoverCross ON JOINGLAC(ACCOUNT_NO, LI_LNTM)

Aqui estão as minhas perguntas para maior otimização:

Quando você coloca a consulta no analisador de consultas e bater no botão "show estimado plano de execução", ele irá mostrar uma representação gráfica do que ele vai fazer.

Junte-Type: Deve haver um loop aninhado lá. (As outras opções são mesclagem e hash). Se você ver loop aninhado, então ok. Se você ver mesclagem ou hash, avise-nos.

Ordem de acesso à tabela: Vá todo o caminho até o topo e percorrer todo o caminho para a direita. O primeiro passo deve ser o acesso a uma mesa. Qual tabela é isso e qual método é usado (varredura de índice, verificação de índice em cluster)? Que método é usado para acessar outra tabela?

Paralelismo: Você deverá ver as pequenas setas jaggedy em quase todos os ícones no plano indicando que o paralelismo está sendo usado. Se você não vê isso, não é um grande problema!

Isso udf_BUPDEF me preocupa. Será que ler a partir de tabelas adicionais? Util.PADLEFT me preocupa menos, mas ainda .. o que é? Se não é um objeto de banco de dados, em seguida, considerar o uso desta vez:

RIGHT('z00000000000000000000000000' + columnName, 7)

Há algum gatilhos JOINCCAC? Como sobre índices? Com uma inserção dessa grande, você vai querer deixar cair todos os gatilhos e índices nessa tabela.

Respondeu 10/12/2008 em 06:41
fonte usuário

votos
1

Quebrar a consulta para torná-lo uma cruz simples planície participar.


   SELECT  CC.COST_CTR_NUM, GL.ACCOUNT_NO
              ,CC.COST_CTR_NUM AS CA_COSTCENT
              ,GL.ACCOUNT_NO AS CA_GLACCOUNT
              ,GL.LI_LNTM AS CA_LNTM
-- I don't know what is BUPDEF doing? but remove it from the query for time being
--              ,udf_BUPDEF(GL.ACCOUNT_NO, CC.COST_CTR_NUM, GL.LI_LNTM, 'N') AS CA_UNIT
       FROM   JOINGLAC AS GL
       CROSS JOIN COSTCENT AS CC

Ver o quão bom é juntar-se a cruz simples? (Sem quaisquer funções aplicada sobre ele)

Respondeu 10/12/2008 em 06:08
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more