Поиск записей с повторяющимся серийным номером

Мне было поручено сегодня очистить данные из отчета SSRS, собранного программистом БД. Первоначальный отчет заключался в том, чтобы найти дубликаты серийных номеров в течение определенного пользователем периода времени. Мне был предоставлен файл Excel с набором данных результата, и он очень пахнул, поэтому я решил пойти в SSRS и узнать, что сделал DBA.

Оказывается, человек, который запросил отчет, не очень хорошо его объяснил, и наша команда служб данных была перегружена работой, поэтому набор результатов был фиктивным, и отчет занял более 30 минут. Это выглядело как отчаянная попытка сделать операцию self join -ish и имела SELECT DISTINCT *, в котором был добавлен большой красный флаг.

Поэтому я решил переписать его.

Фон

  1. Некоторое количество заказов было обработано как дубликаты, что привело к продажам нескольких покрытий для одного и того же подразделения, а также к проблемам с выставлением счетов и т. д.

  2. Мы хотели разобрать отмененные вручную заказы (Status = 'CAN') из удаленных заказов (Status = 'DEL'), а удаленные - это то, что мы искали, поскольку удаленные заказы не отображаются ни в одном из приложений фактурирования, обслуживания и т. Д., Поэтому они больше трудно отследить (по дизайну).

  3. Отчет ниже работает примерно через 3 минуты. Я ничего не знаю о XML (так что, пожалуйста, не распинайте меня, и я тоже не написал этот код), но я подозреваю, что сотни и сотни строк форматирования после того, как SQL-запрос может испортить его. К сожалению, у меня нет возможности сравнить его с SSMS-производительностью только с SQL, поскольку у меня есть только доступ к SSRS. Любые советы по очистке избыточного /ненужного XML приветствуются.

Так вот. Любые советы по эффективности, передовой практике и т. Д. Оценены!

Оригинальный отчет: около 550 строк вернулись
Моя обновленная версия: возвращено 120 строк (на май '14)

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/Data Sources/Reporting</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID> REMOVED </rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@startdate">
            <Value>=Parameters!startdate.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@enddate">
            <Value>=Parameters!enddate.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>

USE Reporting;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @StartDate DATETIME;
--DECLARE @EndDate DATETIME;

-- Extract the list of serials for the desired accounts into a temp table
IF OBJECT_ID('tempdb..#ContractDupSerial') IS NOT NULL DROP TABLE #ContractDupSerial;

SELECT
    cdo.Serial_Number,
    cdo.Model_Number,
    cmo.Contract_Number,
    cmo.Dealer_ID,
    cmo.Status,
    cmo.Status_Date,
    cmo.CSR_ID
INTO #ContractDupSerial
FROM
Servicer_Reporting.dbo.Contract_Master_Original AS cmo
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
    ON cdo.Contract_Number = cmo.Contract_Number
    AND cdo.Data_Source = cdo.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS dh
    on dh.Dealer_ID = cmo.Dealer_ID
WHERE
dh.Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
AND cmo.Creation_Date &gt;= @startdate
AND cmo.Creation_Date &lt; @enddate

-- Filter to get only records where duplicate serials exist

SELECT     
    c.Serial_Number,
    c.Model_Number,
    c.Contract_Number,
    c.Dealer_ID,
    c.Status,
    c.Status_Date,
    c.CSR_ID
FROM #ContractDupSerial AS c
-- Clean up bogus data
WHERE c.Serial_Number IS NOT NULL
AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'
-- Filter out manual cancellations and entries
AND c.Serial_Number NOT IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    WHERE c.CSR_ID IS NOT NULL
    )
-- Filter in duplicate serial numbers
AND c.Serial_Number IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    GROUP BY c.Serial_Number
    HAVING COUNT(c.Serial_Number) > 1
    )
ORDER BY c.Serial_Number ASC, c.Contract_Number ASC;


</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Serial_Number">
          <DataField>Serial_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Model_Number">
          <DataField>Model_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Contract_Number">
          <DataField>Contract_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Dealer_ID">
          <DataField>Dealer_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status">
          <DataField>Status</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status_Date">
          <DataField>Status_Date</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="CSR_ID">
          <DataField>CSR_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.25in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Dealer ID</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox4">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Contract Number</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox4</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>

<!-- CONTINUE FOR HUNDREDS OF LINES -->

      </Page>

    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="startdate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create Start Date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="enddate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create End Date:</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportServerUrl> REMOVED </rd:ReportServerUrl>
  <rd:ReportID> REMOVED </rd:ReportID>
</Report>
11 голосов | спросил Phrancis 19 J0000006Europe/Moscow 2014, 05:24:45

2 ответа


5

Я собираюсь сосредоточиться на SQL в вашем вопросе.

В то время как для пользователя проще определить временные таблицы, обычно лучше определить таблицы #temp. Избегайте синтаксиса Select Into, если вы знаете типы данных. Для этого есть несколько преимуществ.

  1. Мы можем добавить первичные ключи и индексы в таблицу temp, чтобы повысить производительность.
  2. Mr. Теперь у обслуживающего персонала есть все ваши знания о входящих типах данных и ограничениях в таблице.
  3. Нам нужно только ввести данные, которые нам абсолютно необходимы. Не будет потерянной памяти или io.

