Início / dba / Perguntas / 43682
Accepted
RateControl
Asked: 2013-06-04 06:46:26 +0800 CST

O plano de execução não corresponde ao procedimento armazenado

  • 772

Abaixo está um procedimento armazenado para contar o número de widgets feitos em um mês. Se nenhum widget for feito, nenhum registro existirá.

O plano de execução mostra uma INNER JOINdas tabelas M e A, onde na instrução estou fazendo um LEFT OUTER JOIN.

Eu gostaria de contar o número de widgets feitos em um período de tempo, juntar isso a uma tabela mensal (janeiro a dezembro) e ter os resultados mostrados em um relatório do SSRS. A junção é porque não consigo COUNTdados que não estão lá. Atualmente estou recebendo:

MonthName   Widget Count
February    2
March       3
April       4
May         6
June        5
July        4 
August      6
September   2
October     4
November    1
December    2

Gostaria de incluir na lista os meses em que não foram feitos widgets.

Aqui está o código:

DECLARE @OName varchar(50)
DECLARE @Start_Date DATE 
DECLARE @End_Date DATE

SET @OName = 'John'
SET @Start_Date = '01/01/2012'
SET @End_Date   = '12/31/2012'

SELECT   M.[MonthName]
    ,COUNT(A.[Widget_ID]) AS 'Widget Count'
FROM [Connector].dbo.[Months] AS M 
        LEFT OUTER JOIN  [SERVER].[DATABASE].[dbo].[Widget] AS A
        ON MONTH(A.[Widget_Date]) = M.[MonthID]  
WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')
          AND A.[PlantID] = '00000001'
          AND (
               (A.Widget_Date >= @Start_Date AND @End_Date IS NULL)
            OR (A.Widget_Datet <= @End_Date AND @Start_Date IS NULL)
            OR (A.Widget_Date >= @Start_Date AND A.Widget_Date <= @End_Date)
            OR (@Start_Date IS NULL AND @End_Date IS NULL)


GROUP BY  M.[MonthName], M.MonthID 
ORDER BY  M.[MonthID]

Abaixo está o plano de execução para esta consulta.Plano de execução

::UPDATE1:: Acabei de inserir os nomes dos meses nesta tabela. Não consigo alterar a tabela remota.

CREATE TABLE [dbo].[Months](
    [MonthID] [smallint] IDENTITY(1,1) NOT NULL,
    [MonthName] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED ([MonthID] ASC)
)
GO
INSERT INTO [dbo].[Months]
           ([MonthName])
     VALUES
           (<MonthName, varchar(25),>)
GO

A tabela "widget" tem mais de 250 campos, o que vai demorar muito para anonimizar.

t-sql sql-server-2012

1 respostas

  1. Best Answer

    Seu código diz que você está fazendo um LEFT OUTER JOIN, mas você está mesmo? Sua primeira WHEREcláusula (bem como cada uma a seguir) filtra linhas para inclusão da tabela externa:

    WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

    Isso transforma seu LEFT OUTER JOINem um INNER JOIN, quer você queira ou não. Talvez você quisesse mover esses filtros para a ONcláusula.

    É claro que eu provavelmente removeria a MONTH()função não sargável do JOIN, já que isso forçaria uma verificação completa na tabela remota e a escreveria desta maneira - eliminando também a junção à sua tabela local de meses:

    DECLARE 
  @OName      VARCHAR(50) = 'John', 
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats 
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS 
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT 
  m.m, 
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON 
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

    Apenas ignore a mcoluna na saída (você não pode ordená-la sem incluí-la na saída, e não faz sentido ordenar pelo nome).

    Outra sugestão: não use 'single quotes'como delimitadores de alias. As formas disso são obsoletas e também fazem com que os alias de coluna pareçam literais de string. Use [square brackets]em vez disso.

    • 7

relate perguntas