[tsql] trage query optimizen + warning no join predicate

[Y3PP3R]

Hello,

'k Heb een 3rdparty database met 500.000 rows in MS SQL Server Express 2008 en daar wil ik graag wat uit filteren, dus daarvoor heb ik een stored procedure geschreven. Als input gaat er een komma gescheiden lijst van id's in, en die split hij met een eigen geschreven split functie (die werkt prima overigens) en op die lijst met ID's doe ik een join, enfin, zie dit excerpt van mijn sp dat traag loopt (de id's zijn nu even hardcoded als voorbeeld):

SQL:

DECLARE @temp TABLE
(
    [id] INT, 
    [sent] DATETIME NULL, 
    [md5] NVARCHAR(45) NULL,
    [node_id] INT NOT NULL,
    [location] NVARCHAR(300),
    [subject] NVARCHAR(MAX),
    [sender] NVARCHAR(200),
    [recipients] NVARCHAR(MAX),
    [CC] NVARCHAR(MAX),
    [BCC] NVARCHAR(MAX)
)

INSERT @temp
SELECT [Items].[ItemID] AS [id], [Items].[EmailDate] AS [sent],
[Items].[MD5Hash] as [md5], [Items].[ParentID] AS [node_id],
[Items].[ItemFilePath] AS [location], [Items].[EmailSubject] AS [subject],
[Items].[EmailSender] AS [sender], [Items].[EmailRecipients] AS [recipients],
[Items].[EmailCCRecipients] AS [CC], [Items].[EmailBCCRecipients] AS [BCC]
FROM [maildb].[dbo].[Items] AS [Items]
INNER JOIN dbo.SplitString('1,2,3,4,5,6',',') AS [Split]
ON [Items].[ItemID] = [Split].[element]
ORDER BY [Items].[ItemID] ASC;

Dit duurt voor id's 1-6 ongeveer 1 minuut 9 seconden.
En het executionplan voor dit stukje sql is te vinden op: http://www.y3pp3r.nl/download/plan_spGetItems_except.sqlplan

Ik heb dus een trage query, heeft die mogelijk iets te maken met de warning 'no join predicate' die ik op een nested loop heb? En wat in deze query is die loop?

Edit: Nevermind, een rightjoin loste alles op, hoe logisch ook. Alleen snap ik de warning no join predicate nog steeds niet, en niet waarom hij nu weg is.

[ Voor 4% gewijzigd door Y3PP3R op 07-04-2010 17:27 . Reden: code leesbaarder gemaakt ]

[cariolive23]

Hier staat een uitleg over dit onderwerk: http://scarydba.wordpress.com/2009/09/15/no-join-predicate/

Verder had ik er nog nooit van gehoord, ik doe nauwelijks iets met SQL Server, verder dan dit kan ik je dus niet helpen.

[Y3PP3R]

Ok probleem is niet verholpen, het kost na andere nodige aanpassingen 1 seconde per record die je toevoegt aan de ITEMS string (@ITEMS = '1,2,3,4,5,6,7'). Volgens het executionplan zit 65% van de tijd in de insert into @temp table variable, maar daar doe ik toch niets fout?

Hij ziet er als volgt (compleet deze keer!) uit:

SQL:

IF OBJECT_ID('dbo.spGetItems', 'P') IS NOT NULL
DROP PROCEDURE dbo.spGetItems;
GO

CREATE PROCEDURE spGetItems
(
    @SOURCE SYSNAME,
    @ITEMS VARCHAR(MAX)
)
AS
BEGIN
    IF DB_ID(@SOURCE) IS NULL OR OBJECT_ID ('['+@SOURCE+'].[dbo].[Items]','U') IS NULL
    RETURN
    
    IF LEN(@ITEMS) = 0
        RETURN

    DECLARE @tsql NVARCHAR(MAX);
    SET @tsql =
    N'
    --declaration for temptable with copy of [items]
    DECLARE @temp TABLE
    ([id] INT, 
    [sent] DATETIME, 
    [md5] NVARCHAR(45),
    [node_id] INT,
    [location] NVARCHAR(300),
    [subject] NVARCHAR(MAX),
    [sender] NVARCHAR(200),
    [recipients] NVARCHAR(MAX),
    [CC] NVARCHAR(MAX),
    [BCC] NVARCHAR(MAX)
    )
    
    --declarations for hits_addresses
    DECLARE @md5 NVARCHAR(45), @sender NVARCHAR(200), @recipients NVARCHAR(MAX)
    DECLARE @CC NVARCHAR(MAX), @BCC NVARCHAR(MAX)
    DECLARE @email NVARCHAR(200), @emailID INT, @custID INT
    
    --fill temptable with hits from @ITEMS
    INSERT @temp
    SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent,
    Items.[MD5Hash] as md5, Items.[ParentID] AS node_id,
    Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject,
    Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients,
    Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC
    FROM ['+@SOURCE+'].[dbo].[Items] AS Items
    RIGHT JOIN dbo.SplitString('''+@ITEMS+''','','') AS Split
    ON Items.ItemID = Split.element
    ORDER BY Items.[ItemID] ASC;
    
    --Prepare QueryB to contain rownumbers per md5 to be able
    --to select only the first row. This trick is a substitution for MAX(location)
    --because location is a string and MAX doesnt work. We need only one location
    --per md5 because same md5==same msgfile.
    WITH QueryB AS (
    SELECT [md5], [sent], [location], [subject],
    rn = row_number() OVER (PARTITION BY md5 ORDER BY sent)
    FROM @temp
    )
    
    --fill hits with distinct items.
    INSERT INTO dbo.[hits]
    SELECT [md5], [sent], [location], [subject]
    FROM QueryB
    WHERE [rn] = 1 AND [md5] NOT IN (SELECT [md5] FROM dbo.[hits])
        
    --fill hits_nodes with every node that contains a specific md5
    INSERT INTO dbo.[hits_nodes]
    SELECT DISTINCT [md5], [node_id]
    FROM @temp
    WHERE [md5] IS NOT NULL AND [node_id] IS NOT NULL
    
    INSERT INTO dbo.[hits_nodes_errors]
    SELECT DISTINCT [md5], [node_id]
    FROM @temp
    WHERE [md5] IS NULL OR [node_id] IS NULL

    
    --some difficult stuff: split all the email addresses and enter them
    --in email_addresses if not existent. Next fill hits_addresses with relation type.
    DECLARE temp_cursor CURSOR FOR
    SELECT [md5], [sender], [recipients], [CC], [BCC]
    FROM @temp
    
    OPEN temp_cursor
    
    FETCH NEXT FROM temp_cursor
    INTO @md5, @sender, @recipients, @CC, @BCC

    WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC spInsertEmailRelation @md5, @sender, 1
            
            --insert recipients
            EXEC spInsertEmailRelation @md5, @recipients, 2

            --insert cc
            EXEC spInsertEmailRelation @md5, @CC, 3

            --insert bcc
            EXEC spInsertEmailRelation @md5, @BCC, 4
            
            --select custodian where email is found
            SET @custID = (
                SELECT TOP 1 custodians.id
                FROM @temp AS temp_table INNER JOIN
                nodes ON temp_table.node_id = nodes.id INNER JOIN
                custodians INNER JOIN
                discoveryjobs ON custodians.id = discoveryjobs.custodian_id
                ON nodes.discoveryjob_id = discoveryjobs.id
                WHERE temp_table.md5 = @md5)
            
            EXEC spInsertEmailRelation @md5, @custID, 5
            
            -- This is executed as long as the previous fetch succeeds.
            FETCH NEXT FROM temp_cursor
            INTO @md5, @sender, @recipients, @CC, @BCC
        END

    CLOSE temp_cursor
    DEALLOCATE temp_cursor

    SELECT [id], [md5], [sent], [node_id], [location], [subject]
    FROM @temp 
    GROUP BY [id], [md5], [sent], [node_id], [location], [subject]
    '

    EXEC(@tsql)
