一些心得

DataSet转XML:

 

DataSet dataSet = new DataSet();
SqlConnection sqlConn = null;

sqlConn = new SqlConnection("Data Source=10.108.0.1;Initial Catalog=EPSS;User ID=***;Password=***");
sqlConn.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT * FROM ERPEPS", sqlConn);
sqlDataAdapter.Fill(dataSet,"ERPEPS");
sqlConn.Close();

dataSet.DataSetName = "DataSet1";
dataSet.Namespace = "http://tempuri.org/DataSet1.xsd"; 
dataSet.Tables["ERPEPS"].WriteXml(Application.StartupPath + @"/" + Application.ProductName + ".xml");

 

其他:

修改大数据量的字段属性

alter table 表名 alter column 字段 char(20)

select min(rq) from xs where rq>dateadd(month,-3,getdate())

 

给有180多万条记录的表增加了2个索引，牵涉到的相关查询，速度一下子飞快起来。

NYID+RQ索引
ORDERID索引

 

 

SELECT NYID,XE FROM XS WHERE left(NYID,4)='2009' AND GH='000' ORDER BY NYID
compute sum(XE)   <-合计所有月份的XE

 

SELECT NYID,XE FROM XS WHERE left(NYID,4)='2009' AND GH='000' ORDER BY NYID
compute sum(XE) by NYID   <-按每个月份合计XE