一些心得

最新推荐文章于 2024-05-18 22:14:55 发布

原创最新推荐文章于 2024-05-18 22:14:55 发布 · 322 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#dataset #table #xml

SQL 专栏收录该内容

30 篇文章

订阅专栏

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