Вельмі павольны запыт на выдаленне

У мяне ёсць праблемы з прадукцыйнасцю SQL. Па раптоўнай прычыне наступных запытаў вельмі павольна:

У мяне ёсць два спісу, які змяшчае ідэнтыфікатары пэўнага табліцы. Мне трэба выдаліць усе запісы з першага спісу, калі Id ўжо існуе ў другім спісе:

DECLARE @IdList1 TABLE(Id INT)
DECLARE @IdList2 TABLE(Id INT)

-- Approach 1
DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id

-- Approach 2
DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)

Цалкам магчыма, што два спісу змяшчае больш за 10000 запісаў. У гэтым выпадку абодва запыту прымае кожны больш чым на 20 секунд, каб выканаць.

The execution plan also showed something I don't understand. Maybe that explains why it is so slow: Queryplan of both queries

Я запоўніў абодва спісу 10.000 паслядоўных цэлых лікаў, бо спіс утрымліваў значэнне 1-10.000 як адпраўной кропкі.

Як вы можаце бачыць, як запыты паказвае на @ IdList2 Фактычнае колькасць радкоў з'яўляецца 50.005.000 !!. @ IdList1 правільна ( Фактычнае колькасць радкоў з'яўляецца 10,000)

Я ведаю, што ёсць і іншыя рашэнні, як вырашыць гэтую праблему. Як запаўненне трэцяга спісу instaed выдалення з першага спісу. Але маё пытанне:

<�Моцны> Чаму гэтыя запыты выдаляць так павольна і чаму я бачу гэтыя дзіўныя планы запытаў?

14
Ці з'яўляецца гэта праблема, якая можа сустракацца ў рэальным свеце сцэнары або, як раз у гэтай сітуацыі спецыялізаванай для?
дададзена аўтар Jodrell, крыніца
@Jodrell - Засноўвалыя пытанні без якіх-небудзь статыстычных дадзеных на аснове кампіляцый для табліцы зменных (і адсутнасці карысных індэксаў на іх) з'яўляецца вельмі распаўсюджаным з'явай.
дададзена аўтар Martin Smith, крыніца

7 адказы

Дадаць першасны ключ для вашай зменнай табліцы і глядзець, як яны крычаць

DECLARE @IdList1 TABLE(Id INT primary Key not null)
DECLARE @IdList2 TABLE(Id INT primary Key not null)

таму што няма індэкса па гэтых параметрах табліцы, любы далучаецца або подзапросов павінны вывучыць парадку 10000 раз 10000 = 100000000 пара значэнняў.

14
дададзена
Ваш адказ і каментары разам з @MartinSmith быў велізарны крок наперад. Дзякуй!
дададзена аўтар hwcverwe, крыніца
Ці дапаможа гэта з паказчыкам на @ IdList1 ?
дададзена аўтар Jodrell, крыніца
@martin, я не чытаў, што рэчы на ​​некаторы час, так што я забыўся правілы, але гэта не выбар ўкладзеных цыклаў, таму што няма індэкса? Для іншых цыклічных алгарытмаў гэта не трэба індэкс для сартавання значэнняў? Акрамя таго, без індэкса, яна да гэтага часу не разглядаць кожную пару значэнняў - незалежна ад таго, што зацыклення алгарытму выкарыстоўвае для іх стварэння. - выключэнне складае, як Вы адзначаеце, зліццё далучыцца, але ён павінен іх сартаваць пошту перад адпраўкай на паштамт.
дададзена аўтар Charles Bretana, крыніца
@CharlesBretana - Не, гэта можа выкарыстоўваць хэш або зліццё, пакуль ёсць абсталююць далучыцца. Злучэнне зліцця запатрабуе сартаванне абодва ўваходу (як будзе стварэнне індэкса), але як толькі індэкс створаны, відавочна, з'яўляецца патэнцыйна больш карысным, паколькі яно будзе карысным для іншых запытаў (так +1)
дададзена аўтар Martin Smith, крыніца
«Любы далучаецца або подзапросов павінны вывучыць парадку 10000 раз 10000 = 100000000 пара значэнняў.» гэта дакладна толькі для укладзеных цыклаў. Хэш або зліццё будзе апрацоўваць кожны ўваход адзін раз (хоць зліццё таксама спатрэбіцца свайго родам)
дададзена аўтар Martin Smith, крыніца

