access 數(shù)據(jù)庫(kù)的一個(gè)優(yōu)化實(shí)例

一.起因

由于access數(shù)據(jù)庫(kù)安裝容易,操作方便,深受數(shù)據(jù)量不大的用戶歡迎.但數(shù)據(jù)操作方面,微軟為了安全起見(jiàn),對(duì)SQL語(yǔ)法進(jìn)行了大量簡(jiǎn)化和修改.主要修改有以下幾個(gè)方面:
1.一些復(fù)雜的標(biāo)準(zhǔn)SQL語(yǔ)法編寫(xiě)的數(shù)據(jù)操縱語(yǔ)句不能執(zhí)行.
2.每次查詢僅中能執(zhí)行一個(gè)語(yǔ)句.
以上限制對(duì)大量插入數(shù)據(jù)的用戶來(lái)說(shuō),執(zhí)行效率大幅下降.最近開(kāi)發(fā)的一個(gè)軟件 ,以插入860行數(shù)據(jù)為例,如果一次插入一行數(shù)據(jù),(當(dāng)然還有準(zhǔn)備數(shù)據(jù)時(shí)間),大約需要65秒.部分程序如下:

 protected void Button2_Click(object sender, EventArgs e){DataView dv;DataRowView drv;string projectID;       this.Label3.Text = "錯(cuò)誤！請(qǐng)檢查訂單類(lèi)別、商品類(lèi)別、差異率等!";string s = Session["xmkS0"].ToString();this.SDSxmk.SelectCommand = s;dv =(DataView) this.SDSxmk.Select(DataSourceSelectArguments.Empty);if (dv != null){for (int i = 0; i < dv.Count; i++){if (sele(i)){drv = dv[i];projectID = drv["xmdm"].ToString();if (book(drv)){int n = i>>4;int k = (int)Session["P" + n.ToString()];k = k & (65535 ^ (1 << (i - n * 16)));Session["P" + n.ToString()] = k;updatexmkBookflag(projectID, true);}}}}        //this.Label3.Text = "";}private void updatexmkBookflag(string projectID, bool b){string s0, s1, s2;s0 = SDSxmk.UpdateCommand;s1 = "update xmk set bookFlag={0} where xmdm='{1}'";s1 = string.Format(s1,b.ToString(), projectID);SDSxmk.UpdateCommand = s1;SDSxmk.Update();SDSxmk.UpdateCommand = s0;}

二.優(yōu)化

1.由于一次只能執(zhí)行一個(gè)查詢語(yǔ)句,所以我就想起用事務(wù)來(lái)一次執(zhí)行多個(gè)語(yǔ)句,試著進(jìn)行優(yōu)化,效果出人意料.
代碼片段:

 protected void Button2_Click(object sender, EventArgs e)//生成憑證  {DataView dv;DataRowView drv;string projectID;this.Label3.Text = "錯(cuò)誤！請(qǐng)檢查訂單類(lèi)別、商品類(lèi)別、差異率等!";string s1 = "update xmk set bookFlag={0} where xmdm='{1}'";string s = Session["xmkS0"].ToString();string s0 = SDSxmk.UpdateCommand;this.SDSxmk.SelectCommand = s;dv = (DataView)this.SDSxmk.Select(DataSourceSelectArguments.Empty);if (dv != null){OleDbConnection conn = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);OleDbCommand command = conn.CreateCommand();conn.Open();command.Transaction = conn.BeginTransaction();try{for (int i = 0; i < dv.Count; i++){if (sele(i)){drv = dv[i];projectID = drv["xmdm"].ToString();if (book(drv)){int n = i >> 4;int k = (int)Session["P" + n.ToString()];k = k & (65535 ^ (1 << (i - n * 16)));Session["P" + n.ToString()] = k;command.CommandText = string.Format(s1, true, projectID);command.ExecuteNonQuery();}}}command.Transaction.Commit();}catch (Exception ex){command.Transaction.Rollback();}conn.Close();}this.SDSxmk.SelectCommand = Session["xmkS0"].ToString(); ;this.GridView2.SetPageIndex(35);this.Label3.Text = "";}

三.啟示

1.select 語(yǔ)句是執(zhí)行效率極高的.凡是能在select 語(yǔ)句完成的查詢,就不要用數(shù)據(jù)集,數(shù)據(jù)源等進(jìn)行操作,能用復(fù)合select語(yǔ)句的,就使用復(fù)合語(yǔ)句.
2.能使用存儲(chǔ)教程操作的,就用存儲(chǔ)過(guò)程(在SQLserver中特別高效).
3.能使用多條插入的查詢,就使用如(insert into xxx select ***);別在編程中,用數(shù)據(jù)集來(lái)操縱.效率下降10倍以上.
4.不能使用多條語(yǔ)句的情況下.就使用事務(wù).效率一樣很高.
5.兩個(gè)表更新,使用update xxx inner left xxx on xxx=xxx set xxx=xxx,更高效.
6.總之,能使用一次連接,完成數(shù)據(jù)庫(kù)增刪改查的,就一次完成,最高效.對(duì)數(shù)據(jù)的不管多復(fù)雜的操作,直接用sql 語(yǔ)句完成就對(duì)了,千萬(wàn)別想著自己編程,再使用sql語(yǔ)句來(lái)更新,效率下降十倍以上.