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