Конечно, это означает, что мы должны использовать более строгий синтаксис Insert Into...Values(). Я думаю, что это стоит того времени.

Несколько других заметок о создании таблицы #ContractDupSerial.

  • Псевдонимы таблицы сокращены. Псевдонимы велики, но, как переменные, должны иметь значимые имена.
  • Mr. Maintainer отчаянно нуждается в комментарии рядом с оператором WHERE IN. Какие учетные записи представляют эти идентификаторы Account_ID?
  • Капитализация несовместима. (Придираться) литий>
  • Мне не нравится ваш отступ, но это в основном вопрос личных предпочтений. Я никогда не видел стандарта для отступов в SQL.

Обратите внимание, что мне нравится, что вы не используете этот Devil Between для вашей логики даты.

Вот как я его написал:

CREATE TABLE #ContractDupSerial (
    /*
        table definition here
    --*/

)

INSERT INTO #ContractDupSerial(
    Serial_Number,
    Model_Number,
    Contract_Number,
    Dealer_ID,
    Status,
    Status_Date,
    CSR_ID
    )
SELECT
    CDetail.Serial_Number,
    CDetail.Model_Number,
    CMaster.Contract_Number,
    CMaster.Dealer_ID,
    CMaster.Status,
    CMaster.Status_Date,
    CMaster.CSR_ID
FROM Servicer_Reporting.dbo.Contract_Master_Original AS CMaster
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS CDetail
    ON CDetail.Contract_Number = CMaster.Contract_Number
    AND CDetail.Data_Source = CDetail.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS Dealer
    ON Dealer.Dealer_ID = CMaster.Dealer_ID 
WHERE Dealer.Account_ID IN ( --The Smith & Johnson accounts
        102,108,103,107,138,139,142,147,148,162,163,169,170
        )
    AND CMaster.Creation_Date &gt;= @startdate
    AND CMaster.Creation_Date &lt; @enddate

Единственное, что я замечаю, это.

AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'

Подобные заявления дороги. Я не думаю, что это изменит план запроса для этого конкретного случая (он уже сканирует c.Serial_Number в любом случае), но последние два должны использовать равные. По крайней мере, это более семантически правильно.

AND c.Serial_Number <> ''
AND c.Serial_Number <> '.'

Я думаю, что в целом у вас есть довольно элегантное решение. Я пытался подумать о том, как я сам присоединился к таблице для этого запроса, и мне не нравится то, что я придумал. Я буду укладывать ваш образец в глубину моего сознания для будущей справки.

ответил RubberDuck 19 J0000006Europe/Moscow 2014, 06:48:26
5

Я заметил одно подозрительное условие соединения: cdo.Data_Source = cdo.Data_Source. Это всегда должно быть правдой, если обе стороны не являются NULL. Вы имели в виду сказать cdo.Data_Source IS NOT NULL? Или это условие соединения совершенно лишнее? (Я возьму последний).

Временные таблицы редко бывают хорошей идеей, я думаю. Они связаны с большим количеством операций ввода-вывода, и они вынуждают сложный запрос выполняться определенным образом, вместо того чтобы позволить оптимизатору запросов решать порядок соединения.

Вместо этого я предлагаю использовать Common Table Expressions для управления сложными запросами.

WITH Relevant_Dealers AS (
    SELECT Dealer_Id
        FROM Servicer_Reporting.dbo.Dealer_Handling
        WHERE Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
), Relevant_Contracts AS (     -- Your #ContractDupSerial
    SELECT cdo.Serial_Number
         , cdo.Model_Number
         , cmo.Contract_Number
         , cmo.Dealer_ID
         , cmo.Status
         , cmo.Status_Date
         , cmo.CSR_ID
        FROM Servicer_Reporting.dbo.Contract_Master_Original AS cmo
            INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
                ON cdo.Contract_Number = cmo.contract_Number
        WHERE cmo.Dealer_Id IN (SELECT Dealer_Id FROM Relevant_Dealers)
            AND cmo.Creation_Date >= @startdate
            AND cmo.Creation_Date < @enddate
), Dup_SN AS (
    SELECT Serial_Number
        FROM Relevant_Contracts
        GROUP BY Serial_Number
        HAVING COUNT(*) > 1
), Canceled_Contracts AS (
    SELECT Serial_Number
        FROM Relevant_Contracts
        WHERE CSR_ID IS NOT NULL
)
SELECT *
    FROM Relevant_Contracts
    WHERE Serial_Number IS NOT NULL
        AND Serial_Number IN (SELECT Serial_Number FROM Dup_SN)
        AND Serial_Number NOT IN (SELECT Serial_Number FROM Canceled_Contracts)
        -- Clean up bogus data...
        AND Serial_Number <> ''
        AND Serial_Number <> '.'
        AND Serial_Number NOT LIKE 'N/A%'
        AND Serial_Number NOT LIKE 'NA%'
        AND Serial_Number NOT LIKE 'X%'
        AND Serial_Number NOT LIKE '0%';
    ORDER BY Serial_Number ASC, Contract_Number ASC;
ответил 200_success 19 J0000006Europe/Moscow 2014, 08:39:29

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132