Performance problems with the CShapr-SQLite library

[Mike]

I've searched around for discussions and issues about this but have
not found anything yet.

I have recently started using CSharp-SQLite and I like it. I am having
trouble with performance, though, when executing queries that have
JOIN or ORDER BY calls. When I ran a query in my C# program I got a
reported time of about 0.1 sec, but the same query ran for the
original library in 0.003 sec, which is 30 times faster.

Now this query was a simple SELECT query over about 450 records with
an ORDER BY at the end ordering on one column. I have all my necessary
indexes set along with Primary and Foreign keys, but it does not seem
to make a difference. This kills me when doing these queries on tables
with tens of thousands of records.

I have compiled from source, so there may be a compiler directive I'm
missing or something, I'm not sure. Just figured I'd start a
discussion about it. If anyone knows of a solution please direct me.

Thanks.

[Noah Hart]

What are your compiler options?

Is this a Debug or Release configuration?

Noah

[Mike]

I forgot to mention that this is for the ADO SQLiteClient, if that
gives you any better idea.

This is a release version of the SQLiteClient and CsharpSqlite
projects.

TRUE
WIN32
_MSC_VER
NDEBUG
NO_TCL
SQLITE_ASCII
SQLITE_DISABLE_LFS
SQLITE_ENABLE_OVERSIZE_CELL_CHECK
SQLITE_MUTEX_OMIT
SQLITE_OMIT_AUTHORIZATION
SQLITE_OMIT_DEPRECATED
SQLITE_OMIT_GET_TABLE
SQLITE_OMIT_INCRBLOB
SQLITE_OMIT_LOOKASIDE
SQLITE_OMIT_SHARED_CACHE
SQLITE_OMIT_UTF16
SQLITE_OMIT_VIRTUALTABLE
SQLITE_OMIT_WAL
SQLITE_OS_WIN
SQLITE_SYSTEM_MALLOC
VDBE_PROFILE_OFF
SQLITE_HAS_CODEC

I admit I don't know what most of these are for as I didn't really
have the time to investigate fully, so you may look and see a glaring
issue. I compiled it and ran what worked, which may be part of my
problem.

Anyway, I appreciate the help.

Mike

[noah hart]

Remove the SQLITE_ENABLE_OVERSIZE_CELL_CHECK; This is a very intensive
DEBUG flag
I'll change it to be incompatible with NDEBUG

http://code.google.com/p/csharp-sqlite/wiki/CompilerOptions

Let me know if that makes any difference


Noah

[Mike]

That helped a lot. I did an inner join query and am getting a time of
about 0.01 sec in the original library and a time of about 0.04 sec in
CSharpSQLite (SQLiteClient).

Thanks again for the help.

Mike

[fastprom...@gmail.com]

How you do that?

[Stewart]

Assuming that you are compiling with Visual Studio, in the solution panel right-click on the project and select Properties in the context menu.  In the properties form that opens, select the Build tab and then look for the text "Conditional compilation symbols". You need to delete the one you don't want from there. Finally, rebuild the project/solution.

Hope that helps,

Stewart.

On Sunday, 14 October 2012 22:58:52 UTC+1, fastprom...@gmail.com wrote:

How you do that?

[fastprom...@gmail.com]

Thanks for replay! But I have empty input near text "Conditional compilation symbols".

[fastprom...@gmail.com]

http://screenshooter.net/1533411/ndblsca