| Subject | Fastest method of moving records between databases? |
|---|---|
| Author | cantak3 |
| Post date | 2011-05-17T18:24:36Z |
I have a .Net 4.0 application using the Firebird .Net provider. I am using Firebird 2.0.
The application:
- Builds a list of tables/PKs in a source and destination database
- Disables sweep and all insert/update triggers in the destination
- Processes each record in each table in the source
- Checks if the record exists in the destination using the primary keys and selected values
- If the record exists, it updates it. If not, it inserts it.
- Reenables sweep and triggers once done, then updates generator values
There are other criteria for this select which I need not specify here. But this is the basic steps to copying records from one database to another for me. I cannot use FB 2.5's ability to connect to multiples. I cannot use the MERGE or the UPDATE OR INSERT methodology introduced in 2.1.
This is unbearably slow. Of course I expect it will be, but my processor hovers around 4% for each Firebird and my application.
I rewrote this to use parallel processing under .Net 4.0 (Parallel.ForEach Table in TableList). I create a new Firebird connection for this hoping to let Firebird use multiple processes over other cores. (Using SuperServer w/CPU Affinity set, pooling OFF). Now my application uses about 15% processor use, and Firebird uses 6%. Better, but still really slow.
I'm not looking for code or someone to do my work for me. But does anyone have an idea on what is the best way to speed this up? Under parallel connections I should be able to hit near full CPU usage shouldn't I? It's taking about five hours for a 1GB database.
The application:
- Builds a list of tables/PKs in a source and destination database
- Disables sweep and all insert/update triggers in the destination
- Processes each record in each table in the source
- Checks if the record exists in the destination using the primary keys and selected values
- If the record exists, it updates it. If not, it inserts it.
- Reenables sweep and triggers once done, then updates generator values
There are other criteria for this select which I need not specify here. But this is the basic steps to copying records from one database to another for me. I cannot use FB 2.5's ability to connect to multiples. I cannot use the MERGE or the UPDATE OR INSERT methodology introduced in 2.1.
This is unbearably slow. Of course I expect it will be, but my processor hovers around 4% for each Firebird and my application.
I rewrote this to use parallel processing under .Net 4.0 (Parallel.ForEach Table in TableList). I create a new Firebird connection for this hoping to let Firebird use multiple processes over other cores. (Using SuperServer w/CPU Affinity set, pooling OFF). Now my application uses about 15% processor use, and Firebird uses 6%. Better, but still really slow.
I'm not looking for code or someone to do my work for me. But does anyone have an idea on what is the best way to speed this up? Under parallel connections I should be able to hit near full CPU usage shouldn't I? It's taking about five hours for a 1GB database.