Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. Since the tables are so large I'd like to process the update in batches, both so I can gauge progress and to avoid creating huge temp tables. Each column is indexed.

Would this be a good example where a CTE comes into play? Quite honestly they confuse me with the way they need to be written, it's hard to visualize...

The tables are structured as:

ServerA (utility SQL server)
Table: CLIP_IDs
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)

ServerB (main database server)
Table: PACKAGE
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
Table: DOC2
Columns: External_Identification nvarchar(255), Package_id bigint (2b rows)

Both SQL servers are linked both ways, if initiating the query from one is more efficient. I have a feeling issuing the query from ServerA will be, as it seems like the execution plan offers less remote queries.

I stopped the query below after 26 hours because I think I have a syntax logic error. Can someone explain what it is and offer any suggestions please?

Executed from ServerA:

DECLARE @rowsUpdated INT

SET @rowsUpdated = 1

WHILE (@rowsUpdated > 0)
BEGIN
    UPDATE CLIP_IDs
    SET [Count] = x.[count]
    FROM (
        SELECT TOP 50000 c.package_uuid
            ,count(d.external_identification) AS [count]
        FROM CLIP_IDs c
        INNER JOIN ServerB.DATABASE.dbo.package p(NOLOCK) ON c.package_uuid = p.package_uuid
        INNER JOIN ServerB.DATABASE.dbo.doc2 d(NOLOCK) ON p.package_id = d.package_id
        WHERE c.messageextractstate = 1
            AND c.[count] IS NULL
        GROUP BY c.package_uuid
        ) x

    SET @rowsUpdated = @@rowcount

    PRINT N'Finished set of rows: ' + convert(VARCHAR, getdate(), 120)
END
share|improve this question
    
I stopped the query below after 26 hours because I think I have a syntax error. This is highly unlikely as SQL Server will parse the query first to check for syntax validation. + why are you doing cross server updates using Linked server on such huge tables ? That will be dog slow no matter what you do. Instead have an ETL job (SSIS) that will load the data locally to a staging table on the destination database (where you want to update the records). And then do the updates, it will be much quicker ! with SSIS you can even leverage on doing incremental loads as well. –  Kin Sep 19 '13 at 22:44
    
Also use of NOLOCK is dangerous while doing updates. Refer to (blogs.msdn.com/b/davidlean/archive/2009/04/06/…) and (stackoverflow.com/questions/1452996/…) and (jasonstrate.com/2012/06/the-side-effect-of-nolock). –  Kin Sep 19 '13 at 22:47
    
Hi Kin, thanks for your replies. The data being selected is old data and once written is never updated. Maybe that negates the need for the nolock hint, but the DBA team who manages the ServerB always insists that we use nolock on heavily accessed production servers.... So I do. –  beeks Sep 20 '13 at 0:16
    
Regarding using a linked server, I don't have enough disk space to pull the required data from ServerB to join locally on ServerA. Wish I did! I use DTS frequently to move data cross server, but will look into SSIS. Do you see any reason why my query though would NOT work, even if all 3 tables were on a single server? –  beeks Sep 20 '13 at 0:23
1  
DTS is outdated .. SQL Server 2005 introduced SSIS which is more robust than the old flaky DTS. Also, nolock will give you dirty reads and the links that I referenced explains them very well. Your DBA's should also refer to those as its a bad practice to use it in PROD. –  Kin Sep 20 '13 at 1:57
add comment

1 Answer

up vote 2 down vote accepted

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT 
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM 
    ServerB.DATABASE.dbo.package p
    INNER JOIN 
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY 
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT 
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM 
        DATABASE.dbo.package p
        INNER JOIN 
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY 
        p.package_uuid
    ) AS OQ;
share|improve this answer
    
Billinkc, thank you for your very helpful comment. I have to do some reading on OPENQUERY() as that's new to me. The file structure on ServerB is frustratingly complex (36 mounted volumes peppered inside 8 logical drives) but I think we recently allocated a terabyte to tempdb on its own volume. Space is much more limited on ServerA though. Perhaps if I order both the global temp table and the destination table on Package_UUID ascending, I could do a merge update? Another topic to read up on. Servers are connected via token ring. I mean, both servers are in the same datacenter.:) –  beeks Sep 20 '13 at 2:17
    
"Token Ring" - jeebus it's been a long time since I've seen that tech! –  Max Vernon Sep 20 '13 at 2:23
    
Just kidding on the token ring ;) Just 1gig Ethernet. –  beeks Sep 20 '13 at 2:25
    
@Billinkc, thanks. After not being able to access a global temp table from a linked server, I went rogue and created one on ServerB to be quickly snapped up and dropped. The DBA's would be pissed, but it was worth it to see disk reads spike over 1gigabyte/sec ;) –  beeks Sep 20 '13 at 3:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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