22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].[proc_avgdata_AddUpdateavgdata]
@Timestamp datetime,
@TagName varchar(30),
@OperatorTurn smallint,
@Value float,
@Quality smallint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
if exists(select 1 from avgdata where TimeStamp=@Timestamp and TagName=@TagName)
begin
update avgdata set OperatorTurn=@OperatorTurn,Value=@Value, Quality=@Quality
where TimeStamp=@Timestamp and TagName=@TagName
end
else
begin
insert into avgdata (TimeStamp,TagName,OperatorTurn,Value,Quality)
values(@Timestamp,@TagName,@OperatorTurn,@Value,@Quality)
end
END
表建上索引试试.
/// <summary>
/// 批量写入小时汇总
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public bool UpdataHourBatch(List<SaveHourData> data,string tableName)
{
bool flag = false;
try
{
if (data != null && data.Count > 0)
{
string sql = "select top 1 * from " + tableName + " where TimeStamp >'" + data[0].TimeStamp.ToString() + "'";
SqlDataAdapter adatper = new SqlDataAdapter();
adatper.SelectCommand = new SqlCommand(sql, instance);
SqlCommandBuilder scb = new SqlCommandBuilder(adatper);
DataSet ds = new DataSet();
adatper.Fill(ds, tableName);
if (ds.Tables != null && ds.Tables.Count > 0)
{
DataTable addTable = ds.Tables[0].Clone();
for (int i = 0; i < data.Count; i++)
{
DataRow dr = addTable.NewRow();
dr["TimeStamp"] = data[i].TimeStamp;
dr["TagName"] = data[i].TagName;
dr["OperatorTurn"] = data[i].Turn;
dr["Value"] = data[i].Value;
dr["Quality"] = data[i].Quality;
addTable.Rows.Add(dr);
}
ds.Tables[0].Merge(addTable, true, MissingSchemaAction.Add);
adatper.Update(ds, tableName);
ds.Tables[tableName].AcceptChanges();
flag = true;
}
else
{
FileLog.FileLog.WriteTextToFile("Write " + tableName + " Error:Dataset 为空");
}
}
}
catch (Exception ex)
{
FileLog.FileLog.WriteTextToFile("Write " + tableName+ " Error:" + ex.Message.ToString());
flag = false;
}
return flag;
}
if exists(select 1 from avgdata where TimeStamp=@Timestamp and TagName=@TagName)
if exists(select top 1 1 from avgdata where TimeStamp=@Timestamp and TagName=@TagName)
select 1 from avgdata where TimeStamp=@Timestamp and TagName=@TagName
用时会越来越长。如果满足TimeStamp=@Timestamp and TagName=@TagName条件的记录很多,可以改为select top 1 1 from avgdata where TimeStamp=@Timestamp and TagName=@TagName