1

I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them. Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause.

Is there any way we can speed this up or use a different approach?

Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT 
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
         WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
         WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
         WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
         WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
         ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
        END AS col51 
  from source_server.table')

After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it?

SELECT 
           ISNULL(A.Col1,B.Col1) Col1
          ,ISNULL(A.Col2,B.Col2) Col2
          ,ISNULL(A.Col3,B.Col3) Col3
       ...etc
         ,ISNULL(A.Col50,B.Col50) Col50 
         ,ISNULL(A.Col51,B.Col51) Col51 
  FROM Table1 A -- linked server table
  FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table
  WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows      
       OR  (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes 
       OR  (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 <> A.Col50) --updates
0

1 Answer 1

0

I can only assume the destination table is truncated before inserting 170+ million rows and this is done at some defined interval. I would add the 2 calculated columns to the source table and define a trigger on update and insert to keep them current. In the query to the linked server, OPENQUERY already executes it on the remote server. The problems likely lie in the calculations done on each row in the OPENQUERY statement, and the index(es) on the destination being updated on each insert. Add 2 additional columns to the destination table to store the calculated columns from the source table. Now you are storing both the original col50 & col51 columns from the source, and the calculated col50 & col51 columns from the source. Name them appropriately. Update the SELECT..INTO statement to simply select the columns you want, and you want all of them. Also, disable indexes on the destination table before the SELECT..INTO and enable afterward. You may need to use SET IDENTITY INSERT depending on the destination table definition. Then, in the follow-up query, you don't need to join to the remote table because you have all the necessary columns in the local table to perform it already. Consider also using COALESCE instead of ISNULL. Best of luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.