data pump into MySQL

Question

data pump into MySQL

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

I was trying to find if MySQL themselves have documentation somewhere explaining the performance difference, but I couldn't find any.

The only explanation I have is what I have mentioned earlier. The ODBC bulk-load doesn't involve the MySQL statement parser on each record inserted. This is your single biggest performance bottleneck and every database like Oracle, DB2, SQL Server have the same bottleneck. Usually the bulk-load API doesn't involve the parser and uses the specific database low-level API to do the insert. As confirmation of this we see similar performance gains when using the bulk-load APIs for other databases:

- COZYROC Oracle Destination - 10x-30x faster

- COZYROC DB2 Destination - 20x faster

- COZYROC Informix Destination - 6x-8x faster

- COZYROC Teradata Destination - 80x faster (very dramatic speed improvement).

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

@cmoy,

You posted in a SSIS 2005 forum, but you said you were taking data from a 2008 database. Did you post in the wrong forum or are you really on SSIS 2005? Is using SSIS 2008 an option? If SSIS 2008 is an option look into the ADO NET Destination with the MySQL .NET driver to see if performance is acceptable for you. If SSIS 2012 is an option, now included in SSIS is a built-in ODBC Destination component that supports bulk load.

@COZYROC,

I plan on testing your components against the other methods. I am in the process of setting up a testbed for the Cherry City driver to see if I can send data from SQL Server to MySQL using the built-in OLE DB Destination in SSIS 2005. If I can succeed in setting that up I'll see 1) is it a viable setup 2) how does it perform.

Another option that occurred to me was setting up a Linked Server within SQL Server to connect directly to a MySQL instance using the MyODBC driver[/url]. I do not typically support the idea of using Linked Servers, as I am not a fan of database-server-to-database-server communication, however I have resorted to using Linked Servers to push data from SQL Server to MySQL in the past and I was able to achieve satisfactory throughput. In a scenario where SSIS 2005 were the only programming option to push data to MySQL I might make an exception given the limited set of options in the space and move away from SSIS towards implementing a Linked Server. Mileage may vary.

I'll let you know how my testing goes. Thank you for this exchange, and the others we have been carrying on. It has been very useful to me, and hopefully to others evaluating your products.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

I'll let you know how my testing goes. Thank you for this exchange, and the others we have been carrying on. It has been very useful to me, and hopefully to others evaluating your products.

You are welcome! You have raised valid questions and you are quite right. I also believe it will be useful to other people.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

I had a little time and got the Cherry City MySQL OLE DB driver (v 5.60.82) working in BIDS 2005, i.e. I can load data from a flat file into a MySQL 5.5.25 table. I would say it is only viable if you have a small amount of data or infinite time to spare as it is dreadfully slow (~15 rows per second loading a simple table with 3 int-columns). It has been temperamental on my Windows 7 machine as well. If I use the "Table or view" option in the OLE DB Destination and attempt to use the drop-down to select a table it crashes BIDS. It crashes BIDS in several of the different scenarios I tried. The only way I could get it to work was to use the "SQL Command" option with a "select * from dbName.myTableName" in the command window. After all that trouble I found that the driver costs ~$150 US for an unlimited time license, or you can buy a 1 year license for $75. For me, it is no option at all. If working in a 2005 environment I would have explore using a Script Component, or maybe a Linked Server, before ever working with the Cherry City driver.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato