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
8  2713 
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.
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?
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.
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
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
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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Alper Adatoz |
last post by:
Hi,
i have a little problem. i hope u guys give me a clear solution (:
db: mssql
i just want to put jpeg file to the image field at the mssql db.
and after that i want to call it back..
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
|
by: Andre |
last post by:
Hello
Can anyone help me translate this from access so that it can work in mssql
(i need to get next value, but cannot use identity as if row is deleted,
another must get new next column number which would be same as deleted one)
Access;
INSERT INTO table
SELECT
(IIF(code<>Null,MAX(code)+1,1) AS code,
0 AS usercode
FROM table
|
by: Kenneth P |
last post by:
Hi,
I'm trying to do some Custom Paging technique with the datagrid object and
with the select command in Sql, thus forcing the server to only select those
rows from the database that should be rendered in the datagrid object.
This works fine with the code I have in MsSql2k but now I'm trying to do the
same with MySql v4.1 database and it doesn't support the 'TOP'
command/function/clause and I'm looking for some equivalence in MySql...
|
by: m3rajk |
last post by:
I've recently become the defacto DBA of two MSSQL databases at work because I am the only one with SQL experience. I have been asked to do some tasks with the databases but this requires and understanding of their structure. I cannot find anything akin to MySQL's mysqldump for mssql and have been looking for that among other help in being able to use my MySQL experience as a base for learning MSSQL. I have spent a few days (8 to 9 hours per day)...
| | |
by: intergroove |
last post by:
Help.
This has been bugging me for a couple of days now:
I am writing a script to regularly transfer data from a MYSQL db to a MSSQL.
Being new to MSSQL I'm a bit freaked out about the UNIQUEIDENTIFER (UI) system.
I have to INSERT data to a table where I need the UI for items from 2 other tables. This negates my ability to use a JOIN. So I need to grab the UI from within PHP and the reference the variable in the query.
|
by: bhing |
last post by:
Ciao!!..
I am currently working with mysql database now and i am trying to get some fields in the mssql database.table.. I want to automatically add those fields to my mysql database table, or if no table yet it wil create and insert the data there..
tnx...
|
by: Gosth in the shell |
last post by:
Hi there, i need some help
I got a software to backend is MSSQL 2005, but a provider software
requires an UPDATE on his table with MySQL 5.0.56a backend, so i made
the next:
download and install ODBC MySQL 3.51
configuring system DSN
add a linked server on MSSQL 2005
|
by: ssnaik84 |
last post by:
Hi Guys,
Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :)
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
