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
