Zi 字媒體

利用程式技巧(使用大量記憶體)降低SQL操作負載(維度)(次數)藉此達到程式運作效率 (停用關聯子查詢)(SELECT多使用 LIMIT 0,1)   原本SQL:     SQL = String.Format(“SELECT u.id AS id,u.emp_no AS jobnum,u.security_id AS s_id,u.name AS name,d.name AS dname,u.attribute AS attribute,u.birthday AS birthday,(SELECT COUNT(*) FROM card_for_user_car WHERE card_for_user_car.user_id=u.id) AS card_count FROM user AS u ,department_detail AS d_d,department AS d WHERE ((u.id=d_d.user_id) AND ((d_d.car_id IS NULL) OR (d_d.car_id <1))) AND (d_d.dep_id=d.id) {2} ORDER BY u.id LIMIT {0} , {1};”, (m_intUserNowPage – 1) * 1000, 1000, m_SQL_user_condition01);//修改人員列表元件要有分頁功能(一次1000筆)   修正後的程式碼:     ArrayList AL_data01 = new ArrayList();     ArrayList AL_data02 = new ArrayList();     AL_data01.Clear();     AL_data02.Clear();          SQL = String.Format(“SELECT u.id AS id,u.emp_no AS jobnum,u.security_id AS s_id,u.name AS name,d.name AS dname,u.attribute AS attribute,u.birthday AS birthday FROM user AS u ,department_detail AS d_d,department AS d WHERE ((u.id=d_d.user_id) AND ((d_d.car_id IS NULL) OR (d_d.car_id <1))) AND (d_d.dep_id=d.id) {2} ORDER BY u.id LIMIT {0} , {1};”, (m_intUserNowPage – 1) * 1000, 1000, m_SQL_user_condition01);     AL_data01.Add(Strid + “,” + Strjobnum + “,” + Strs_id + “,” + Strname + “,” + Strdname + “,” + Strattribute + “,” + Strbirthday);          SQL = String.Format(“SELECT user_id,COUNT(*) FROM card_for_user_car GROUP BY user_id;”, (m_intUserNowPage – 1) * 1000, 1000, m_SQL_user_condition01);     AL_data02.Add(ReaderCount01[0].ToString() + “,” + ReaderCount01[1].ToString());          for (int i = 0; i < AL_data01.Count; i++)     {         String StrCount=”0″;          string[] strs01 = AL_data01[i].ToString().Split(‘,’);         for (int j = 0; j < AL_data02.Count; j++)         {             string[] strs02 = AL_data02[j].ToString().Split(‘,’);             if (strs01[0] == strs02[0])             {                 StrCount = strs02[1];                 break;             }         }         dgvSub0100_01.Rows.Add(false, strs01[0], strs01[1], strs01[2], strs01[3], strs01[4], strs01[5], strs01[6], StrCount);     }   note:     01.原SQL:人員如果10000 每人一張卡，那麼SQL的查詢表就會變成10000*10000=1億(phpmyadmin執行要160秒，C#會當機)     02.修改後:10000*1000=1仟萬(C#不用5秒)     03.等同 (虛擬表+ LEFT JOIN)