END

complete executionplan op: http://www.y3pp3r.nl/download/spGetItems_execplan2.sqlplan

Ik heb al gegoocheld met een normale tabel als tussentabel, maar dat loste het probleem ook niet op.

[ Voor 0% gewijzigd door Y3PP3R op 07-04-2010 17:29 . Reden: code leesbaarder gemaakt ]

[P_de_B]

Volgens het execution plan zit veruit de meeste tijd in het inserten in de @temp tabel, hij doet daar een clustered index insert. Kun je eens testen wat er gebeurd als je de @temp variabele door een echte tabel vervangt?

[pedorus]

offtopic:
Dit lijkt zo een 'debug het even voor mij'-vraagje. Zorg er in ieder geval eens voor dat de relevante code op de regel past, dan lees ik het misschien...

Bedoel je niet gewoon een "between 1 and 6" of een "in" ipv die gekke inner join op een splitstring?

[Y3PP3R]

dit is zeker geen eerste poging om het probleem op te lossen pedorus. Ik heb er al naar gekeken samen met andere mensen en we snappen niet waarom die insert zoveel tijd kost. En het is geen gekke 'join', ik weet niet meer waar het vandaan komt, maar deze join is sneller dan een IN.

@P_de_B, heb ik geprobeerd en dat werkt ook niet sneller. Als ik die select los uitvoer, dus zonder de INSERT, dan is hij instant klaar, ook met een string met 1000 items.

[pedorus]

Y3PP3R schreef op dinsdag 06 april 2010 @ 17:13:
dit is zeker geen eerste poging om het probleem op te lossen pedorus. Ik heb er al naar gekeken samen met andere mensen en we snappen niet waarom die insert zoveel tijd kost. En het is geen gekke 'join', ik weet niet meer waar het vandaan komt, maar deze join is sneller dan een IN.

Zou je me dan kunnen vertellen wat er sneller is aan een right join op dbo.splitString('1,2,3,4,5,6') dan een simpele "id in (1,2,3,4,5,6)" (in combinatie met passen als xml of exec als sql-injection geen probleem is), en wat die order by precies doet in een insert? Als je even zoekt zie je dat er vele oplossingen voor het array naar stored procedure-probleem zijn, en deze werkt blijkbaar traag, dus dan probeer je een andere lijkt me zo... Zo zou je bijvoorbeeld Table-Valued Parameters kunnen gebruiken.

[Y3PP3R]

Haha wijsneus
LINQ2SQL ondersteund in 3.5 en 4.0 geen table valued parameters. En als ik die select met Splitstring los uitvoer, zonder insert, met een string van 1000 items, heb ik mijn resultaat binnen de seconde op 't scherm staan.

En ter info, even de IN (SELECT ...) VS RIGHT JOIN:

SQL:

    DECLARE @temp TABLE
    ([id] INT, 
    [sent] DATETIME, 
    [md5] NVARCHAR(45),
    [node_id] INT,
    [location] NVARCHAR(300),
    [subject] NVARCHAR(MAX),
    [sender] NVARCHAR(200),
    [recipients] NVARCHAR(MAX),
    [CC] NVARCHAR(MAX),
    [BCC] NVARCHAR(MAX)
    )
    
    DECLARE @ITEMS NVARCHAR(MAX)
    SET @ITEMS = '1,2,3,4,5,6,7,8,9,10,11,12,13'
    
    INSERT @temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC
    FROM [enron_3].[dbo].[Items] AS Items
    WHERE Items.ItemID IN (SELECT element FROM dbo.SplitString(@ITEMS,','));
    
    INSERT @temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC
    FROM [enron_3].[dbo].[Items] AS Items
    RIGHT JOIN dbo.SplitString(@ITEMS,',') AS Split
    ON Items.ItemID = Split.element;
    
    INSERT tempitems SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC
    FROM [enron_3].[dbo].[Items] AS Items
    RIGHT JOIN dbo.SplitString(@ITEMS,',') AS Split
    ON Items.ItemID = Split.element;

Exec plan hier: www.y3pp3r.nl/download/joinvsin.sqlplan

Uitkomsten:
@temp + IN: 44% batch (subtree 208,5)
@temp + JOIN: 28% batch (subtree 133,3)
normale tabel + JOIN: 28% batch (subtree 133,3)

[pedorus]

Ja, dat is ook wel het slechtste uit 2 werelden natuurlijk - in is veelal trager dan andere oplossingen zoals join, maar hier is het toepasselijk vanwege het lijstje. Wat doet deze?

