Feature Wishes And Possible Bug In "migration Toolkit"

[Smeagle]

Hi,

Just tried out the SQL Migration Toolkit…

Some wishes:

– I would like so set options to handle different datatypes for conversion.

F.e. I would like to import all “booleans” from MSSQL to “tinyint” (as bit is very slow on mysql) and import varchar(>255) as “text” (as I need to import the data into a old version of mysql)

– I would like to set option for database-type-default (MyISAM od InnoD:cool: globally

– I would like to be able to switch “import indexes” globally. (see below)

I Import >200 tables and it's annoying to have to go through every table and set this…

possible bug with import index:

– On the table under “advanced” there is a “import index” switch, which is enabled but ghosted. Why? I would like to disable it but couldn't.

– On first run, SQLYog tried to create index but failed (index not supported by mySQL). So I checked in “map/edit SQL” and in that statement there was no instruction for index. After clicking “OK” and retrying the import, SQLyog no longer tried to build the index, which is what I wanted. But it's somewhat inconsistent, it seems SQLYog builds the index but doesn't include the code for this in the “edit sql”-dialog? And after “editing” the SQL-Code no longer builds the index?

Thanks 😉

– Oliver

[Smeagle]

Hi,

another feature request:

Please make it as fast, as “ODBC Import” was in Version 3.71… The Import wich took ~1 1/2 Hours in Version 3.71 and “ODBC Import” is now Running in 5.29 and “Migration Toolkit” for 7 hours. And still running. He is now on Row 1.000.000 of 28.000.000 of one large table. 🙁 This will take a lot more hours i guess. Are there any ways to speed this up?

Its the exact same configuration I used before with 3.71:

MSSQL 2005 on a Server with 4 CPUS, SQLyog is running on this server. MySQL 5.0.27 is running on anther server, this one with only 1 CPU. So Migration Toolkit copies from local MSSQL server to remote MySql server.

– Oliver

[Rohit]

@Smeagle:

We have had reports in the past where users have complained about ODBC import speed.

Most of the time it turns out that the user has been importing into InnoDB tables as compared to MyISAM tables. Innodb inserts can be 100x slower compared to MyISAM if innodb_flush_log_at_trx_commit is not configured properly.

The latest migration toolkit is supposed to be several times faster than the one shipped with 3.71.

[Smeagle]

Hi,

I am importing into MyISAM Tables. On Table took about 4 hours alone. Table is defined like this:

CREATE TABLE `test` (

`typnr` int(10) NOT NULL,

`genartnr` int(10) NOT NULL,

`lkz` char(3) NOT NULL,

`nkw` tinyint(1) NOT NULL,

) ENGINE=MyISAM DEFAULT CHARSET=latin1

so nothing special here. But it is quite large:

Rows Avg_row_length Data_length

29018323 13 377238199

I do this import job every 3 Months, last cycle with 3.71 took 1,5hours and this cycle, first time with the 5.29 Version, 9 hours.

One part of the problem is, that now indexes are created on the copied database. As I understand, INSERTing Data on a table with indexes should be slower? BUT: The table above had no indexes (I removed them before import to be sure) and it was very slow.

Have you read my first post? How can I disable copying indexes? Is see the setting “import indexes” but cannot change it as it is greyed oput. I need completely different indexes on my copy of the database than on the original database, so i have to remove them anyways after importing…

Thanks,

– Oliver

[adarsh]

Hi,

To uncheck the import index option from adcvance properties first you should disable the import foreign keys option and then you can disable the import indexes optiion.

[peterlaursen]

I do not think the slow speed is related til MySQl or SQLyog.

Probably it is the ODBC driver.  What is the ODBC source?

If you import into any other program that can connect with ODBC (Access, OpenOffice base) is it then significantly faster?

[Smeagle]

I used 3.71 on the same machine and the same configuration. That was working much faster. I use the ODBC-Drivers that came with MSSQL 2005. I could try reinstalling 3.71 on that machine, but I would prefer to do everything in one version. I looked at the ODBC-Connection and noticed, it uses TCP/IP. I think named pipes are faster (but I usesed TCP/IP with 3.71, too)? I'll test it with named pipes and turned off index-import and get back to you.

Thanks for the info for “import index” option. I would still like to set “defaults” for all the options so I don't have to go through all tables.

– Oliver

[Author]

Posts