Sorry, the first sql statement in my previous mail is incorrect. It is not " 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);"
It should be " sprintf (sql_str, "select count(*) 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);" Thanks for Clemens' correction. Hi Clemens, I will try your advice and feed back the test result tomorrow. >Message: 5 >Date: Sat, 21 Jun 2014 21:53:58 +0800 >From: 163 <sdu...@163.com> >To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> >Subject: [sqlite] Performance issue using SQLite >Message-ID: <790e34bd-c627-4dd6-872e-2b358a6d1...@163.com> >Content-Type: text/plain; charset=us-ascii > > >> 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? > > > >------------------------------ > >Message: 6 >Date: Sat, 21 Jun 2014 16:12:13 +0200 >From: Clemens Ladisch <clem...@ladisch.de> >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Performance issue using SQLite >Message-ID: <53a592bd.5020...@ladisch.de> >Content-Type: text/plain; charset=UTF-8 > >163 wrote: >> 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: >> >> 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' > >This query does not select count(*). > >Anyway, its EXPLAIN QUERY PLAN output is: > > 0|0|0|SEARCH TABLE T_CTRL_CLRRULE USING INDEX I_T_CTRL_CLRRULE1 (CTRL_NO=? > AND CTRL_FLAG=?) > >> 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. >> >> 1. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and >> PRODUCT_ID='%s' and OPERATE_TYPE='%s' and CTRL_FLAG='%s' >> 2. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and >> PRODUCT_ID='AAAAAA' and OPERATE_TYPE='%s' and CTRL_FLAG='%s' >> 3. select count(*) from T_CTRL_CLRRULE where CTRL_NO='%s' and >> PRODUCT_ID='%s' and OPERATE_TYPE='AAAAAA' and CTRL_FLAG='%s' > >The EXPLAIN QUERY PLAN output is the same for all three: > > 0|0|0|SEARCH TABLE T_CTRL_CLRRULE USING COVERING INDEX UIDX_T_CTRL_CLRRULE > (CTRL_NO=? AND PRODUCT_ID=? AND OPERATE_TYPE=? AND CTRL_FLAG=?) > >> I find it will only take 0.27ms on average executing all above 3 sql. > >The combined OR conditions are too complex for the query optimizer, so >it does index lookup only on the other columns. > >> should I split the where statement every time manually if I met above >> requirement? > >You could combine the three queries like this: > > select (select count(*) ...) + (select count(*) ...) + (select count(*) ...) > >But better try this instead: > > select count(*) > from T_CTRL_CLRRULE > where CTRL_NO='%s' > and PRODUCT_ID in ('%s', 'AAAAAA') > and OPERATE_TYPE in ('%s', 'AAAAAA') > and (PRODUCT_ID != 'AAAAAA' or OPERATE_TYPE != 'AAAAAA') > and CTRL_FLAG='%s' > > >Regards, >Clemens > > >------------------------------ > >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > >End of sqlite-users Digest, Vol 78, Issue 19 >******************************************** _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users