/// <summary>
/// 更新数据库成长值和xml
/// </summary>
public static void UpVip()
{
DateTime newtime = DateTime.Now; DbAccess db = new DbAccess(oConn.ConnectionString);
string sqlsel = "select reg_id,reg_lastday,reg_vipRank from reg where reg_id>0 and reg_rank<> 2 and reg_vipRank>0"; DataTable table = db.GetTable(sqlsel);
for (int i = 0; i < table.Rows.Count; i++)
{
string sql = "";
if (DateTime.Parse(table.Rows[i]["reg_lastday"].ToString()) > newtime)
sql = "update reg set reg_vipRank=reg_vipRank+10 where reg_id=";
else if (int.Parse(table.Rows[i]["reg_vipRank"].ToString()) >= 5)
sql = "update reg set reg_rank =0, reg_vipRank=reg_vipRank-5 where reg_id=";
else
sql = "update reg set reg_rank =0, reg_vipRank=0 where reg_id=";
sql += table.Rows[i]["reg_id"].ToString();
if (db.GetState(sql))
CreateMemberInfoData(int.Parse(table.Rows[i]["reg_id"].ToString()));
} }用控制台写了个更新会员成长值的程序(晚上2点执行),因为数据量很大(普通会员300多万,VIP有5千左右),要在300万数据中查询这5千条数据进行更新(成长值+10),感觉速度超慢,占数据库资源太大了,大牛们有没有更好的方法节省资源。本人菜鸟一个,也没什么经验。望指点指点。谢谢
/// 更新数据库成长值和xml
/// </summary>
public static void UpVip()
{
DateTime newtime = DateTime.Now; DbAccess db = new DbAccess(oConn.ConnectionString);
string sqlsel = "select reg_id,reg_lastday,reg_vipRank from reg where reg_id>0 and reg_rank<> 2 and reg_vipRank>0"; DataTable table = db.GetTable(sqlsel);
for (int i = 0; i < table.Rows.Count; i++)
{
string sql = "";
if (DateTime.Parse(table.Rows[i]["reg_lastday"].ToString()) > newtime)
sql = "update reg set reg_vipRank=reg_vipRank+10 where reg_id=";
else if (int.Parse(table.Rows[i]["reg_vipRank"].ToString()) >= 5)
sql = "update reg set reg_rank =0, reg_vipRank=reg_vipRank-5 where reg_id=";
else
sql = "update reg set reg_rank =0, reg_vipRank=0 where reg_id=";
sql += table.Rows[i]["reg_id"].ToString();
if (db.GetState(sql))
CreateMemberInfoData(int.Parse(table.Rows[i]["reg_id"].ToString()));
} }用控制台写了个更新会员成长值的程序(晚上2点执行),因为数据量很大(普通会员300多万,VIP有5千左右),要在300万数据中查询这5千条数据进行更新(成长值+10),感觉速度超慢,占数据库资源太大了,大牛们有没有更好的方法节省资源。本人菜鸟一个,也没什么经验。望指点指点。谢谢
from reg
where reg_id>0 and reg_rank<> 2 and reg_vipRank>0这样不行吗?
一句话帮你搞定
when reg_vipRank>=5 then reg_vipRank-5 else 0 end ,
reg_rank = case when reg_lastday>getdate() then reg_rank
else 0 end where reg_id>0 and reg_rank<> 2 and reg_vipRank>0
不趴窝才出鬼了