由于数据量巨大,数据库分了99999张表(tab_10000 - tab_99999)
程序导入txt文本, 读取每一行数据然后批量插入数据库,其中存储过程代码如下ALTER PROCEDURE [dbo].[AddData](@QQNumber bigint,@QQPassword varchar(1000),@BeiZhu varchar(1000)=null)
AS
BEGIN
declare @tableNumber varchar(10);
declare @sql nvarchar(2000);
declare @count bigint;
declare @countsql nvarchar(2000);
set @tableNumber=Substring(CONVERT(varchar(20),@QQNumber),1,5);
set @countsql='select @count=count(QQNumber) from tab_'+@tableNumber+' where QQNumber='+Convert(varchar(20),@QQNumber);
exec sp_executesql @countsql,N'@count int output',@count output;
print @count;
begin Transaction;
if(@count<>0)
begin
set @QQPassword='∝'+@QQPassword;
set @sql='update tab_'+@tableNumber+' set QQPassword=QQPassword+'''+@QQPassword+'''where QQNumber='+Convert(varchar(20),@QQNumber);
exec(@sql);
end
else
begin
set @sql='insert into tab_'+@tableNumber+' (QQNumber,QQPassword,BeiZhu) values('''+Convert(varchar(20),@QQNumber)+''','''+@QQPassword+''','''+isnull(@BeiZhu,'NULL')+''')';
exec(@sql);
end
if(@@ERROR<>0)
begin
rollback Transaction;
return 0;
end
else
begin
commit Transaction;
return 1;
end
END
由于导入的数据很大, 所以我在程序里做了字符串拼接, 一次性插入多条数据for (int i = 0; i < newdatas.Length; i++)
{
string[] tempstr = newdatas[i].Replace("----", "∽").Split('∽');
sql += "exec AddData @QQNumber=" + tempstr[0] + ",@QQPassword='" + tempstr[1].Replace("'", "") + "',@BeiZhu=null ";
if (i % 500 == 0)
{
ds.AddData(sql);
sql = string.Empty;
}
current = i;
}
current = newdatas.Length;
ds.AddData(sql);
sql = string.Empty;其中newdatas的数组长度大概在500W左右,我每拼接500次 就执行一次sql ,程序在前几天导入都没有错误, 但在数据库数据量日益庞大的情况下, 程序出现了TimeOut超时错误, 执行等待超时, 请问ado.net里如何设置等待时间,或者从sql语句上下手,还有什么地方可以优化下的, 期待高手建言献策,小弟感激不尽
程序导入txt文本, 读取每一行数据然后批量插入数据库,其中存储过程代码如下ALTER PROCEDURE [dbo].[AddData](@QQNumber bigint,@QQPassword varchar(1000),@BeiZhu varchar(1000)=null)
AS
BEGIN
declare @tableNumber varchar(10);
declare @sql nvarchar(2000);
declare @count bigint;
declare @countsql nvarchar(2000);
set @tableNumber=Substring(CONVERT(varchar(20),@QQNumber),1,5);
set @countsql='select @count=count(QQNumber) from tab_'+@tableNumber+' where QQNumber='+Convert(varchar(20),@QQNumber);
exec sp_executesql @countsql,N'@count int output',@count output;
print @count;
begin Transaction;
if(@count<>0)
begin
set @QQPassword='∝'+@QQPassword;
set @sql='update tab_'+@tableNumber+' set QQPassword=QQPassword+'''+@QQPassword+'''where QQNumber='+Convert(varchar(20),@QQNumber);
exec(@sql);
end
else
begin
set @sql='insert into tab_'+@tableNumber+' (QQNumber,QQPassword,BeiZhu) values('''+Convert(varchar(20),@QQNumber)+''','''+@QQPassword+''','''+isnull(@BeiZhu,'NULL')+''')';
exec(@sql);
end
if(@@ERROR<>0)
begin
rollback Transaction;
return 0;
end
else
begin
commit Transaction;
return 1;
end
END
由于导入的数据很大, 所以我在程序里做了字符串拼接, 一次性插入多条数据for (int i = 0; i < newdatas.Length; i++)
{
string[] tempstr = newdatas[i].Replace("----", "∽").Split('∽');
sql += "exec AddData @QQNumber=" + tempstr[0] + ",@QQPassword='" + tempstr[1].Replace("'", "") + "',@BeiZhu=null ";
if (i % 500 == 0)
{
ds.AddData(sql);
sql = string.Empty;
}
current = i;
}
current = newdatas.Length;
ds.AddData(sql);
sql = string.Empty;其中newdatas的数组长度大概在500W左右,我每拼接500次 就执行一次sql ,程序在前几天导入都没有错误, 但在数据库数据量日益庞大的情况下, 程序出现了TimeOut超时错误, 执行等待超时, 请问ado.net里如何设置等待时间,或者从sql语句上下手,还有什么地方可以优化下的, 期待高手建言献策,小弟感激不尽
1。在sqlserver的属性中;
2。可能在asp.net的connection控件中;
准确的记不清楚了,你找找看
public static string Init()
{
XmlDocument xml = new XmlDocument();
xml.Load(AppDomain.CurrentDomain.BaseDirectory + "\\Xml\\Config.xml"); //读取配置文件
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(xml.SelectSingleNode("root/ConnectionString").Attributes["Address"].Value); //指定连接字符串
builder.LoadBalanceTimeout = 10; //在被销毁前连接的最短存活时间
builder.MaxPoolSize = 2000; //最大连接数
builder.MinPoolSize = 10; //最小连接数
builder.AsynchronousProcessing = true; //允许异步连接
builder.Pooling = true; //打开连接池
builder.ConnectTimeout = 999999; //等待响应时间
builder.ConnectionReset = false;
return builder.ConnectionString;
}
sqlserver属性,大哥, 说清楚点。。太模糊了。。在数据库里设置吗?
-》SQL Sserver组-》鼠标右键-》属性-》打开“连接”页上-》修改查询超时设定时间即可,一般
缺省是600ms
根据你的提示 我已经修改了SqlCommand的CommandTimeout属性 using (SqlCommand cmd = new SqlCommand(sql, con) { CommandType = commandType, CommandTimeout = 99999 })
但我觉得,设置TimeOut时间不是解决问题的根本, 我觉得数据库还可以做进一步优化,但我数据库才疏学浅, 不知道还能怎么修改