Union very slow

• abdalah.mehdoini

  Ten Centuries

  Points: 1015

  More actions

  September 17, 2022 at 1:35 pm

  #4088840

  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

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  September 17, 2022 at 1:44 pm

  #4088841

  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.

• September 18, 2022 at 7:12 am

  This was removed by the editor as SPAM

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  September 18, 2022 at 12:54 pm

  #4089054

  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;