SQL:

    INSERT INTO @temp Exec('SELECT ItemID, EmailDate, MD5Hash, ParentID, ItemFilePath, 
        EmailSubject, EmailSender, EmailRecipients, EmailCCRecipients, 
        EmailBCCRecipients FROM Items WHERE ItemID IN (' + @ITEMS + ')');

[Y3PP3R]

Als ik die wil vergelijken met de rest en onder de andere queries plak in bovenstaand testje, verschijnt hij niet in het executionplan. Ik heb hem even in de sproc gestopt en getest, het blijft traag. Omdat nu de trage INSERT niet zoveel van de te verdelen %-en opslokt, kon ik zien welke andere delen traag zijn. Daar komt de email-cursor naar boven als traagste.
Misschien zeg ik nu iets doms, maar jij hebt in je voorbeeld de select vervangen door een EXEC(), maar de SELECT werkte supersnel, ook voor 1000 items, alleen de INSERT was traag?

[P_de_B]

Daarom zei ik dat je moest proberen de @temp variabele te vervangen door een echte tabel. De clustered index insert in de temp variabele lijkt de grootste bottleneck. Heb je dat al getest?

[Y3PP3R]

Heb ik dat niet gedaan in het voorbeeld hierboven (INSERT INTO tempitems)? Dat was evensnel als de @temp tabel. Daar had ik op de @temp tabel ook geen PK zitten. Of bedoel je iets anders, dan snap ik niet goed wat je bedoelt.

[pedorus]

Y3PP3R schreef op woensdag 07 april 2010 @ 13:53:
Als ik die wil vergelijken met de rest en onder de andere queries plak in bovenstaand testje, verschijnt hij niet in het executionplan. Ik heb hem even in de sproc gestopt en getest, het blijft traag. Omdat nu de trage INSERT niet zoveel van de te verdelen %-en opslokt, kon ik zien welke andere delen traag zijn. Daar komt de email-cursor naar boven als traagste.
Misschien zeg ik nu iets doms, maar jij hebt in je voorbeeld de select vervangen door een EXEC(), maar de SELECT werkte supersnel, ook voor 1000 items, alleen de INSERT was traag?

Tsja, het deelprobleem is nu opgelost, maar het grotere probleem blijft bestaan. In een goede stored procedure komen zowel cursors als temp-tables meestal niet voor (zeker niet zonder clustered index), en die zijn dus beiden af te raden voor beginners. Set-based logica is de juiste manier, en dat lijkt me hier prima mogelijk (ondanks dat ik spInsertEmailRelation niet ken).

[Y3PP3R]

Het deel probleem opgelost? Doordat je de EXEC() gebruikt verschijnt hij niet in het execution plan, vind ik niet 'opgelost'. Ik ben het wel met je eens dat set based logic beter is, maar er zitten een aantal keuzes in de sproc die ik niet makkelijk kan vertalen. Voor de compleetheid zal ik spInsertEmailRelation nog even posten:

SQL:

IF OBJECT_ID('dbo.spInsertEmailRelation', 'P') IS NOT NULL
DROP PROCEDURE dbo.spInsertEmailRelation;
GO
CREATE PROCEDURE spInsertEmailRelation
(
    @MD5 nvarchar(45),
    @EMAILS nvarchar(200),
    @RELATION INT
)
AS
BEGIN
    DECLARE @email NVARCHAR(200)
    DECLARE @count INT, @emailid INT
    
    IF (@RELATION = 5 AND ISNUMERIC(@EMAILS) = 1)
        BEGIN
            INSERT INTO dbo.hits_addresses ([md5], [email_address_id], [relation])
            VALUES (@MD5, @EMAILS, @RELATION)
        END
    ELSE
        BEGIN
            DECLARE my_cursor CURSOR FOR
            SELECT [element] FROM dbo.SplitString(@EMAILS, ';')
            WHERE [element] LIKE '%@%.%'
                        
            OPEN my_cursor

            FETCH NEXT FROM my_cursor
            INTO @email
            
            set @email = dbo.Trim(@email)
            
            WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    IF (@email > 0)
                    BEGIN
                        SET @count = (SELECT COUNT(*) FROM dbo.email_addresses
                                    WHERE [email] = @email)
                        IF (@count = 0)
                            INSERT INTO dbo.email_addresses (email) VALUES (@email)
                        
                        SET @emailid = (SELECT dbo.email_addresses.id
                                    FROM dbo.email_addresses
                                    WHERE dbo.email_addresses.email = @email)
                            
                        INSERT INTO dbo.hits_addresses ([md5], [email_address_id], [relation])
                        VALUES (@MD5, @emailid, @RELATION)
                    END
                    FETCH NEXT FROM my_cursor
                    INTO @email
                END
            
            CLOSE my_cursor
            DEALLOCATE my_cursor
        END
END

Het is niet de bedoeling dat jullie dit voor mij gaan vertalen naar set based logic, maar enig advies zou ik op prijs stellen.

[pedorus]

Y3PP3R schreef op woensdag 07 april 2010 @ 23:20:
Het deel probleem opgelost? Doordat je de EXEC() gebruikt verschijnt hij niet in het execution plan, vind ik niet 'opgelost'.

Gek, die insert into zou toch gewoon in het execution plan moeten staan. Daarnaast vraag ik me af hoe je hem in het grotere geheel hebt gezet, want daar deed je toch al een exec omheen zie ik nu pas.

Ik ben het wel met je eens dat set based logic beter is, maar er zitten een aantal keuzes in de sproc die ik niet makkelijk kan vertalen. Voor de compleetheid zal ik spInsertEmailRelation nog even posten:

Hints:

• De oorspronkelijke tabel zuigt en de verantwoordelijke hoort op zijn flikker te krijgen indien mogelijk.
• Relation type 5 is een eenvoudig geval dat hier niet hoort.
• Ipv splitString en een cursor kun je een tally table gebruiken (zie csv-voorbeeld).
• eerst een insert voor de nieuwe, gevonden emailadressen.
• dan een insert op hits_addresses
• zo samengevat kan deze losse stored procedure weg, en die splitString ook als die alleen in deze 2 sp's wordt gebruikt.

[Y3PP3R]

Ik zit de splitstring functie even te bekijken, en... die gebruikt een tally table! Erg bedankt verder!

SQL:

ALTER FUNCTION [dbo].[SplitString](@arr AS NVARCHAR(MAX), @sep AS CHAR(1))
  RETURNS TABLE
AS
RETURN
  SELECT
    (number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,
    dbo.TRIM(SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number)) AS element
  FROM dbo.Numbers
  WHERE number <= LEN(@arr) + 1
    AND SUBSTRING(@sep + @arr, number, 1) = @sep;

Edit:
Het kostte een lange dag maar dan heb je uiteindelijk set basic logica gebruikt voor je hele sproc. En dan duurt het 1 uur en 12 minuten om hem te runnen. Iemand nog advies?

SQL:

-- 1000 item id's
    DECLARE @ITEMS NVARCHAR(MAX)
    SET @ITEMS = '25201,25198,25195,25192,25189,25186,25183,25180,25177,25174,25171,25168,25165,25162,25159,25156,25153,25150,25147,25144,25141,25138,25135,25132,25129,25126,25123,25120,25117,25114,25111,25108,25105,25102,25099,25096,25093,25090,25087,25084,25081,25078,25075,25072,25069,25066,25063,25060,25057,25054,25051,25048,25045,25042,25039,25036,25033,25030,25027,25024,25021,25018,25015,25012,25009,25006,25003,25000,24997,24994,24991,24988,24985,24982,24979,24976,24973,24970,24967,24964,24961,24958,24955,24952,24949,24946,24943,24940,24937,24934,24931,24928,24925,24922,24919,24916,24913,24910,24907,24904,24901,24898,24895,24892,24889,24886,24883,24880,24877,24874,24871,24868,24865,24862,24859,24856,24853,24850,24847,24844,24841,24838,24835,24832,24829,24826,24823,24820,24811,24808,24805,24799,24796,24790,24787,24784,24781,24763,24742,24736,24733,24730,24718,24715,24703,24700,24694,24685,24682,24679,24670,24667,24664,24661,24652,24646,24637,24631,24628,24622,24616,24613,24610,24607,24604,24601,24598,24592,24589,24586,24583,24580,24577,24571,24565,24562,24553,24550,24541,24535,24529,24523,24520,24517,24511,24508,24484,24481,24478,24472,24463,24457,24454,24436,24433,24430,24424,24415,24412,24409,24403,24397,24376,24370,24352,24349,24346,24343,24340,24337,24334,24331,24328,24325,24322,24319,24316,24313,24310,24307,24304,24301,24298,24295,24292,24289,24286,24283,24280,24277,24274,24271,24268,24265,24262,24259,24256,24253,24250,24247,24244,24241,24238,24235,24232,24229,24226,24223,24220,24217,24214,24211,24208,24205,24202,24199,24196,24193,24190,23485,23482,23479,23470,23467,22816,22807,22801,22795,22789,22786,22780,22774,22768,22762,22756,22750,22744,22732,22726,22714,22708,22705,22699,22585,22582,22579,22576,22573,22570,22567,22564,22561,22558,22555,22522,22519,22498,22495,22492,22489,22486,22483,22480,22477,22474,22471,22468,22465,22462,22459,22456,22453,22411,22408,22405,22402,22399,22396,22393,22390,22387,22384,22381,22378,22375,22372,22369,22366,22363,22360,22357,22354,22351,22348,22345,22342,22339,22336,22333,22330,22327,22324,22321,22318,22315,22312,22309,22267,22264,22261,22258,22207,22204,22201,22198,22195,22192,22189,22186,22183,22180,22177,22174,22171,22168,22165,22162,22159,22156,22153,22150,22147,22144,22141,22138,22135,22039,22036,22033,22030,22018,21979,21976,21973,21970,21967,21940,21937,21934,21931,21928,21925,21922,21784,21781,21775,21772,21769,21766,21763,21760,21757,21754,21751,21748,21745,21742,21739,21736,21733,21730,21727,21724,21721,21718,21715,21712,21709,21706,21703,21700,21697,21694,21691,21688,21685,21682,21679,21676,21673,21670,21667,21664,21661,21658,21655,21652,21649,21646,21643,21640,21637,21634,21631,21628,21625,21622,21619,21616,21613,21610,21607,21604,21601,21598,21592,21586,21580,21574,21571,21568,21565,21562,21559,21556,21553,21124,21118,21109,21103,20113,20110,20107,20104,20101,20098,20095,20092,20089,20008,20005,20002,19999,19996,19993,19990,19987,19984,19981,19978,19975,19972,19969,19966,19963,19960,19957,19954,19951,19948,19945,19942,19939,19936,19933,19930,19927,19924,19921,19918,19915,19912,19909,19906,19903,19900,19897,19894,19891,19888,19885,19882,19879,19876,19873,19807,19804,19801,19798,19795,19792,19789,19786,19783,19780,19777,19774,19771,19768,19765,19762,19759,19756,19753,19750,19747,19744,19741,19738,19735,19732,19729,19726,19723,19720,19717,19714,19711,19105,19102,19099,19096,19093,19090,19087,19084,19081,19078,19075,19072,19069,19066,19063,19060,19057,19054,19051,19048,19045,19042,19039,19036,19033,19030,19027,19024,19021,19018,19015,19012,19009,19006,19003,19000,18997,18994,18991,18988,18985,18982,18979,18976,18973,18970,18967,18964,18961,18958,18955,18952,18949,18946,18943,18940,18937,18934,18931,18928,18925,18922,18919,18916,18913,18910,18907,18904,18901,18898,18895,18892,18889,18886,18883,18880,18877,18874,18871,18868,18865,18862,18859,18856,18853,18850,18847,18844,18841,18838,18835,18832,18829,18826,18823,18820,18817,18814,18811,18808,18805,18802,18799,18796,18793,18790,18787,18784,18781,18778,18775,18772,18769,18766,18763,18760,18757,18754,18751,18748,18745,18742,18739,18736,18733,18730,18727,18724,18721,18718,18715,18712,18709,18706,18703,18700,18697,18694,18691,18688,18685,18682,18679,18631,18628,18625,18622,18619,18616,18613,18610,18607,18604,18601,18598,18595,18592,18589,18586,18583,18580,18577,18574,18571,18568,18565,18562,18559,18556,18553,18550,18547,18544,18541,18538,18535,18532,18529,18526,18523,18520,18517,18514,18511,18508,18505,18502,18499,18496,18493,18490,18487,18484,18481,18478,18475,18472,18469,18466,18463,18460,18457,18454,18451,18448,18445,18442,18439,18436,18433,18430,18427,18424,18421,18418,18415,18412,18409,18406,18403,18400,18397,18394,18391,18388,18385,18382,18379,18376,18373,18370,18367,18364,18361,18358,18355,18352,18349,18346,18343,18340,18337,18334,18331,18328,18325,18322,18319,18316,18313,18310,18307,18304,18301,18298,18295,18292,18289,18286,18283,18280,18277,18274,18271,18268,18265,18262,18259,18256,18253,18250,18247,18241,18238,18235,18232,18226,18223,18220,18217,18208,18205,18202,18199,18157,18151,18148,18145,18142,17539,17536,17533,17530,17527,17524,17521,17518,17515,17512,17509,17506,17503,17500,17497,17494,17491,17410,17407,17404,17401,17398,17395,17392,17389,17386,17383,17380,17377,17374,17371,17368,17365,17362,16996,16993,16990,16987,16984,16981,16633,16267,16264,16261,16258,16255,16252,16249,16246,16243,16240,16237,16234,16231,16228,16225,16222,16219,16216,16213,16210,16207,16204,16201,16198,16195,16192,16189,16186,16183,16180,16177,16174,16171,15853,15850,415095,415092,415089,415086,415083,415080,415077,415074,415071,415068,415065,415062,415059,415056,415053,415050,415047,415044,415041,415038,415035,415032,415029,415026,415023,415020,415017,415014,415011,415008,415005,415002,414999,414996,414993,414990,414987,414984,414981,414978,414975,414972,414969,414966,414963,414960,414957,414954,414951,414948,414945,414942,414939,414936,414933,414930,414927,414924,414921,414918,414915,414912,414909,414906,414903,414900,414897,414894,414891,414888,414885,414882,414879,414876,414873,414870,414867'
    
--email adres separator
    DECLARE @sep CHAR
    SET @sep = ';'
    
    DECLARE @temp TABLE
    ([id] INT, 
    [sent] DATETIME, 
    [md5] NVARCHAR(45),
    [node_id] INT,
    [location] NVARCHAR(300),
    [subject] NVARCHAR(MAX),
    [sender] NVARCHAR(200),
    [recipients] NVARCHAR(MAX),
    [CC] NVARCHAR(MAX),
    [BCC] NVARCHAR(MAX)
    )
    
    INSERT INTO @temp Exec('SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC
    FROM ['+@SOURCE+'].[dbo].[Items] AS Items
    RIGHT JOIN dbo.SplitString('''+@ITEMS+''','','') AS Split
    ON Items.ItemID = Split.element;');
        
    WITH QueryB AS (
    SELECT [md5], [sent], [location], [subject],
    rn = row_number() OVER (PARTITION BY md5 ORDER BY sent)
    FROM @temp
    )
    
    --fill hits with distinct items.
    INSERT INTO dbo.[hits]
    SELECT [md5], [sent], [location], [subject]
    FROM QueryB q
    WHERE [rn] = 1
        AND NOT EXISTS (SELECT * FROM dbo.[hits] h WHERE h.md5 = q.md5)

    --fill hits_nodes with every node that contains a specific md5
    INSERT INTO dbo.[hits_nodes]
    SELECT DISTINCT t.[md5], t.[node_id]
    FROM @temp t
    WHERE t.[md5] IS NOT NULL
        AND t.[node_id] IS NOT NULL
        AND NOT EXISTS (SELECT * FROM dbo.hits_nodes h WHERE h.md5 = t.md5 )
    

    -- Insert sender addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT t.[Sender] 
        FROM @temp AS t
        GROUP BY t.sender

    -- make sender relations
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.[md5], e.[id], 1
        FROM @temp AS t
        INNER JOIN email_addresses AS e
        ON t.sender = e.email
        WHERE NOT EXISTS (SELECT * FROM hits_addresses h WHERE h.email_address_id = e.id AND h.relation = 1)
        GROUP BY t.[md5], e.[id]

    --insert all distinct recipients into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN @temp t
        WHERE n.number < LEN(@sep + t.[recipients] + @sep)
        AND SUBSTRING(@sep + t.[recipients] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1))
    
    --inserts all senders with relation to message into hits_addresses
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.md5 as MD5, e.id AS Email, 2
            FROM dbo.numbers n
            CROSS JOIN @temp t
            INNER JOIN email_addresses e
            ON dbo.Trim(SUBSTRING(@sep+t.recipients+@sep,n.number+1,CHARINDEX(@sep,@sep+t.recipients+@sep,n.number+1)-n.number-1)) = e.email
            WHERE n.number < LEN(@sep+t.recipients+@sep)
            AND SUBSTRING(@sep+t.recipients+@sep,n.number,1) = @sep
            GROUP BY t.md5, e.id

    --insert all distinct CC into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN @temp t
        WHERE n.number < LEN(@sep + t.[CC] + @sep)
        AND SUBSTRING(@sep + t.[CC] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1))
    
    --inserts all CC with relation to message into hits_addresses
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.md5 as MD5, e.id AS Email, 3
            FROM dbo.numbers n
            CROSS JOIN @temp t
            INNER JOIN email_addresses e
            ON dbo.Trim(SUBSTRING(@sep+t.[CC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[CC]+@sep,n.number+1)-n.number-1)) = e.email
            WHERE n.number < LEN(@sep+t.[CC]+@sep)
            AND SUBSTRING(@sep+t.[CC]+@sep,n.number,1) = @sep
            GROUP BY t.md5, e.id

    --insert all distinct BCC into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN @temp t
        WHERE n.number < LEN(@sep + t.[BCC] + @sep)
        AND SUBSTRING(@sep + t.[BCC] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1))
    
    --inserts all BCC with relation to message into hits_addresses
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.md5 as MD5, e.id AS Email, 4
            FROM dbo.numbers n
            CROSS JOIN @temp t
            INNER JOIN email_addresses e
            ON dbo.Trim(SUBSTRING(@sep+t.[BCC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[BCC]+@sep,n.number+1)-n.number-1)) = e.email
            WHERE n.number < LEN(@sep+t.[BCC]+@sep)
            AND SUBSTRING(@sep+t.[BCC]+@sep,n.number,1) = @sep
            GROUP BY t.md5, e.id

    --inserts all BCC with relation to message into hits_addresses
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.md5, c.id, 5
        FROM @temp t
        INNER JOIN dbo.hits_nodes h
        ON t.md5 = h.md5
        INNER JOIN dbo.nodes n
        ON h.node_id = n.id
        INNER JOIN dbo.discoveryjobs d
        ON n.discoveryjob_id = d.id
        INNER JOIN dbo.custodians c
        ON d.custodian_id = c.id

