Hello!
I have a program that does some math in an SQL query. There are
hundreds of thousands rows (some device measurements) in an SQLite
table, and using this query, the application breaks these measurements
into groups of, for example, 10000 records, and calculates the average
for each group. Then it returns the average value for each of these
groups.
The query looks like this:
SELECT strftime('%s',Min(Stamp)) AS DateTimeStamp,
AVG(P) AS MeasuredValue,
((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) AS SubIntervalNumber
FROM LogValues
WHERE ((DeviceID=1) AND (Stamp >= datetime(1334580095, 'unixepoch')) AND
(Stamp <= datetime(1336504574, 'unixepoch')))
GROUP BY ((100 * (strftime('%s', [Stamp]) - 1334580095)) /
(1336504574 - 1334580095)) ORDER BY MIN(Stamp)
The numbers in this request are substituted by my application with
some values. I don't know if i can optimize this request more (if
anyone could help me to do so, i'd really appreciate)..
This SQL query can be executed using an SQLite command line shell
(sqlite3.exe). On my Intel Core i5 machine it takes 4 seconds to
complete (there are 100000 records in the database that are being
processed).
Now, if i write a C program, using sqlite.h C interface, I am waiting
for 14 seconds for exactly the same query to complete. This C program
"waits" during these 14 seconds on the first sqlite3_step() function
call (any following sqlite3_step() calls are executed immediately).
>From the Sqlite download page I have downloaded SQLite command line
shell's source code and build it using Visual Studio 2008. I ran it
and executed the query. Again 14 seconds.
So why does a prebuilt, downloaded from the sqlite website, command
line tool takes only 4 seconds, while the same tool, built by me,
takes 4 times longer time to execute?
I am running Windows 64 bit. The prebuilt tool is an x86 process. It
also does not seem to be multicore optimized - in a Task Manager,
during query execution, I can see only one core busy, for both
built-by-mine and prebuilt SQLite shells.
I have tried different Visual Studio's optimization options, tried to
match "Pragma compile_options;" output by defining preprocessor
directives in sqlite3.c file to output generated by downloaded
sqlite3.exe file. To no avail.
Any way I could make my C program execute this query as fast as the
prebuilt command line tool does it?
Thanks much!
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