SQL Server кампілюе план, калі пераменная табліца пустая і не перакампіляваць пры даданні радка. спрабаваць

DELETE FROM @IdList1
WHERE Id IN (SELECT Id FROM @IdList2)
OPTION (RECOMPILE)

Гэта будзе ўлічваць фактычная колькасць радкоў, якiя ўтрымлiваюцца ў таблічнай зменнай і пазбавіцца ад ўкладзенага плана завесы

Вядома, стварэнне індэкса на Id праз абмежаванне таксама можа быць карысным для іншых запытаў з выкарыстаннем зменнай табліцы таксама.

12
дададзена
Ваш адказ і каментары разам з @CharlesBretana быў велізарны крок наперад. Я desiced прыняць адказ Charels ', таму што я не магу прыняць два адказы;). Дзякуй!
дададзена аўтар hwcverwe, крыніца
дзякуй ... Даведаўся што-то сёння!
дададзена аўтар Charles Bretana, крыніца
зноў жа, аднак, так як cacheplan ствараецца для кожнага аператара, а не для ўсёй партыі ці захоўваемая працэдура, не стварае планы кэша для кожнага аператара ў партыі або ў парадку, перш чым яна пачне выконвацца?
дададзена аўтар Charles Bretana, крыніца
Гэта нова для мяне. Вы можаце ўдакладніць - The cacheplan першапачатковых кампіляцыі будуць адбывацца, калі ВЕЯТ сустракаюцца, праўда? Не тады, калі зменныя табліц абвешчаныя? Я маю на ўвазе, што план складаецца з'яўляецца для Delete, а не для аб'явы зменнай табліцы ... Калі гэта так, то ў гэтай кропцы будзе не зменныя табліц запаўняцца? Акрамя таго, калі вы не пярэчыце, не маглі б вы даць спасылку? Я хацеў бы прачытаць пра гэта.
дададзена аўтар Charles Bretana, крыніца
@CharlesBretana - Ёсць некаторыя спасылкі і прыклады кода ў мой адказ тут
дададзена аўтар Martin Smith, крыніца
@CharlesBretana - Ён збірае ўсе заявы ў пакеце перад яго выкананнем, за выключэннем, калі аператар спасылаецца на неіснуючы аб'ект і пазначаная для адкладзенага кампіляцыі. Так што ў гэтым выпадку DELETE Заява складаецца, калі зменныя табліцы з'яўляюцца пустымі. Тады (у сілу опцыі (RECOMPILE) ) ён атрымлівае перакампіляваць ў кропцы DELETE і можа прымаць да ўвагі фактычная колькасць радкоў пасля таго, як зменныя табліц запаўняюцца.
дададзена аўтар Martin Smith, крыніца

Магчымыя рашэнні:

1) Паспрабуйце стварыць індэксы, такім чынам,

1.1) Калі спіс {1 | 2} слупок .id мае унікальныя значэння, то вы можаце вызначыць унікальны кластарны індэкс, выкарыстоўваючы PK абмежаванні, як гэта:

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY);
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY);

1.2) Калі спіс {1 | 2} слупок .id могуць мець аднолькавыя значэння, то вы можаце вызначыць унікальны кластарны індэкс, выкарыстоўваючы PK абмежаванне з выкарыстаннем манекена IDENTITY калонкі, як гэта:

DECLARE @IdList1 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );
DECLARE @IdList2 TABLE(Id INT, DummyID INT IDENTITY, PRIMARY KEY (ID, DummyID) );