Executionplan op http://www.y3pp3r.nl/download/nw_getitems_plan.sqlplan

Op de queries INSERT INTO hits_addresses op de join met de email gaat alle tijd zitten. Kan ik dit nog versnellen?

[ Voor 96% gewijzigd door Y3PP3R op 09-04-2010 11:58 . Reden: advies opgevolgd, maar heeft niet geholpen ]

[Fiander]

Tja,
een paar kleine dingetje.
de eerste is al eerder voorgesteld.
Vervang de memory table door een echte tabel. ( @temp > #temp )
zodra je dat gedaan hebt, kun je voor de laatste querie indexes en een geclusterde index toevoegen. wat het joinen makkelijker maakt.

tweede.
is je express 2008 voledig gepatched ? tussen build 1600 en 2740 zit een wereld van verschil bij enkele queries.

derde
test deze ook eens tegen je database

code:

    CREATE TABLE #temp 
    ([id] INT,  
    [sent] DATETIME,  
    [md5] NVARCHAR(45), 
    [node_id] INT, 
    [location] NVARCHAR(300), 
    [subject] NVARCHAR(MAX), 
    [sender] NVARCHAR(200), 
    [recipients] NVARCHAR(MAX), 
    [CC] NVARCHAR(MAX), 
    [BCC] NVARCHAR(MAX) 
    ) 
     
    DECLARE @ITEMS NVARCHAR(MAX) 
    SET @ITEMS = '1,2,3,4,5,6,7,8,9,10,11,12,13' 
    
    SELECT element into #E FROM dbo.SplitString(@ITEMS,',')
    CREATE CLUSTERED INDEX  IX_C_Element ON #E ( element )
    INSERT INTO #temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC 
    FROM [enron_3].[dbo].[Items] AS Items 
    RIGHT JOIN #E AS Split 
    ON Items.ItemID = Split.element;

vierde
zou je je query nog eens kunnen draaien nadat je eerst
Set statistics io on
hebt gezet ?
en dan de output posten?

vijfde
Kun je in #temp niet een kolom opnemen met
[code]dbo.Trim(SUBSTRING(@sep+t.[BCC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[BCC]+@sep,n.number+1)-n.number-1))[/code]
dan kan je join een stuk goedkoper en zou je er een index op kunnen zetten.
laat maar, onderdeel van number tabel.... tis ook al laat...

[pedorus]

Het lijkt mis te gaan op die inner join met de emails. Kun je gebruik maken van with, zodat je de insert query zelf vereenvoudigd tot insert a select b inner join c? Bevat email_adresses wel een index op adres? Eventueel kun je dit ook samenvoegen met de vorige query, met behulp van merge. Probeer daarnaast eens een clustered index op zeg md5 aan @temp toe te voegen (#temp lijkt me niet zo'n goed idee).

[Fiander]

Ik zit zo nog eens te kijken,
en klopt het dat je drie keer hetzelfde doet ?

eerst de onbekende email adressen inserten en daarna per email adres opslaan welk bericht erbij hoort ?
en dat eerst voor de recipients dan voor de CC's en daarna nog eens voor de BCC's ?

wat houd je tegen om deze niet gewoon "dom" aan elkaar te plakken ?

code:

INSERT INTO dbo.email_addresses (email) 
    SELECT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1) - n.number - 1)) AS [Value] 
    FROM dbo.[numbers] n 
    CROSS JOIN @temp t 
    WHERE n.number < LEN(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep) 
    AND SUBSTRING(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number, 1) = @sep 
    AND NOT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1) - n.number - 1)) = '' 
    AND dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1) - n.number - 1)) LIKE '%@%.%' 
    GROUP BY dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep+ t.[CC] + @sep+t.[BCC]+@sep, n.number+1) - n.number - 1))

