'like' performance

[MikeFranz]

I have a some pretty large tables in my (now encrypted) SQLITE DB.
200'000 rows is a typical table size. The tables are indexed and using
an unencrypted DB all queries perform superbly, both on the Pro and on
the device.

With the encrypted DB most queries work fine also, but when it comes
to using LIKE 'xxx%' in the where clause (against an indexed column)
performance takes a nosedive (10s is typical on the MacBook Pro;
haven't bothered to tried it on the device.)

Additional observations::

- Limiting the result test using LIMIT n but this has no impact on
performance even though the result set is (obviously) smaller than it
otherwise would have been.

- The same delay is experienced regardless of whether the search value
includes a wildcard '%'

- The app bombs if the search value doesn't include a wildcard(!)
following LIKE

2010-05-04 11:05:11.844 CNO[22500:207] *** Terminating app due to
uncaught exception 'NSInternalInconsistencyException', reason: 'Could
not run query'
*** Call stack at first throw:
(
0   CoreFoundation                      0x02a4ab79 __raiseError + 393
1   libobjc.A.dylib                     0x98924509
objc_exception_throw + 56
2   CoreFoundation                      0x02a4a8e8 +[NSException
raise:format:arguments:] + 136
3   Foundation                          0x0024715b -
[NSAssertionHandler
handleFailureInMethod:object:file:lineNumber:description:] + 116
4   CNO                                 0x00002e1b -[Queries query2]
+ 548
5   CNO                                 0x00002732 -[CNOAppDelegate
application:didFinishLaunchingWithOptions:] + 335
6   UIKit                               0x0042ca7f -[UIApplication
_callInitializationDelegatesForURL:payload:suspended:] + 1062
7   UIKit                               0x0042eaee -[UIApplication
_runWithURL:payload:launchOrientation:statusBarStyle:] + 333
8   UIKit                               0x004359e8 -[UIApplication
handleEvent:withNewEvent:] + 1858
9   UIKit                               0x00430ccb -[UIApplication
sendEvent:] + 71
10  UIKit                               0x00438ee2
_UIApplicationHandleEvent + 7130
11  GraphicsServices                    0x03267468
PurpleEventCallback + 1546
12  CoreFoundation                      0x029d1c76 __CFRunLoopRun +
4646
13  CoreFoundation                      0x029d0573
CFRunLoopRunSpecific + 371
14  CoreFoundation                      0x029d03f1 CFRunLoopRunInMode
+ 97
15  UIKit                               0x0042e66b -[UIApplication
_run] + 611
16  UIKit                               0x00436ff1 UIApplicationMain
+ 1114
17  CNO                                 0x000025c0 main + 102
18  CNO                                 0x00002551 start + 53
19  ???                                 0x00000001 0x0 + 1
)




Anyone else have problems with LIKE on an encrypted DB?

Regards

Mike

[MikeFranz]

Apologies: The bomb previously described was an error on my part.
Nothing whatsoever to do with 'like' or SQLCipher. The performance
question remains, however.

Mike

[Stephen Lombardo]

Hi Mike,

I'm glad to hear you resolved the crash issue, that was a puzzler. As far as the performance of a like operation, it's going to depend a lot on the specific query being run, whether or not its doing a full table scan, etc. Here are some guidelines.

As a general rule, SQLCipher is almost as fast as standard SQLite for queries that use indexes because we are able to seek to and decrypt individual pages very quickly. Overhead for these types of queries can be as low as 5%.

On the other hand queries that do a full scan effectively need to decrypt every page for that table, which is very expensive. You should probably expect a 50% overhead for SQLCipher in these cases. You can use SQLite's EXPLAIN and EXPLAIN QUERY PLAN functions to gain insight into the types of operations a query will involve (http://www.sqlite.org/lang_explain.html).

Given the size of your database, one immediate suggestion would be to use a full text index instead of LIKE queries. 

http://groups.google.com/group/sqlcipher/browse_thread/thread/c8825361ff11136e/f34d04fa6281572d?lnk=gst&q=FTS#f34d04fa6281572d

Finally, if you are interested in gaining some additional insight into SQLCipher performance, I've just pushed a tool I wrote for internal benchmarking on SQLCipher. Its a simple iPhone app called SQLCipherSpeed that runs a number of query tests between an encrypted database and a standard sqlite database, providing a comparison of execution time and expected overhead between them for common query types. I'll try to write up a post about the performance details sometime soon, but in the mean time feel free to check it out.

http://github.com/sjlombardo/SQLCipherSpeed

Cheers,

Stephen

[MikeFranz]

Stephen

SQLCipherSpeed: Another nice project you've put together. It's already
helped by localize my main performance issues. Whilst I find the
encrypted DB to be 20% slower than non-encrypted in some cases, the
performance is still reasonable. With a little tactical de-normalizing
it's quite possible this will improve significantly too.

Cheers

Mike

On May 5, 6:43 am, Stephen Lombardo <sjlomba...@zetetic.net> wrote:
> Hi Mike,
>
> I'm glad to hear you resolved the crash issue, that was a puzzler. As far as
> the performance of a like operation, it's going to depend a lot on the
> specific query being run, whether or not its doing a full table scan, etc.
> Here are some guidelines.
>
> As a general rule, SQLCipher is almost as fast as standard SQLite for
> queries that use indexes because we are able to seek to and decrypt
> individual pages very quickly. Overhead for these types of queries can be as
> low as 5%.
>
> On the other hand queries that do a full scan effectively need to decrypt
> every page for that table, which is very expensive. You should probably
> expect a 50% overhead for SQLCipher in these cases. You can use SQLite's
> EXPLAIN and EXPLAIN QUERY PLAN functions to gain insight into the types of
> operations a query will involve (http://www.sqlite.org/lang_explain.html).
>
> Given the size of your database, one immediate suggestion would be to use a
> full text index instead of LIKE queries.
>

> http://groups.google.com/group/sqlcipher/browse_thread/thread/c882536...