How can C # database applications solve the problem of high memory consumption?

Document directory

• 1. Start with the simplest DataAdapter. Fill (DataTable) method.
• 2. I tried to use DataReader to read data and then write it into dtDest.
• 3. Pure SQL statements. No DataTable
• 4. Output data to a text file in CSV instead of a Database
• 5. Export to XML

This is a post, but the question is not answered. So I simply put it on the Blog as a long-term question. In this case, someone may be able to give me some clues.

When I use C # For database applications, I often encounter a problem that memory consumption is extremely high. This situation
It is more obvious when importing and exporting databases with large data volumes.
Let's talk about a common Case of Database Import and Export. Let's take a look at the reasonable solution:

Environment:
There is a database named SRC, which contains 5 million records. There are three fields, A, B, and C. Database size Removal
About MB after water loss. This is used as the source database.
The database DEST is the target database with five fields, including AA, BB, CC, DD, EE, and FF.

The values of AA, BB, and CC are calculated based on the values of SRC. A and B. And the process is complex, so
It is not suitable for using stored procedures. In addition, AA, BB, and CC are not obtained separately.
SRC. B (of course, each value can be obtained after three operations, but the calculation is three times larger ).

Purpose:
Import all data in SRC to the DEST database.

Methods I have tried:

1. Start with the simplest DataAdapter. Fill (DataTable) method.

Then
Foreach (DataRow rowsrc in dtSrc ){
DataRow row = dtDest. NewRow ();
....
DtDest. Rows. Add (row );
}
DaDest. Update (dtDest );
DtDest. AcceptChanges ();

This is obviously not a way to deal with big data migration.
The most obvious problem is the high memory consumption. The MB database table is changed to the able in DataSet,
The volume is much larger. Add the copy in dtDest and the resources occupied by daDest. Update,
It is easy to exceed 2 GB of application memory address space, and the error is returned and exited.

2. I tried to use DataReader to read data and then write it into dtDest.

Using (... reader = ...){
While (reader. Read ()){
DataRow row = dtDest. NewRow ();
....
DtDest. Rows. Add (row );
}
}
DaDest. Update (dtDest );
DtDest. AcceptChanges ();
Memory usage is much less, because SRC reads data from DataReader, it seems that it will not be used later,
But some are released. However, there is no need to store dtdest data in the memory. However
No way. Even after dadest. Update and dtdest. acceptchanges (),
Save. Therefore, putting dadest. Update and dtdest. acceptchanges () in a loop cannot be
Memory usage has been improved.

3. Pure SQL statements. No DataTable

Use sqlcommand. executenonquery () to execute the "insert into..." SQL statement. This way
The memory usage is greatly reduced. To achieve the "Pipeline" effect. But the most come
Is indeed a more serious problem. In this way, the disk space is greatly occupied. The database generates a large number of logs,
And deprecated space. Taking access as an example, after a program runs for a period of time, the maximum file size exceeds the limit of 2 GB.
. After "compressing and restoring the Database" is executed, it is only about MB. As shown in the preceding figure, the data generated is too much garbage.
Large. Of course, SQL server does not have a limit of 2 GB, but such a large number of logs and discarded empty
Is definitely not a suitable result. In addition, if the ratio of security is MB,
It will occupy more than 10 Gb of storage space, which is a big waste. Of course, it can be compressed afterwards,
The waste of space in the processing process cannot be ignored.

4. Output data to a text file in CSV instead of a Database

This is a solution. At least it will not occupy any memory, nor will it waste hard disk space. But trouble
The most important type information is lost. For strings and numbers, it is better to say that the string does not exist at most.
With the length, the numbers do not have the precision, but they can still be saved. Some Complex classes are troublesome.
Type, such as image, binary, and guid. This byte stream type cannot be reported.
In CSV.

5. Export to XML

Of course, it is better to use XML, but the Parse stored in Binary is also very resource-consuming, and the XML
How can I import data to a database? With C #, I went back to the old road. SQL Server supports XML,
But what about other databases? For example, Access? After all, not everyone buys SQL Server or
In some cases, SQL Server is suitable for use.

Is there any good way to solve this problem? I think the memory occupied by a large database
Use is a very obvious problem. There may be a very simple way I took a lot of detours without considering it. Thank you.

Dancefire
---
CCNA
Http://www.dancefires.com/
Http://blog.csdn.net/dancefire/
MSN: Dancefire@263.net
I am interested in Operating System, Embedded System and Network Security.