je hoeft dan alleen nog maar uit te zoeken om wat voor soort relatie het gaat.

en dat kan ook in één keer,

code:

        INSERT INTO dbo.hits_addresses (md5, email_address_id, relation) 
        SELECT X.MD5, E.ID, X.Relation FROM 
        (
            SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[recipients]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[recipients]+@sep,n.number+1)-n.number-1)) AS Email, 2 as Relation
            FROM dbo.numbers n 
            CROSS JOIN @temp t 
            WHERE n.number < LEN(@sep+t.[recipients]+@sep) 
            AND SUBSTRING(@sep+t.[recipients]+@sep,n.number,1) = @sep
            UNION ALL   
            SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[CC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[CC]+@sep,n.number+1)-n.number-1)) AS Email, 3 as Relation
            FROM dbo.numbers n 
            CROSS JOIN @temp t 
            WHERE n.number < LEN(@sep+t.[CC]+@sep) 
            AND SUBSTRING(@sep+t.[CC]+@sep,n.number,1) = @sep
            UNION ALL
            SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[BCC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[BCC]+@sep,n.number+1)-n.number-1)) AS Email, 4 as Relation
            FROM dbo.numbers n 
            CROSS JOIN @temp t 
            WHERE n.number < LEN(@sep+t.[BCC]+@sep) 
            AND SUBSTRING(@sep+t.[BCC]+@sep,n.number,1) = @sep
           ) X INNER JOIN email_addresses e
            ON X.email = e.email 
            GROUP BY X.md5, e.id

