Union very slow

  • Hello ,

    Can you help me to optimize this request

    in fact the union is very slow and I have more help how I can replace it

    ----->DOCUMENT--(3250954 40minutes)
    declare @sumdocument int ;
    SET IDENTITY_INSERT DOCUMENT on
    Insert
    into link.dbo.DOCUMENT
    (SEQ_DOC
    ,NUM_DOC_LAST
    ,COD_UTI
    ,COD_TDOC
    ,COD_STO
    ,COD_APP
    ,ID_DOC
    ,NUM_REV_DOC
    ,NAME_DOC
    ,FILENAME_DOC
    ,DAT_DOC
    ,SIZE_DOC
    ,EXT_DOC
    ,AUTHOR_DOC
    ,EMAIL_DOC
    ,REF_DOC
    ,TXT_DOC
    ,IND_PRV_DOC
    ,IND_DEL_DOC
    ,PATH_DOC
    ,NOM_DEPOT
    ,IND_ARC_DOC
    ,DAT_INT
    ,FUNCTIONAL_ID
    ,FUNCTIONAL_DATE
    ,StatutArchiveDocumentId
    )
    select distinct d.* FROM Link.dbo.EVT_ATT E
    JOIN [pprod.link.db.bollore-logistics.com].LINK.dbo.DOCUMENT d
    ON E.SEQ_DOC = d.SEQ_DOC
    UNION
    SELECT Distinct d.*
    FROM Link.dbo.DOS_ATT E
    JOIN [pprod.link.db.BAD-logistics.com].LINK.dbo.DOCUMENT d
    ON E.SEQ_DOC = d.SEQ_DOC
    UNION
    SELECT Distinct d.*
    FROM Link.dbo.DT_ATT a
    JOIN [pprod.link.db.bad-logistics.com].LINK.dbo.DOCUMENT d
    ON a.SEQ_DOC = d.SEQ_DOC
    UNION
    SELECT Distinct d.*
    FROM Link.dbo.CAR_ATT CT
    JOIN [pprod.link.db.bad-logistics.com].LINK.dbo.DOCUMENT d
    ON CT.SEQ_DOC = d.SEQ_DOC
    SET IDENTITY_INSERT DOCUMENT off
    select @sumDOCUMENT = count(*) from Link.[dbo].[DOCUMENT]
    print 'DOCUMENT' +'--->' + cast( @sumDOCUMENT as varchar(30));

    thanks for your help

  • multiple issues there.

    Union on its own needs to sort the data to remove duplicates - adding to this you have identity_insert on which has its own issues.

    and (probably the worst) your 4 union queries are retrieving data from the same linked server table - depending on how SQL decides to do it may be retrieving the full contents of the dbo.document table to a local temp table (4 times) and then joining to the other 4 tables.

    based on the above and on the fact that you are only using the contents of the dbo.document and none of the local tables I advise you to first create a table with the distinct values of SEQ_DOC from the 4 local tables and then using this one to join to the remote server.

    Even on this case here and depending on the size of the remote table it may be better to retrieve its contents explicitly to a local table and then join to the list of SEQ_No's. Potentially and if feasible only retrieve the rows where SEQ_No is between the local min and max values.

  • This was removed by the editor as SPAM

  • DECLARE @sumdocument int;

    WITH Bollore
    AS
    (
    SELECT SEQ_DOC
    FROM OPENQUERY([pprod.link.db.bollore-logistics.com], 'SELECT SEQ_DOC FROM LINK.dbo.DOCUMENT')
    )
    , Bad
    AS
    (
    SELECT SEQ_DOC
    FROM OPENQUERY([pprod.link.db.bad-logistics.com], 'SELECT SEQ_DOC FROM LINK.dbo.DOCUMENT')
    )
    , AllDocs
    AS
    (
    SELECT SEQ_DOC
    FROM Bollore D
    WHERE EXISTS
    (
    SELECT 1
    FROM Link.dbo.EVT_ATT L
    WHERE L.SEQ_DOC = D.SEQ_DOC
    )

    -- maybe just union
    UNION ALL

    SELECT SEQ_DOC
    FROM Bad D
    WHERE EXISTS
    (
    SELECT 1
    FROM Link.dbo.DOS_ATT L
    WHERE L.SEQ_DOC = D.SEQ_DOC
    )
    OR EXISTS
    (
    SELECT 1
    FROM Link.dbo.DT_ATT L
    WHERE L.SEQ_DOC = D.SEQ_DOC
    )
    OR EXISTS
    (
    SELECT 1
    FROM Link.dbo.CAR_ATT L
    WHERE L.SEQ_DOC = D.SEQ_DOC
    )
    )
    SELECT @sumDOCUMENT = COUNT(*)
    FROM AllDocs;

    RAISERROR('DOCUMENT--->%i', 0, 1, @sumdocument) WITH NOWAIT;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply