Zi 字媒體

利用程式技巧(使用大量記憶體)降低SQL操作負載(維度)(次數)藉此達到程式運作效率 (停用關聯子查詢)(SELECT多使用 LIMIT 0,1) – jashliao部落格

2021/12/25

利用程式技巧(使用大量記憶體)降低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);
   }