Sqlserver accounts for more than 500 mb of memory, and the original program cannot query more than million data at a time, recording the solution to this problem.

We need to use "Data Synchronization" today Program "Re-Synchronize the fundyield data of the Internet database to the Intranet. The last time the data was successfully queried, the 50 million data records failed. I remember that there was a large amount of memory remaining on the internet server last time, and sqlserver only occupied 150 mb. This time, more than 500 mb of memory was occupied. In any case, the program could not query 50 million data at a time, and the query always timed out, however, this data is in a hurry. You have to find a solution.
The system used the Last modified Date (zhxgrq) field of each table as the update tag. After checking the data, it found that more than pieces of data were-1-1, unless the program queries all the pieces of data at a time, There is only one way. After reading the structure of the table, there is an ID field (bigint type). Although it is not a primary key, it is not repeated, so we can use this field as" Paging It is no problem to query 10-20 W data each time, so the original object class is changed to the following: Namespace Wft_datasyncmodel
{
[Serializable ()]
Public   Partial   Class Fundyield: entitybase, wcfmail. interface. idatasyncentity
{
Public Fundyield ()
{
Tablename =   " Fundyield " ;
Entitymap = Entitymaptype. sqlmap;
// Identityname = "Id field name ";

//Primarykeys. Add ("primary key field name ");
Primarykeys. Add ("Jjdm");
Primarykeys. Add ("FSRQ");

Propertynames =   New   String [] { " ID " , " Jjdm " , " Jjmc " , " Jjjc " , " Dwjz " , " Ljjz " , " FSRQ " , " Quarteryield " , " Dayyield " , " Weekyield " , " Weekyieldpm " , " Month1yield " , " Month1yieldpm " , " Month3yield " , " Month3yieldpm " , " Month6yield " , " Yearyield " , " Yearyieldpm " , " Year1yield " , " Year1yieldpm " , " Year2yield " , " Year3yield " , " Totalyield " , " Bzc3 " , " Bzc6 " , " Bzc12 " , " Bzc24 " , " Buystate " , " Addtime " , " Zhxgrq " , " Dayyieldpm " , " Month6yieldpm " , " Year2yieldpm " , " Year3yieldpm " , " Totalyieldpm " , " Dayyieldcount " , " Weekyieldcount " , " Month1yieldcount " , " Month3yieldcount " , " Month6yieldcount " , " Yearyieldcount " , " Year1yieldcount " , " Year2yieldcount " , " Year3yieldcount " , " Totalyieldcount " };
Propertyvalues =   New   Object [Propertynames. Length];

}
//... Object attributes are omitted here
}

 

In the object class fundyield, there is an object ing type attribute:
Entitymap = Entitymaptype. sqlmap ; // Ing to custom SQL queries

By default, it should be
Entitymap = entitymaptype. Table; // map to table

The data update object class must inherit a data update interface: wcfmail. interface. idatasyncentity

All right, the object class is modified here only. The ing of the object class is specified as the sqlmap type. A sqlmap configuration file must be created. The fixed file name is "entitysqlmap. config". The following is the file content: Code

<? XML version = "1.0" encoding = "UTF-8" ?>
<! -- SQL-MAP entity class custom query Profile
SQL statements cannot use the select * from table format. You must specify the same field definition as the object class. Otherwise, an unpredictable error may occur.
To generate an object class, use the pdf. Net object class tool.
Learn about http://www.pwmis.com/sqlmap for PDF. net

Power by DTH, 2010.12.8
-->
< Configuration >
< Namespace Name = "Wft_datasyncmodel" >
< Map Name = "Fundyield" >
< SQL >
<! [CDATA [
Select
ID, jjdm, jjmc, jjjc, dwjz, ljjz, FSRQ, quarteryield, dayyield, weekyield, california, California, totalyield, bzc3, bzc6, bzc12, bzc24, buystate, addtime, zhxgrq, dayyieldpm, California, totalyieldpm, dayyieldcount, large, small, yearyieldcount, year1yieldcount, year2yieldcount, year3yieldcount, totalyieldcount
From fundyield where ID <400000
]> </ SQL >
</ Map >

</ Namespace >
</ Configuration >

 

Note that the namespace and ing name must be consistent with the class definition.
OK, the required work is complete. We only changed the ing type of the object class and compiled an object class Query file, compiled the project, re-released the file, and started the execution, the rest is that the query conditions of the configuration file are modified each time. For example, I am using the following conditions:
Where ID & gt; = 600000 and ID & gt; <800000

The final task is to wait for the execution to complete, and the task is OK.


========================== Conclusion: using object-oriented methods (OO) can also be easily processed" Pure data problems ", Data is only part of an object. We put the data into the object for processing, making it easy for us to process new problems. This is the beauty of OO!