Import Data and Data transfer Slow Compared to MS SQL

[ETtektips]

MySql 3.23.54 Running on a web shared host
I have a regular requirement to upload/ export data to a MySql server. Tests are appends/ insert not updates.
Whilst I have not been able to get "LOAD DATA LOCAL INFILE..." working yet (and may not due to host issue) - my other tests have shown the following.

Test import data has 5 text fields with 10 chars in each, 27,000 records. Table already exists in the database. Tests performed on a local MySQL and MS SQL - plus remote MySql and MS SQL

1. using Premiumsoft Navicat as the gui client application for the import. Source in MS Access table format
1.1 Local connection 9 mins
1.2 Remote connection 48 mins

2. same table created in MS SQL and the same Access database used as the source. Using DTS Import in MS Enterprise Manager.
2.1 Local connection 20 seconds
2.2 Remote connection 2 minutes

3. Tried MySql with 'SQLyog' as the client interface but I gave up after about 90 minutes on the remote test.

4. Have tried using Odbc but this is very, very slow.

5. Tried Import to the Local MySql (9 minutes) and then a data transfer from the Local server to the remote for this table only - but that took 44 minutes i.e. 9+44mins to achieve the same as an import (48 mins)

I must be missing something - can't believe that Microshaft have got one over on MySql.

Thanks in advance for any thoughts.
Antony

p.s. 'Local' is actually a box on the local network. The client applications are running on a decent Win 2000 worksattion.

 

[sleipnir214]

A quick pointer about LOAD DATA....

The "LOCAL" keyword of "LOAD DATA LOCAL INFILE..." instructs MySQL to transmit the datafile through the connection. This functionality is turned off by default.

If you can place the datafile on the same filesystem as the MySQL server, then just use "LOAD DATA INFILE..." and point to the file on the filesystem.


Also, you're comparing apples to oranges here. Testing a local system against a foreign system will always slant your test results in favor of the local system.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ETtektips]

Thanks you for your post sleipnir214. I'm trying to get the host to configure to support "LOAD DATA LOCAL INFILE..." and also figure out how to how to run the "LOAD DATA INFILE..." (I am new to this and have just been using phpMyAdmin 2.3.0-rc4 and Navicat for access to the db)

Please note I am not comparing Local and Remote, MySql and MS SQL which are being compared like for like i.e. separate tests for local and remote servers.

 

[sleipnir214]

When you are testing local and remote MSSQL database acces, is that foreign server on the same network as your foreign MySQL server? Otherwise, the network latency will be more of a factor than anything -- it could simply be that your HSP's network is slow.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[ETtektips]

Thank you for your comments. The 'local' server is actually running both MySql (windows version) and MS SQL. The 'remote' servers are two separate boxes one running Windows/ MS SQL and the other std MySql on (Linux I think) - both remote servers are similar specs.