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)
Columns: Package_UUID nvarchar(255), MessageExtractState tinyint, [count] int (350m rows)
ServerB (main database server)
Columns: Package_UUID nvarchar(255), Package_id bigint (650m rows)
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
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.