Large data volume data storage sub-table instance (Enterprise Application System) with original code

As the data grows, the single table in the database cannot satisfy the storage of large data volumes, so we propose to store a large number of second-level data according to the natural time and the single-site information table.

For example: Atmospheric, Water Conservancy, Traffic (GPS) Information monitoring system in the real-time data storage, generally in accordance with the start time, end time, single site, multi-site, monitoring projects, such as data query, analysis, chart.

such as by 5 minutes single site data 12*24 (hours) *365 (days) * (monitoring items) 10=100w, that is, a site one year data volume 100w, 100 stations *100w = 100 million Such data is unable to meet the fast query.

So we will follow the "Tb_5m_ year _ Station number" table name, Tb_ Time Scale _ year _ station number to build a table. "tb_5m_2016_a0001", "tb_5m_2016_a0002", "tb_5m_2016_a0003",, "tb_5m_2016_a0004"

Table storage 100W, such as the storage of 1 minutes of data sheet 500W, such as 1 seconds of data: 60*500w=3 billion data, so not ah, we want to in the Sub-table, table rules add a month, Tb_5m_ _ month _ Station number, here do not say.

Question, how can we write the code quickly and easily? , we also want to use ORM (EF) for data query, take our real project.

Ideas that we use. NET development, in the Database Building basic table (Tb_5m_base) to implement EF, with DbContext to achieve data access.

Not much else to say, go straight to the code,

Function 5 minutes data query, user specified start time, end time, single (multi) Select site, single (multi) Select Monitoring project, data query function.

The code is issued as follows:

public class Tb_5m_base
{

public int id{get; set;}
Public Datatime time{get; set;}
public string Pcodes {get; set;}

public double values{get; set;}
}

public partial class Entityframeworkdatacontext:dbcontext
{
Static Entityframeworkdatacontext ()
{
Database.setinitializer<entityframeworkdatacontext> (NULL);
}


Public Entityframeworkdatacontext ()
: Base ("Name=entityframeworkdatacontext")
{
}

protected override void Onmodelcreating (Dbmodelbuilder modelBuilder)
{

MODELBUILDER.CONFIGURATIONS.ADD (New Tb_5m_base ());
}

Public dbset< tb_5m_base> tb_5m_base{get; set;}

}

public class Blldataquery
{

<summary>
Get Data
</summary>

<param name= "StartTime" > Start date </param>
<param name= "EndTime" > Results Date </param>

<param name= "Stationids" > Site id:1,2,3</param>

<param name= "Pcodus" > Monitoring Items:ec,pc,mc</param>
<returns></returns>
public static list<tb_5m_base> Getminutedata (DateTime startTime, DateTime endtime,string stationids, string Pcodus)
{
list<tb_5m_base> list = new list<tb_5m_base> ();
string[] Strarray = stationids. Split (new char[] {', '});
int year = Starttime.year;
int num = endtime.year;
String str = string. Empty;
String CommandText = String. Empty;
Pcodus=pcodus;
while (year <= num)//Years no problem
{
foreach (String Strstationid in Strarray)//Site
{
Tbname= string. Format ("Tb_5m_{0}_{1}_src", year, Strstationid);
CommandText = string. Format ("select * from {0}" where time between ' {1} ' and ' {2} ' and Pcodes in ({3}) ", new object[] {tbname, startTime, end Time, Pcodus});
Try
{
using (Entityframeworkdatacontext _dbcontext = entityframeworkdatacontext.createdbcontext)
{
List. AddRange (_dbcontext. Database.sqlquery<tb_5m_base> (sql,new paramentlist[0]). Tolist<tb_5m_base> ());
}
}
catch (Exception Exception)
{

}
}
year++;
}
return list;
}

Large data data storage sub-table instance (Enterprise Application System) with original code