Improve slow DMS table validation query

0

DMS is using this query to perform table validation post full load migration (in this case on a MySQL source) :

SELECT tableID FROM ( SELECT @row:=@row+1 AS rownum, tableID FROM (SELECT @row:=0) v, ( SELECT tableID FROM mySchema.bigTable ORDER BY tableID ASC ) AS sorted ) as ranked WHERE rownum % [PartitionSize] = [PartitionNumber]

It is quite slow on a table with over 10 million rows (20 seconds for each 10000 partition pass) or painfully slow on a table with over 50 million rows (around 7 minutes for each 10000 partition pass).

Why not use this much faster query until no rows are returned?

MySQL:

SELECT tableID FROM mySchema.bigTable ORDER BY tableID ASC LIMIT [PartitionNumber]*[PartitionSize],[PartitionSize]

Oracle / PostgreSQL:

SELECT tableID FROM mySchema.bigTable ORDER BY tableID ASC LIMIT [PartitionSize] OFFSET [PartitionNumber]*[PartitionSize]

SQL Server:

SELECT tableID FROM mySchema.bigTable ORDER BY tableID ASC OFFSET [PartitionNumber]*[PartitionSize] ROWS FETCH FIRST [PartitionSize] ROWS ONLY

profile picture
asked a year ago87 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions