> Hi Experts, > I met a performance issue when using SQLite 3.8.4.3. I found it would be > quite slow trying to select count(*) using a where statement with several OR > condition. For example: > > sprintf (sql_str, "select ctrl_no from T_CTRL_CLRRULE where CTRL_NO='%s' and > ((PRODUCT_ID='%s' and OPERATE_TYPE='%s') or (PRODUCT_ID='AAAAAA' and > OPERATE_TYPE='%s') or (PRODUCT_ID='%s' and OPERATE_TYPE='AAAAAA')) and > CTRL_FLAG='%s'", sCtrlNo, sProductId, sOperType, sOperType, sProductId, > sCtrlFlag); > > The sql to create T_CTRL_CLRRULE is as following: > CREATE TABLE T_CTRL_CLRRULE (CTRL_NO TEXT,CTRL_NAME TEXT,CTRL_SYS > TEXT,PRODUCT_ID TEXT,OPERATE_TYPE TEXT,CTRL_FLAG TEXT); > CREATE UNIQUE INDEX UIDX_T_CTRL_CLRRULE on T_CTRL_CLRRULE > (CTRL_NO,PRODUCT_ID,OPERATE_TYPE,CTRL_FLAG); > CREATE INDEX I_T_CTRL_CLRRULE1 on T_CTRL_CLRRULE (CTRL_NO,CTRL_FLAG); > > We can see there is a unique key UIDX_T_CTRL_CLRRULE on t_ctrl_clrrule. The > select statement to get count(*) is based on unique index. There are > 2,000,000 data in t_ctrl_clrrule table. I try to change ctrl_no, product_id, > operate_type, ctrl_flag in above SQL statement to test the efficiency. The > result is that it will take 1.24ms to get the count result using above sql. > Then I split above sql to 3 seperate sql and test the efficiency again. The 3 > sql is as following: > > 1. sprintf (sql_str, "select count(*) from T_CTRL_CLRRULE where > CTRL_NO='%s' and PRODUCT_ID='%s' and OPERATE_TYPE='%s' and CTRL_FLAG='%s'", > sCtrlNo, sProductId, sOperType, sCtrlFlag); > 2. sprintf (sql_str, "select count(*) from T_CTRL_CLRRULE where > CTRL_NO='%s' and PRODUCT_ID='AAAAAA' and OPERATE_TYPE='%s' and > CTRL_FLAG='%s'", sCtrlNo, sOperType, sCtrlFlag); > 3. sprintf (sql_str, "select count(*) from T_CTRL_CLRRULE where > CTRL_NO='%s' and PRODUCT_ID='%s' and OPERATE_TYPE='AAAAAA' and > CTRL_FLAG='%s'", sCtrlNo, sProductId, sCtrlFlag); > > I wrote another program which will execute all above 3 sql every time and I > find it will only take 0.27ms on average executing all above 3 sql. > > we can see the sum result of above 3 sql is logically equal to the sql > statement with OR. But the efficiency is quite different. I want to know is > it a performance issue. Or should I split the where statement every time > manually if I met above requirement?
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
