Your ZeosLib DBO Version: 6.6.1 beta
The IDE you are using: Delphi
Version (and maybe patchlevel) of the IDE: 2007
The database you are using: MySQL
Version of the database: 5.x
Used protocol: 5
[hr]Error description and post
BatchMove (as seen with BDE)
Simple way of copying data from a table or query (columns formatted in the same way) to another table.
As seen with the BDE the tables/queries do not even need to be on the same database engine/server.
Also notice that it is incredibly fast when using BatchMove (BDE).
Would be awesome to have that with ZEOS -- otherwise I'd still have to include the complete BDE setup when distributing my tools/applications.
(BDE BatchMove even takes a ZEOS dataset as a source or target)
BatchMove a-la BDE
Moderators: gto, cipto_kh, EgonHugeist, mdaems
Hi Hansaplast,
I assume you have tried
"insert into destination_table select * from source_table"
and found that this is far superior to batchmove, but need to move data between databases.
MySQL works on the assumption that the databases are not on the same PC and requires the user to
"select into outfile" and bulk load the result into the destination database.
This process is exponentially faster than anything BDE has ever been able
to achieve.
This option works well if user has access the the location where the "outfile" resides. When extracting data from a remote server the security policies kick in and prevent this method from owrking directly. The easiest way to deal with this is to shell out and do a mySQLDump and restore. Not that all of these methods are optimised to move LARGE LARGE volumes of records over the wire.
Personally, the added power that MySQL and Zeos offer, compensate for the need to work around a batchmove.
Hope that the above comments are useful.
Regards
M Parak
Durban South Africa
I assume you have tried
"insert into destination_table select * from source_table"
and found that this is far superior to batchmove, but need to move data between databases.
MySQL works on the assumption that the databases are not on the same PC and requires the user to
"select into outfile" and bulk load the result into the destination database.
This process is exponentially faster than anything BDE has ever been able
to achieve.
This option works well if user has access the the location where the "outfile" resides. When extracting data from a remote server the security policies kick in and prevent this method from owrking directly. The easiest way to deal with this is to shell out and do a mySQLDump and restore. Not that all of these methods are optimised to move LARGE LARGE volumes of records over the wire.
Personally, the added power that MySQL and Zeos offer, compensate for the need to work around a batchmove.
Hope that the above comments are useful.
Regards
M Parak
Durban South Africa
well, I did not use BDE too much and did nothing like you said with it.
But sometimes I also need to transfer numerous records between similar structures, stored sometimes in heterogenous database environment.
I know 2 ways to solve this problem:
- as it is mentioned here, bulk copy operations. It is DB-specific and you should do it the way your SQL server likes it the best. I can only explain how I did it with Mircosoft SQL.
- the other way is a generic record copying from one DataSet into another. The scenario is:
open a couple of DataSets, a Source and a Dest. Obviously, you should open a Dest DataSet in Read\Write mode (ReadOnly=False of RequestLive=True);
disable controls for both of 'em in a try\finally clause that finally enables 'em.
next you should distinguish corresponding fields in both dataset the way you like. I index fields to copy fields with same names from src to dest, but you may do it using other criteria - field indexes or types.
Anyway, after you indexed 'em you do TSourceDataSet.First and copy it into dest dataset record by record, field by field.
I did not place code into this post cause it's long a bit and.. er.. It is a bit similar to code of one of commercial MemTables.
So if it is interesting for anyone - i can send cuts by email or in a private message or place it here if team members assume it's OK.
The thing I wanted to underline is the diffwrence between this methods.
- bulk insert methods is the fastest way to transfer data and should be used when you deal with big bunches of records.
- dataset copy is very simple to use in your code. you do not need to know how bulk copying is done in your environment, in fact, you do not even need to know which database you will work with when designing this transferring code. You also may use different kinds of DataSets for source and destination (I had to use TZQuery with TADOQUery once, for instance). The only thing that should be done - connect a couple of datasets to databases, feed the source DataSet with data and make sure that your copying algorithm has info to find correspondence between fields in both datasets.
The weak point is speed. Especially when destination server is remote. In this case copying of each record is performed by executing a single insert statement (even if you are using a cached updates) and with a large table it may take hours and more to perform this transfer.
Sorry for enormous amount of symbols in post... Wanted to tell it in a couple of words, but it seems to be a nowell having been typed...
But sometimes I also need to transfer numerous records between similar structures, stored sometimes in heterogenous database environment.
I know 2 ways to solve this problem:
- as it is mentioned here, bulk copy operations. It is DB-specific and you should do it the way your SQL server likes it the best. I can only explain how I did it with Mircosoft SQL.
- the other way is a generic record copying from one DataSet into another. The scenario is:
open a couple of DataSets, a Source and a Dest. Obviously, you should open a Dest DataSet in Read\Write mode (ReadOnly=False of RequestLive=True);
disable controls for both of 'em in a try\finally clause that finally enables 'em.
next you should distinguish corresponding fields in both dataset the way you like. I index fields to copy fields with same names from src to dest, but you may do it using other criteria - field indexes or types.
Anyway, after you indexed 'em you do TSourceDataSet.First and copy it into dest dataset record by record, field by field.
I did not place code into this post cause it's long a bit and.. er.. It is a bit similar to code of one of commercial MemTables.
So if it is interesting for anyone - i can send cuts by email or in a private message or place it here if team members assume it's OK.
The thing I wanted to underline is the diffwrence between this methods.
- bulk insert methods is the fastest way to transfer data and should be used when you deal with big bunches of records.
- dataset copy is very simple to use in your code. you do not need to know how bulk copying is done in your environment, in fact, you do not even need to know which database you will work with when designing this transferring code. You also may use different kinds of DataSets for source and destination (I had to use TZQuery with TADOQUery once, for instance). The only thing that should be done - connect a couple of datasets to databases, feed the source DataSet with data and make sure that your copying algorithm has info to find correspondence between fields in both datasets.
The weak point is speed. Especially when destination server is remote. In this case copying of each record is performed by executing a single insert statement (even if you are using a cached updates) and with a large table it may take hours and more to perform this transfer.
Sorry for enormous amount of symbols in post... Wanted to tell it in a couple of words, but it seems to be a nowell having been typed...
-
Hansaplast
- Fresh Boarder
- Posts: 2
- Joined: 24.04.2007, 21:44
Thanks mparak and zx for the input! Very much appreciated!
As indicated, copying from dataset to dataset is good for a small number of records. Which is not the case here ...
I did try that approach and it's not to hard to do. But very very slow with a large number of records.
The insert select from trick would indeed be superior, but both databases reside on a different server ...
Bulk insert would be great ... if I could access both servers on a file level, from within an application without things becoming to complicated. So not a real option either. (unless I'm missing something here)
Here is were BDE BatchMove (I'm not a BDE fan myself either!) is super fast! With bulk, give it a try some time.
Since the BDE natively is not using MySQL, yet manages to use (via Zeos) to still copy at very high speed,... well, that makes me think that at a lowlevel we should be able to do something similar with just Zeos components (not using BDE) ...
As indicated, copying from dataset to dataset is good for a small number of records. Which is not the case here ...
I did try that approach and it's not to hard to do. But very very slow with a large number of records.
The insert select from trick would indeed be superior, but both databases reside on a different server ...
Bulk insert would be great ... if I could access both servers on a file level, from within an application without things becoming to complicated. So not a real option either. (unless I'm missing something here)
Here is were BDE BatchMove (I'm not a BDE fan myself either!) is super fast! With bulk, give it a try some time.
Since the BDE natively is not using MySQL, yet manages to use (via Zeos) to still copy at very high speed,... well, that makes me think that at a lowlevel we should be able to do something similar with just Zeos components (not using BDE) ...
-
mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Hi Hansaplast,
Not talking about BatchMove yet. I still have to read the Delphi documentation.
Do you know what trick they use to have it that quick? I suppose you're talking about the TBatchMove component? Or is it some method from query or table components?
As you are using mysql : have you tried the mysql federated table engine in combination with 'insert into XXX select XX from XXX'? I'm not sure about performance, but it should work.
Mark
Not talking about BatchMove yet. I still have to read the Delphi documentation.
Do you know what trick they use to have it that quick? I suppose you're talking about the TBatchMove component? Or is it some method from query or table components?
As you are using mysql : have you tried the mysql federated table engine in combination with 'insert into XXX select XX from XXX'? I'm not sure about performance, but it should work.
Mark
Hi Mark,
I for one have been looking forward to using federated tables to achieve just this very task. I found however that the concept was not too evolved and that it seems to have been designed for small batches of data. The fact that the "show create" command reveals the username, password and full url to the host database does not help.
Regards
M Parak
Durban South Africa
I for one have been looking forward to using federated tables to achieve just this very task. I found however that the concept was not too evolved and that it seems to have been designed for small batches of data. The fact that the "show create" command reveals the username, password and full url to the host database does not help.
Regards
M Parak
Durban South Africa