door de berekeningen voor de join met email_adresses te doen, heb je daar in de join geen last meer van.

[Y3PP3R]

Goedemiddag Ik ben redelijk blij!

Ik heb even geëxperimenteerd en dit is het snelste dat ik heb kunnen bouwen, samen met wat voorgestelde indexes. Dit duurt 21 seconden.

SQL:

SET STATISTICS IO ON
    GO
    
    DECLARE @ITEMS NVARCHAR(MAX)
    SET @ITEMS = N'25201,25198,25195,25192,25189,25186,25183,25180,25177,25174,25171,25168,25165,25162,25159,25156,25153,25150,25147,25144,25141,25138,25135,25132,25129,25126,25123,25120,25117,25114,25111,25108,25105,25102,25099,25096,25093,25090,25087,25084,25081,25078,25075,25072,25069,25066,25063,25060,25057,25054,25051,25048,25045,25042,25039,25036,25033,25030,25027,25024,25021,25018,25015,25012,25009,25006,25003,25000,24997,24994,24991,24988,24985,24982,24979,24976,24973,24970,24967,24964,24961,24958,24955,24952,24949,24946,24943,24940,24937,24934,24931,24928,24925,24922,24919,24916,24913,24910,24907,24904,24901,24898,24895,24892,24889,24886,24883,24880,24877,24874,24871,24868,24865,24862,24859,24856,24853,24850,24847,24844,24841,24838,24835,24832,24829,24826,24823,24820,24811,24808,24805,24799,24796,24790,24787,24784,24781,24763,24742,24736,24733,24730,24718,24715,24703,24700,24694,24685,24682,24679,24670,24667,24664,24661,24652,24646,24637,24631,24628,24622,24616,24613,24610,24607,24604,24601,24598,24592,24589,24586,24583,24580,24577,24571,24565,24562,24553,24550,24541,24535,24529,24523,24520,24517,24511,24508,24484,24481,24478,24472,24463,24457,24454,24436,24433,24430,24424,24415,24412,24409,24403,24397,24376,24370,24352,24349,24346,24343,24340,24337,24334,24331,24328,24325,24322,24319,24316,24313,24310,24307,24304,24301,24298,24295,24292,24289,24286,24283,24280,24277,24274,24271,24268,24265,24262,24259,24256,24253,24250,24247,24244,24241,24238,24235,24232,24229,24226,24223,24220,24217,24214,24211,24208,24205,24202,24199,24196,24193,24190,23485,23482,23479,23470,23467,22816,22807,22801,22795,22789,22786,22780,22774,22768,22762,22756,22750,22744,22732,22726,22714,22708,22705,22699,22585,22582,22579,22576,22573,22570,22567,22564,22561,22558,22555,22522,22519,22498,22495,22492,22489,22486,22483,22480,22477,22474,22471,22468,22465,22462,22459,22456,22453,22411,22408,22405,22402,22399,22396,22393,22390,22387,22384,22381,22378,22375,22372,22369,22366,22363,22360,22357,22354,22351,22348,22345,22342,22339,22336,22333,22330,22327,22324,22321,22318,22315,22312,22309,22267,22264,22261,22258,22207,22204,22201,22198,22195,22192,22189,22186,22183,22180,22177,22174,22171,22168,22165,22162,22159,22156,22153,22150,22147,22144,22141,22138,22135,22039,22036,22033,22030,22018,21979,21976,21973,21970,21967,21940,21937,21934,21931,21928,21925,21922,21784,21781,21775,21772,21769,21766,21763,21760,21757,21754,21751,21748,21745,21742,21739,21736,21733,21730,21727,21724,21721,21718,21715,21712,21709,21706,21703,21700,21697,21694,21691,21688,21685,21682,21679,21676,21673,21670,21667,21664,21661,21658,21655,21652,21649,21646,21643,21640,21637,21634,21631,21628,21625,21622,21619,21616,21613,21610,21607,21604,21601,21598,21592,21586,21580,21574,21571,21568,21565,21562,21559,21556,21553,21124,21118,21109,21103,20113,20110,20107,20104,20101,20098,20095,20092,20089,20008,20005,20002,19999,19996,19993,19990,19987,19984,19981,19978,19975,19972,19969,19966,19963,19960,19957,19954,19951,19948,19945,19942,19939,19936,19933,19930,19927,19924,19921,19918,19915,19912,19909,19906,19903,19900,19897,19894,19891,19888,19885,19882,19879,19876,19873,19807,19804,19801,19798,19795,19792,19789,19786,19783,19780,19777,19774,19771,19768,19765,19762,19759,19756,19753,19750,19747,19744,19741,19738,19735,19732,19729,19726,19723,19720,19717,19714,19711,19105,19102,19099,19096,19093,19090,19087,19084,19081,19078,19075,19072,19069,19066,19063,19060,19057,19054,19051,19048,19045,19042,19039,19036,19033,19030,19027,19024,19021,19018,19015,19012,19009,19006,19003,19000,18997,18994,18991,18988,18985,18982,18979,18976,18973,18970,18967,18964,18961,18958,18955,18952,18949,18946,18943,18940,18937,18934,18931,18928,18925,18922,18919,18916,18913,18910,18907,18904,18901,18898,18895,18892,18889,18886,18883,18880,18877,18874,18871,18868,18865,18862,18859,18856,18853,18850,18847,18844,18841,18838,18835,18832,18829,18826,18823,18820,18817,18814,18811,18808,18805,18802,18799,18796,18793,18790,18787,18784,18781,18778,18775,18772,18769,18766,18763,18760,18757,18754,18751,18748,18745,18742,18739,18736,18733,18730,18727,18724,18721,18718,18715,18712,18709,18706,18703,18700,18697,18694,18691,18688,18685,18682,18679,18631,18628,18625,18622,18619,18616,18613,18610,18607,18604,18601,18598,18595,18592,18589,18586,18583,18580,18577,18574,18571,18568,18565,18562,18559,18556,18553,18550,18547,18544,18541,18538,18535,18532,18529,18526,18523,18520,18517,18514,18511,18508,18505,18502,18499,18496,18493,18490,18487,18484,18481,18478,18475,18472,18469,18466,18463,18460,18457,18454,18451,18448,18445,18442,18439,18436,18433,18430,18427,18424,18421,18418,18415,18412,18409,18406,18403,18400,18397,18394,18391,18388,18385,18382,18379,18376,18373,18370,18367,18364,18361,18358,18355,18352,18349,18346,18343,18340,18337,18334,18331,18328,18325,18322,18319,18316,18313,18310,18307,18304,18301,18298,18295,18292,18289,18286,18283,18280,18277,18274,18271,18268,18265,18262,18259,18256,18253,18250,18247,18241,18238,18235,18232,18226,18223,18220,18217,18208,18205,18202,18199,18157,18151,18148,18145,18142,17539,17536,17533,17530,17527,17524,17521,17518,17515,17512,17509,17506,17503,17500,17497,17494,17491,17410,17407,17404,17401,17398,17395,17392,17389,17386,17383,17380,17377,17374,17371,17368,17365,17362,16996,16993,16990,16987,16984,16981,16633,16267,16264,16261,16258,16255,16252,16249,16246,16243,16240,16237,16234,16231,16228,16225,16222,16219,16216,16213,16210,16207,16204,16201,16198,16195,16192,16189,16186,16183,16180,16177,16174,16171,15853,15850,415095,415092,415089,415086,415083,415080,415077,415074,415071,415068,415065,415062,415059,415056,415053,415050,415047,415044,415041,415038,415035,415032,415029,415026,415023,415020,415017,415014,415011,415008,415005,415002,414999,414996,414993,414990,414987,414984,414981,414978,414975,414972,414969,414966,414963,414960,414957,414954,414951,414948,414945,414942,414939,414936,414933,414930,414927,414924,414921,414918,414915,414912,414909,414906,414903,414900,414897,414894,414891,414888,414885,414882,414879,414876,414873,414870,414867'
    
    DECLARE @sep NCHAR
    SET @sep = N';'
    
    DECLARE @SOURCE SYSNAME
    SET @SOURCE = 'enron_3'
    
    CREATE TABLE #temp 
    ([id] INT,  
    [sent] DATETIME,  
    [md5] NVARCHAR(45), 
    [node_id] INT, 
    [location] NVARCHAR(300), 
    [subject] NVARCHAR(MAX), 
    [sender] NVARCHAR(200), 
    [recipients] NVARCHAR(MAX), 
    [CC] NVARCHAR(MAX), 
    [BCC] NVARCHAR(MAX) 
    ) 
    
    CREATE NONCLUSTERED INDEX IX_temp_table
    ON #temp(md5, node_id)
    
    SELECT element into #E FROM dbo.SplitString(@ITEMS,',')
    CREATE CLUSTERED INDEX  IX_C_Element ON #E ( element )
    
    INSERT INTO #temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC 
    FROM [enron_3].[dbo].[Items] AS Items 
    RIGHT JOIN #E AS Split 
    ON Items.ItemID = Split.element;
        
    WITH QueryB AS (
    SELECT [md5], [sent], [location], [subject],
    rn = row_number() OVER (PARTITION BY md5 ORDER BY sent)
    FROM #temp
    )
    
    --fill hits with distinct items.
    INSERT INTO dbo.[hits]
    SELECT [md5], [sent], [location], [subject]
    FROM QueryB q
    WHERE [rn] = 1
        AND NOT EXISTS (SELECT * FROM dbo.[hits] h WHERE h.md5 = q.md5)

    --fill hits_nodes with every node that contains a specific md5
    INSERT INTO dbo.[hits_nodes]
    SELECT DISTINCT t.[md5], t.[node_id]
    FROM #temp t
    WHERE t.[md5] IS NOT NULL
        AND t.[node_id] IS NOT NULL
        AND NOT EXISTS (SELECT * FROM dbo.hits_nodes h WHERE h.md5 = t.md5 )
    

    -- Insert sender addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(t.[Sender])
        FROM #temp AS t
        WHERE NOT EXISTS (SELECT * FROM email_addresses WHERE email = dbo.Trim(t.[Sender]))
        GROUP BY dbo.Trim(t.sender)

    -- make sender relations
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.[md5], e.[id], 1
        FROM #temp AS t
        INNER JOIN email_addresses AS e
        ON t.sender = e.email
        WHERE NOT EXISTS (SELECT * FROM hits_addresses h WHERE h.email_address_id = e.id AND h.relation = 1)
        GROUP BY t.[md5], e.[id]

    --insert all distinct recipients into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN #temp t
        WHERE n.number < LEN(@sep + t.[recipients] + @sep)
        AND SUBSTRING(@sep + t.[recipients] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        AND NOT EXISTS (SELECT * FROM email_addresses WHERE email = dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1)))
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[recipients] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[recipients] + @sep, n.number+1) - n.number - 1))

    --insert all distinct CC into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN #temp t
        WHERE n.number < LEN(@sep + t.[CC] + @sep)
        AND SUBSTRING(@sep + t.[CC] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        AND NOT EXISTS (SELECT * FROM email_addresses WHERE email = dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1)))
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[CC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[CC] + @sep, n.number+1) - n.number - 1))

    --insert all distinct BCC into email_addresses
    INSERT INTO dbo.email_addresses (email)
        SELECT dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) AS [Value]
        FROM dbo.[numbers] n
        CROSS JOIN #temp t
        WHERE n.number < LEN(@sep + t.[BCC] + @sep)
        AND SUBSTRING(@sep + t.[BCC] + @sep, n.number, 1) = @sep
        AND NOT dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) = ''
        AND dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)) LIKE '%@%.%'
        AND NOT EXISTS (SELECT * FROM email_addresses WHERE email = dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1)))
        GROUP BY dbo.Trim(SUBSTRING(@sep + t.[BCC] + @sep, n.number+1, CHARINDEX(@sep, @sep + t.[BCC] + @sep, n.number+1) - n.number - 1))
    
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation) 
    SELECT X.MD5, E.ID, X.Relation FROM 
    (
        SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[recipients]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[recipients]+@sep,n.number+1)-n.number-1)) AS Email, 2 as Relation
        FROM dbo.numbers n 
        CROSS JOIN #temp t 
        WHERE n.number < LEN(@sep+t.[recipients]+@sep) 
        AND SUBSTRING(@sep+t.[recipients]+@sep,n.number,1) = @sep
        UNION ALL   
        SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[CC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[CC]+@sep,n.number+1)-n.number-1)) AS Email, 3 as Relation
        FROM dbo.numbers n 
        CROSS JOIN #temp t 
        WHERE n.number < LEN(@sep+t.[CC]+@sep) 
        AND SUBSTRING(@sep+t.[CC]+@sep,n.number,1) = @sep
        UNION ALL
        SELECT t.md5 as MD5, dbo.Trim(SUBSTRING(@sep+t.[BCC]+@sep,n.number+1,CHARINDEX(@sep,@sep+t.[BCC]+@sep,n.number+1)-n.number-1)) AS Email, 4 as Relation
        FROM dbo.numbers n 
        CROSS JOIN #temp t 
        WHERE n.number < LEN(@sep+t.[BCC]+@sep) 
        AND SUBSTRING(@sep+t.[BCC]+@sep,n.number,1) = @sep
       ) X INNER JOIN email_addresses e
        ON X.email = e.email 
        GROUP BY X.md5, e.id, X.Relation

    --inserts all custodians where message is found with relation to message into hits_addresses
    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation)
        SELECT t.md5, c.id, 5
        FROM #temp t
        INNER JOIN dbo.hits_nodes h
        ON t.md5 = h.md5
        INNER JOIN dbo.nodes n
        ON h.node_id = n.id
        INNER JOIN dbo.discoveryjobs d
        ON n.discoveryjob_id = d.id
        INNER JOIN dbo.custodians c
        ON d.custodian_id = c.id
    WHERE NOT EXISTS (SELECT * FROM hits_addresses WHERE md5 = t.md5 AND email_address_id = c.id)
    
    DROP TABLE #E
    DROP TABLE #temp

