Update online database 1

[SuperComputing]

I have a local server that contains our inventory that changes constantly. Our online store is hosted elsewhere and uses a mysql database for storing the inventory. I would like to update the online database every hour.

I have a local SQL script that pulls the local updated inventory data (2100 rows) in under a second.

If I log in to the PHP admin and import the data from csv, it takes about a second.

If I try to make an asp page pull the local data and write it to the online database, whether updating existing records or inserting new rows into a blank database, it can take up to 30 mins.

Any ideas?

Here's some of my code (for when I have a blank database):

Set RemoteConn = Server.CreateObject("ADODB.Connection")
RemoteConnStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
RemoteConn.Open RemoteConnStr
Set LocalConn = Server.CreateObject("ADODB.Connection")
LocalConn.open = "DSN=CPDATA;"

LocalQry = "SELECT Items.ItemNumber, Items.Description1, Items.Price, Inventory.QtyOnHand AS Qty etc...
FROM Items 
LEFT JOIN Inventory ON Items.ItemNumber = Inventory.ItemNumber 
WHERE ... 
Set LocalRS = LocalConn.Execute(LocalQry)

do until LocalRS.EOF 
ItemNumber = LocalRS("ItemNumber")
Description1 = LocalRS("Description1")
etc...

AddItem = "INSERT INTO " & tablename & " VALUES ( '"&Replace(ItemNumber,"'","''")&"','"&Replace(Description1,"'","''")&"',etc...
RemoteConn.Execute(AddItem)

LocalRS.MoveNext
loop

 

[foxbox]

i'm doing MS-SQL and Oracle, and in a case like this i would NOT use an ASP script to update the database. I would go for the direct approach: call a stored procedure in the database.

I believe MySQL has a LOAD DATA command:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html