What is the best way to periodically update a MySQL table from a SQL Server table?-CodePudding

What is the best method for inserting and updating a MySQL table from a SQL Server table/view?

The solution that I've built involves using an SSIS package that performs 3 tasks.

Execute SQL Task - This task enables ANSI_QUOTES for the MySQL session

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';

Data Flow Task - Uses an OLE DB Source (a SQL Server view) and an ADO NET Destination (the MySQL table). The yellow bang on the ADO NET Destination is just notifying about potential truncation when inserting data.

Sequence Container with Execute SQL Task -

The SQL statement follows the structure below:

UPDATE MySQLTable
SET
    KeyField = src.[KeyField],
    Field1 = src.[Field1],
    Field2 = src.[Field2],
    ...,
    Field85 = src.[Field85]
FROM OPENQUERY([MySQL Linked Server], 'SELECT * FROM MySQLDatabase.MySQLTable') AS MySQLTable
INNER JOIN SQLServerTable AS src ON MySQLTable.[KeyField] = src.[KeyField]

The issue I'm having is that the updates are very slow. Using MySQL Workbench and viewing the performance dashboard I only get 3-5 updates per second. I need help in identifying the reason(s) for slow updates and/or the best way to update a MySQL table from a SQL Server table.

CodePudding user response：

If the number of data that you have in your source is not huge you can insert the data in a staging table ( the same structure as the source but in the destination database or at least the destination server ). You have to replace the update by a simple update query in your destination server. The performance would be far beter.

If the amount of data is too big you can filter the data with business rules like modification date, ...