MSSQL to MYSQL Table Copy over HTTP

[pechar]

Hi all,

I created an application to copy certain tables from a database on MSSQL server to another database on MYSQL server which is on another server (very very very far away). The process works well but it terribly slow. And were only talking text... no images are in the database yet.

I used ADO.Net and .NET ODBC Connection to MYSQL. What I do is select the data from MSSQL tables to a dataset using a source DataAdapter and and then (with the help of command builder) I update the changes using a destination DataAdapter.
I use the CommandBuilder since i don't want create my own update statements (too much of a hassle)

This process needs to run once a day, but still takes ages.

Any ideas on an alternative method?? I've tried all sorts of other ways like Data Transformation Services but this still takes long and causes errors for blob data.

Thanks in advance and I apologise for the long thread but wanted to make things clear.

Lukas

[kenobewan]

This method is always going to be slow, have you tried ado.net. You can use a remote connection string and copy the data that way. HTH.

[pechar]

This method is always going to be slow, have you tried ado.net. You can use a remote connection string and copy the data that way. HTH.

Yes I am using ADO.NET as I said earlier in thread and am using a remote connection string. Or do you mean something else?

[kenobewan]

My bad, there are three things most likely to influence this process. If your sql query and sql server are not optimized, which you can do something about. The speed of your connection which you cannot.

I believe that you would be better off using a datatable or xml and writing your own sql. Anything remote need to be efficient, more efficient equals faster. However, one of the pitfalls of remoting outside a lan (very far away) is how long the connection has to remain open. In such a case you may be better using a web service. Hope this one is more helpful.

[pechar]

My bad, there are three things most likely to influence this process. If your sql query and sql server are not optimized, which you can do something about. The speed of your connection which you cannot.

I believe that you would be better off using a datatable or xml and writing your own sql. Anything remote need to be efficient, more efficient equals faster. However, one of the pitfalls of remoting outside a lan (very far away) is how long the connection has to remain open. In such a case you may be better using a web service. Hope this one is more helpful.

Kenobewan thanks for your reply, sorry havent replied but didn't have access to internet this weekend. Could you please elaborate on usage of DataTable , my own sql and/or XML? By how long the connection has to remain open you mean timeouts? Thanks again

[kenobewan]

By using a datatable/xml and your own query, I am hoping that you may improve performance over update with dataadapter. The proof would be in performance testing.

While connecting to a db and executing, the connection needs to remain open. This is not a problem in a web farm or over a lan, but becomes a major issue if the server is hosted elsewhere. Unless you can change your server design, then make sure you are only opening to execute and close again.

May even be better to send datatable/xml file through webservice or something like this:
How To Update Server Data Through a Web Service by Using ADO.NET and Visual C# .NET

[pechar]

Hi all,

This post is similar to my previous post. I need to get data from a remote FoxPro Database to be displayed in a web application. Would you suggest using a web service on the database server to retrieve this data? Amounts of data are rather large and I'm worried about the speed of the webservice.

Or would it be better to copy all the tables to the web application server once a day and use that data. (this would take some time)

Real time data is an issue here so I don't think the second choice is feasible.
Any ideas?

Thanks
Lukas

[kenobewan]

So similar I merged it, you don't need to create separate threads on the same topic. Replying will bump them up the queue. Thanks.

[pechar]

So similar I merged it, you don't need to create separate threads on the same topic. Replying will bump them up the queue. Thanks.

Sorry my bad next time I keep it in the same thread.
Also I'll give it a try as you mentioned.
Thanks again