Index op email_addresses nonclustered op email
Index op hits_addresses nonclustered op relation
Index op hits_addresses nonclusterd op email_address_id, relation
Index op hits_nodes nonclustered op md5
Index op nodes nonclustered op discoveryjob_id
De meesten waren voorgesteld door de query optimizer.

Output: http://www.y3pp3r.nl/download/getItems_result.rpt
Plan: http://www.y3pp3r.nl/download/getItems_plan.sqlplan

Als ik met @temp werk gaat al de tijd zitten in het vullen van die tabel maar is de query verder wel sneller. En met een echte tabel of een #temptable maakt geen verschil.

[Fiander]

@temp is een memory tabel een soort variable waarop je alleen een geclusterde index kunt plaatsen.
#temp is een echte tabel ( weliswaar tijdelijk ) waarop je ook normale indexen kunt plaatsen.

code:

  INSERT INTO dbo.hits_addresses (md5, email_address_id, relation) 
        SELECT t.md5, c.id, 5 
        FROM #temp t 
        INNER JOIN dbo.hits_nodes h 
        ON t.md5 = h.md5 
        INNER JOIN dbo.nodes n 
        ON h.node_id = n.id 
        INNER JOIN dbo.discoveryjobs d 
        ON n.discoveryjob_id = d.id 
        INNER JOIN dbo.custodians c 
        ON d.custodian_id = c.id 
    WHERE NOT EXISTS (SELECT * FROM hits_addresses WHERE md5 = t.md5 AND email_address_id = c.id)

