Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Issue with INSERT-SELECT with linked server

    Hello,

    I have a historical table on a dedicated SQL Server (let's call it the reporting db) that is populated every morning with production data that does not already exist. The data in the prod table is purged after 7 days and nothing is ever deleted from the historical table. I have set up the linked server between the two 2008 SQL Servers, but when I try to run this simple query from the reporting DB, it takes more than 5 minutes and still "executing". I eventually have to cancel it:

    -- INSERT INTO Temp_Import_historical
    SELECT TOP 1 *
    FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
    WHERE NOT EXISTS (select [Temp_Import_ID] from Temp_Import_historical where a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])

    I have omitted the INSERT statement on purpose, since I can't even get to output 1 row.
    Can someone tell me why this is such a resource intensive query?

    TIA

  2. #2
    Join Date
    Aug 2012
    Posts
    30
    looks like issue with linked server link. is network stable? is there any other parallel processes accessing same source table? how big is the source table?

  3. #3
    Join Date
    Mar 2003
    Posts
    97
    Yes, the network is stable. There are 11,545,851 records in the historical table and 325,090 records in the production table, as of now. I thought about creating an index on the [Temp_Import_ID] column on the historical table, but I'm afraid that will make writes that much slower. Any other thoughts?

    TIA

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    is it partitioned?

  5. #5
    Join Date
    Mar 2003
    Posts
    97
    No. Will that help with this insert? If so, should I create an index on [Temp_Import_ID] and partition the index or should I just partition the column?

  6. #6
    Join Date
    Aug 2012
    Posts
    30
    it might be better if you could partition history table based on date and create an index on it. i hope you will always be interested in latest partition

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to humor me, try:
    Code:
    SELECT *
       INTO #foo
       FROM [192.168.1.100].ProdDB.dbo.Temp_Import_historical a
    
    -- INSERT INTO Temp_Import_historical
       SELECT TOP 1 *
          FROM #foo AS a
          WHERE NOT EXISTS (select [Temp_Import_ID]
             FROM Temp_Import_historical
             WHERE  a.[Temp_Import_ID] = Temp_Import_historical.[Temp_Import_ID])
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2003
    Posts
    97
    PatP,

    I ran your code and it took 1 minute and 13 seconds to insert 325,090 rows into #foo. I guess I could add all of them and do a delete based on some set of unique fields, or were you thinking of something else?

    TIA

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just remove the comment???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2003
    Posts
    97
    Sorry for the delay. I've gone ahead and implemented the select-into which writes the data to a staging table on the source db, and I then run the insert-select with the "not exists" to bring in the new records. It works like a charm. Thanks a lot guys!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •