Hi Brandon,
Brandon Whalen wrote:
I'm currently trying to use sqlite to manage a database in a c program. I have 4 tables where each table has at most 6 columns. I've found that if I use a select statement and run that statement through a callback function that I get incredibly slow response times. I've found that the select statement itself happens rather fast, but its taking the selected data and sending it to my callbacks that is taking the most time, specifically I've found from gprof:
Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls ms/call ms/call name
22.90 1592.40 1592.40 1649956995 0.00 0.00 getPayload
21.97 3120.02 1527.62 23262 65.67 272.85 sqlite3VdbeExec
Is this common in sqlite? I've tested my sql statement on the command line and its rather fast, but I've also found that if I run a built in function(count) on the results that it too suffers from a severe slowdown in performance.
What you call "the select statement itself" is, I think, just compiling the statement to a program in the virtual machine language. This is very fast.
When using built-in functions (such as "count"), SQLite often has to do a full table scan, which can take a long time, since it can't use an index.
The getPayload function is defined in btree.c, and is the function that takes values and keys out of the B-Tree. As you can see, it is called a little over 1.6 billion times, which suggests that you have a lot of rows (maybe on the order of 1.6E09/24 ~ 69 million (since you have at most 24 columns). That is a lot of rows, and may induce "poor" performance, regardless of whether you are using SQLite or MySQL, PostgreSQL or any of the other big databases. You should check that indices are used (using the EXPLAIN statement) whenever possible.
HTH
Ulrik P.
-- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
