Как найти дубликаты в большой таблице на основе совпадающих и несовпадающих полей?

У меня есть очень большая таблица с более чем 10 миллионами записей. Я хочу найти дубликаты на основе совпадения некоторых полей и несоответствия некоторых полей в нем.

В настоящее время я использую запрос ниже:

SELECT DISTINCT MainTable.[lineitemid] 
FROM   [dbo].[lineitem] MainTable 
       INNER JOIN [dbo].[lineitem] AS ChildTable 
               ON ChildTable.invoicedate = MainTable.invoicedate 
                  AND LEFT(ChildTable.vendorname, 4) = LEFT(MainTable.vendorname, 4) 
                  AND ChildTable.invoiceid <> MainTable.invoiceid AND -- Invoice ID column not matching 
                  ChildTable.documentcurrencyamount = MainTable.documentcurrencyamount 
WHERE  ChildTable.lineitemid <> MainTable.lineitemid AND -- LineItemId is PK 
       MainTable.projectid = 1125 AND ChildTable.projectid = 1125 -- Duplicates should be identified with specific ProjectId

Этот запрос работает нормально, если количество записей для ProjectId меньше 100 000. Когда записей ProjectId больше 1 миллиона, при выполнении этого запроса размер базы данных tempdb увеличивается до 100 ГБ, что вызывает проблемы с нехваткой места на диске. Запрос выполняется навсегда.

Помогите, пожалуйста, оптимизировать запрос.

Добавлены следующие строки после получения ответа на приведенный выше запрос....

Большое спасибо, @Gordon-Linoff. Предложенный вами запрос работал намного быстрее. VendorName из другой таблицы. Могу ли я включить внутреннее соединение, как показано ниже?

SELECT li1.[LineItemId]
FROM [dbo].[LineItem] li1
INNER JOIN VendorMaster vm1 ON li1.VendorNumber=vm1.VendorNumber
AND vm1.CompanyCode = li1.CompanyCode
WHERE EXISTS (SELECT 1
              FROM [dbo].[LineItem] as li2  
            INNER JOIN VendorMaster vm2 on li2.VendorNumber = vm2.VendorNumber
              AND vm2.CompanyCode = li2.CompanyCode
              WHERE li2.InvoiceDate = li.InvoiceDate  and 
                    LEFT(li2.VendorName, 4) = LEFT(li1.VendorName, 4) and 
                    li2.InvoiceId <> li1.InvoiceId and  -- Invoice ID column not matching
                    li2.DocumentCurrencyAmount = li1.DocumentCurrencyAmount and
                    li2.LineItemId <> li1.LineItemId and
                    li2.ProjectId = li1.ProjectId
            li2.VendorNumber = li.VendorNumber)
AND li.ProjectId = 1125

Это эффективный подход?


sql sql-server duplicates query-optimization tempdb
person Manigandan Dorairaj    schedule 23.10.2014    source источник
comment
Вы смотрели на план выполнения? Какие индексы у вас есть на столе?   -  person Donal    schedule 23.10.2014
comment
@Donal, я создал индекс для ключевых полей, которые я использую для сопоставления и несоответствия - «СОЗДАТЬ НЕКЛАСТЕРНЫЙ ИНДЕКС [idx_KeyFields] ON [dbo].[LineItem] ([ProjectId], [VendorNumber]) INCLUDE ([LineItemId ],[InvoiceId],[InvoiceDate],[DocumentCurrencyAmount])'   -  person Manigandan Dorairaj    schedule 23.10.2014
comment
Я бы принял ответ Гордона - на вашем месте - он знает свое дело.   -  person Donal    schedule 23.10.2014
comment
@айман. . . Это должно быть разумным подходом к имени поставщика.   -  person Gordon Linoff    schedule 24.10.2014
comment
@GordonLinoff, спасибо. Я добавил внутреннее соединение в Select and Exists для объединения с основной таблицей Vendor по номеру поставщика и балансовой единице. Не могли бы вы увидеть измененный запрос (2-й) в вопросе. Пожалуйста, предложите мне какой-либо эффективный способ улучшить его производительность?   -  person Manigandan Dorairaj    schedule 28.10.2014

Ответы (1)


arrow_upward
2
arrow_downward

Менее затратный способ запустить этот запрос — использовать exists и обойтись без distinct:

SELECT li.[LineItemId]
FROM [dbo].[LineItem] li
WHERE EXISTS (SELECT 1
              FROM [dbo].[LineItem] as li2 on 
              WHERE li2.InvoiceDate = li.InvoiceDate and 
                    LEFT(li2.VendorName, 4) = LEFT(li.VendorName, 4) and 
                    li2.InvoiceId <> li.InvoiceId and  -- Invoice ID column not matching
                    li2.DocumentCurrencyAmount = li.DocumentCurrencyAmount and
                    li2.LineItemId <> li.LineItemId and
                    li2.ProjectId = li.ProjectId
WHERE MainTable.ProjectId = 1125;

Для производительности поможет индекс LineItem(ProjectId, InvoiceDate, DocumentCurrencyAmount, VendorName, InvoiceId, LineItemId). Вы можете еще больше ускорить запрос, объявив LEFT(LineItem.VendorName, 4) вычисляемым столбцом и добавив его в индекс перед VendorName.

person Gordon Linoff    schedule 23.10.2014
comment
спасибо, я попробую добавить вычисляемый столбец и изменить индекс, чтобы включить столбцы, которые вы предложили. - person Manigandan Dorairaj; 23.10.2014
comment
Большое спасибо, @Gordon-Linoff. Этот запрос работал намного быстрее. Я добавил объединение с другой таблицей, не могли бы вы посмотреть на мой пост и предложить? - person Manigandan Dorairaj; 23.10.2014