Je bent hier voor iedere regel in het join gedeelte aan het controleren ofdat deze regel reeds bestaat.
Je zou hier ipv de NOT EXISTS ook een left join kunnen doen op hits_addresses, en dan controleren of hits_adresses.id is null, uiteraard telt dit ook voor de insert op HITS en HITS_nodes
Je past je logica daarmee aan van row based naar set based. je controleerd in één keer wat je moet inserten.

zoiets dus.

code:

    INSERT INTO dbo.hits_addresses (md5, email_address_id, relation) 
        SELECT t.md5, c.id, 5 
        FROM #temp t 
        INNER JOIN dbo.hits_nodes h 
        ON t.md5 = h.md5 
        INNER JOIN dbo.nodes n 
        ON h.node_id = n.id 
        INNER JOIN dbo.discoveryjobs d 
        ON n.discoveryjob_id = d.id 
        INNER JOIN dbo.custodians c 
        ON d.custodian_id = c.id 
        LEFT JOIN hits_addresses HA ON HA.md5 = t.md5 AND HA.email_address_id = c.id
        WHERE HA.id is null

geen idee of ie in een keer goed is, heb hier geen SQLServer tot mijn bechikking, moet allemaal uit mijn blote hoofd


werkte het aan elkaar plakken van de recipiant, de CC en de BCC niet ?
je zou het geheel dan kunnen terug brengen tot één insert+joins ipv 3 achter elkaar.

verder heb je nu dit

code:

   CREATE NONCLUSTERED INDEX IX_temp_table 
    ON #temp(md5, node_id) 
     
    SELECT element into #E FROM dbo.SplitString(@ITEMS,',') 
    CREATE CLUSTERED INDEX  IX_C_Element ON #E ( element ) 
     
    INSERT INTO #temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC  
    FROM [enron_3].[dbo].[Items] AS Items  
    RIGHT JOIN #E AS Split  
    ON Items.ItemID = Split.element;

ik denk dat dit beter is.

code:

     SELECT element into #E FROM dbo.SplitString(@ITEMS,',') 
    CREATE CLUSTERED INDEX  IX_C_Element ON #E ( element ) 
     
    INSERT INTO #temp SELECT Items.[ItemID] AS id, Items.[EmailDate] AS sent, Items.[MD5Hash] as md5, Items.[ParentID] AS node_id, Items.[ItemFilePath] AS location, Items.[EmailSubject] AS subject, Items.[EmailSender] AS sender, Items.[EmailRecipients] AS recipients, Items.[EmailCCRecipients] AS CC, Items.[EmailBCCRecipients] AS BCC  
    FROM [enron_3].[dbo].[Items] AS Items  
    RIGHT JOIN #E AS Split  
    ON Items.ItemID = Split.element; 

  CREATE CLUSTERED INDEX IX_C_temp_table 
    ON #temp( id ) 

  CREATE NONCLUSTERED INDEX IX_temp_table 
    ON #temp(md5, node_id)

doordat de indexen NA het vullen van de tabel geplaatst worden, gaat het insert process sneller, omdat de index niet telkens bijgewerkt hoeft te worden.

door eerst een geclusterde index te plaatsen, zal er in de andere indexes op die tabel ipv een 16 byte grote filepointer er de key van de geclusterde index zitten, dat scheelt je bij deze opzet zoals je die nu hebt 12 byte per regel per index. tevens zullen alle lookups nu ook ietsjes sneller lopen.

verder denk ik dat de grootste klapper die je nog kunt maken toch echt in het samenvoegen van de recipiant, de CC en de BCC zit, en daar één grote insert van te maken.

[Y3PP3R]

Hello again,

Omdraaien van invoegen en index toevoegen heeft me 3 seconden gescheelt

Dat samenvoegen van de recipients + cc + bcc, dan krijg ik na 30 sec. de melding dat hij er mee stopt omdat hij geen dubbele waarden kan invoegen in email_addresses. (dat komt natuurlijk omdat hij daarvoor al de senders heeft ingevoegd, die kan ik er ook aan toevoegen, dan duurt de query nog 38 seconden). Als ik die queries los laat staan gaat 't sneller (21 seconden).

Ik snap zo snel niet wat je met die join doet ipv met NOT EXISTS. Ik heb geen veld id op hits_addresses, zou ik die moeten hebben? Ik ga het wel even proberen...

Thanks both Fiander & Pedorus

EDIT:
Die autoseed id column toevoegen aan hits_addresses en dan join ipv NOT EXISTS haalt echt niets uit. Even traag.

[ Voor 7% gewijzigd door Y3PP3R op 10-04-2010 15:27 ]

[pedorus]

Je zou nog eens kunnen kijken of het nieuwe MERGE sneller is dan insert if not exists. In principe is dat voor dit soort gevallen bedoelt. Verder misschien 3x distinct/group by ipv "GROUP BY X.md5, e.id, X.Relation" op regel 133 pas na de union all's, hoewel dat misschien niet boeit ivm sorteren voor index. Ik heb hier even geen Management Studio, dus ik kan niet kijken wat er nu nog traag gaat. Op zich lijkt me 21-3=18 sec al een flinke verbetering ten opzichte van meer dan een uur.

[Fiander]

met de join word in één keer bepaalt of iets uit een set reeds bestaat.
met de not exists moet er voor elke regel in de hoofd query gecontroleerd worden ofdat deze waarde reeds bestaat in de andere tabel.

je wilt 100 dingen inserten.
met de left join één keer opzoeken en klaar.
met de not exists 100 keer de sub query uitvoeren om te controleren.

[Y3PP3R]

zeker sneller! alleen dit grapje duurt nog lang als ik 500.000 records moet processen

Verder even een quote van iemand uit de posthistory van Pedorus:

Korben schreef op vrijdag 09 april 2010 @ 14:27:

[...]


Ten tweede, dat maakt niet uit. Of je nou jouw 'join'-variant, de TS zijn 'in'-variant, of de query die LINQ uitspuugt met een EXISTS draait, de execution plans zijn hetzelfde.

Dat zou verklaren waarom ik geen verschil merkte?

En hoe zorg ik bij MERGE dat er geen dubbele records uitkomen? Als ik dit uitvoer krijg ik de melding dat hij weer dubbele records wil invoegen en geen zin heeft in verdere uitvoer:

SQL:

MERGE
    email_addresses AS [target]
USING
    #temp AS [source]
ON
    ([target].email = [source].Sender)
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT(email)
        VALUES([source].Sender)

[ Voor 29% gewijzigd door Y3PP3R op 11-04-2010 13:39 ]

[Fiander]

en als je #temp eens distinct vult ?