2) Паспрабуйце дадаць HASH JOIN Намёк запыт, як гэта:

DELETE list1
FROM @IdList1 list1
INNER JOIN @IdList2 list2 ON list1.Id = list2.Id
OPTION (HASH JOIN);
2
дададзена

Табліцы ў табліцах зменных могуць мець першасныя ключы, таму, калі вашы дадзеныя падтрымліваюць унікальнасць для іх Id s, Вы можаце быць у стане палепшыць прадукцыйнасць, перайшоўшы на

DECLARE @IdList1 TABLE(Id INT PRIMARY KEY)
DECLARE @IdList2 TABLE(Id INT PRIMARY KEY)
2
дададзена

Паспрабуйце гэты альтэрнатыўны сінтаксіс:

DELETE deleteAlias
FROM @IdList1 deleteAlias
WHERE EXISTS (
        SELECT NULL
        FROM @IdList2 innerList2Alias
        WHERE innerList2Alias.id=deleteAlias.id
    )

EDIT .....................

Паспрабуйце выкарыстоўваць #temp табліцу з індэксамі замест.

Вось агульны прыклад, дзе «DepartmentKey» з'яўляецца PK і FK.

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end


CREATE TABLE #Department 
( 
    DepartmentKey int , 
    DepartmentName  varchar(12)
)



CREATE INDEX IX_TEMPTABLE_Department_DepartmentKey ON #Department (DepartmentKey)




IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end


CREATE TABLE #Employee 
( 
    EmployeeKey int , 
    DepartmentKey int ,
    SSN  varchar(11)
)



CREATE INDEX IX_TEMPTABLE_Employee_DepartmentKey ON #Employee (DepartmentKey)


Delete deleteAlias 
from #Department deleteAlias
where exists ( select null from #Employee innerE where innerE.DepartmentKey = deleteAlias.DepartmentKey )





IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
begin
        drop table #Employee
end

IF OBJECT_ID('tempdb..#Department') IS NOT NULL
begin
        drop table #Department
end
1
дададзена
Калі вы можаце выкарыстоўваць #temp табліцы, паспрабаваць прыклад у маёй абароне.
дададзена аўтар granadaCoder, крыніца
Вы змушаныя выкарыстаць @ зменныя табліцы, ці вы можаце паспрабаваць #temp табліцу?
дададзена аўтар granadaCoder, крыніца
На жаль, гэта павольна таксама. Той жа вынік і сапраўды такі ж план запыту.
дададзена аўтар hwcverwe, крыніца

Вы карыстаецеся Табліца зменных , альбо дадаць першасны ключ табліцы або змяніць іх Часовыя табліцы і дадаць INDEX . Гэта прывядзе да значна больш высокай прадукцыйнасці. Як правіла, калі табліца толькі маленькая, выкарыстоўвайце Таблічны зменныя , аднак, калі табліца пашыраецца і ўтрымлівае шмат дадзеных, то альбо выкарыстоўваць часовую табліцу.

1
дададзена

Я быў бы спакуса паспрабаваць

DECLARE @IdList3 TABLE(Id INT);

INSERT @IdList3
SELECT Id FROM @IDList1 ORDER BY Id
EXCEPT
SELECT Id FROM @IDList2 ORDER BY Id

Не патрабуецца выдаленне.

0
дададзена
@oleksii праўда, ОП ўказвае на яго надуманы прыклад, звязаны з гэтымі двума зменнымі табліцамі і канкрэтна deletetion. Тым не менш, гэта ўсё яшчэ можа быць карысным для іншага чытача.
дададзена аўтар Jodrell, крыніца
Але што, калі ОП <�я> неабходна , каб выдаліць, як ён/яна кажа: Мне трэба выдаліць усе запісы з першага спісу, калі Id ўжо існуе ў другім спісе
дададзена аўтар oleksii, крыніца