I am attempting to sync a MS-SQL database with a MySQL database using the SQLyog Import External Data Wizard. I've created a view in MS-SQL, and am doing a “SELECT * ” query on that view in SQLyog. The problem seems to be caused by a LEFT OUTER JOIN in the view query, when I remove this the query runs with no problem.
When I add the LEFT OUTER JOIN, the query runs in under 1 second in MS-SQL Management Studio on the same PC. I've also run the same query in Excel, using the same ODBC connection, and it completes immediately. When I run the job in SQLyog, it takes 27 minutes to complete the sync.
Any tips? Is there a problem with the way SQLyog handles the LEFT OUTER JOIN?
Code:
SELECT TOP (100) PERCENT i.ITEM_ID, i.ORDERNO, i.ITEM,
i.QUANTO, i.QUANTS, i.QUANTB, s.DESC1 + ' ' + s.DESC2 AS description,
i.DROPSHIP, i.ITEM_STATE, pi.EXPECT_ON
FROM MailOrderManager.dbo.ITEMS AS i INNER JOIN
MailOrderManager.dbo.STOCK AS s ON i.ITEM = s.NUMBER LEFT OUTER JOIN
MailOrderManager.dbo.PURITEM AS pi ON i.ITEM_ID = pi.ITEM_ID
WHERE (i.ORDERNO > 200000)
SQLyog does not 'know' the query used for populating the VIEW. Bascially SQLyog will just SELECT * … for every table (or VIEW). Even if you use the option to 'specify a query' in SQLyog the query is sent to to theserver (ie. SQL server) who returns a result set. SQLyog creates a table on (ie MySQL) and populates this table with the result set.
So this is strange – also as Excel loads the VIEW very fast it does not seem to be an problem with ODBC either.
Please tell:
1) Are the machines on the same network or are you importing over the Internet?
2) How big is that VIEW? (#of rows, the data length for the table in MySQL after import as returned by SHOW TABLE STATUS)
3) Can you try creating a table on source with the same data and next import just to see if it makes any difference (not sure about SQL Server syntax but there must be something like CREATE TABLE … AS SELECT …)
If it is not a networking issue or a MySQL configuratiobn issue I am afraid we will need a complete test case with data to comment further (access to the database or a copy). Can you provide that? And please create a support ticket for privacy when exchanging such details.
1) Are the machines on the same network or are you importing over the Internet?
The MS-SQL source database is on the same network, the MySQL destination is over the internet. However, I can run other similar sized exports over this connection without any issues.
'peterlaursen' wrote on '27:
2) How big is that VIEW? (#of rows, the data length for the table in MySQL after import as returned by SHOW TABLE STATUS)
Rows 33060, Data Length 5429504, MyISAM
'peterlaursen' wrote on '27:
3) Can you try creating a table on source with the same data and next import just to see if it makes any difference (not sure about SQL Server syntax but there must be something like CREATE TABLE … AS SELECT …)
After creating a table from the view, the import completes in 40 seconds with compression on (I've tried with it on and off).
'peterlaursen' wrote on '27:
If it is not a networking issue or a MySQL configuratiobn issue I am afraid we will need a complete test case with data to comment further (access to the database or a copy). Can you provide that? And please create a support ticket for privacy when exchanging such details.
I may be able to provide this, but would have to sanitize the data.
noblenull
Memberpeterlaursen
ParticipantKhushboo
Member
