Derrell Thanks for that!
I did not have the primary key set up right. (I'm an SQL newbie) which
forced me to use "order by."
By the way, "ORDER BY 1" I believe to mean order by column id = 1. So it's
not really a meaningless statement.
I think any "order by clause" is going to cause a per row memory
allocation.
Matt
[EMAIL PROTECTED]
wiredUniverse.com
To
01/10/2005 11:24 sqlite-users@sqlite.org
AM cc
Subject
Please respond to Re: [sqlite] excessive malloc()
[EMAIL PROTECTED] calls
te.org
[EMAIL PROTECTED] writes:
> I switched my application over to SQLite3 and did some performance
> profiling and found that the majority of the processing time spent is
> making calls to malloc().
>
> sqlite3_step() is the function that is making all the excessive calls,
one
> call per row fetched.
>
> The program is a stock scanning / data mining program. It keeps about 6-7
> years worth of daily stock price data for a company in a table, and I
have
> about 3000 of these tables. One complete scan of all 3000 companies will
> make roughly 5.5 million malloc() calls!
>
> I create each table using SQL command:
> CREATE TABLE 'SPY' (key INTEGER PRIMARYKEY UNIQUE, open FLOAT, low FLOAT,
> high FLOAT, close FLOAT, volume FLOAT)
Firstly, if "key" is your primary key, you need not specify UNIQUE because
primary keys are, by definition, unique. Secondly, I don't know if sqlite
understands "PRIMARYKEY". The more standard way of specifying that is with
a
space in it: "PRIMARY KEY". You can confirm whether sqlite understood this
to
be your primary key field by doing the following from the sqlite shell:
.mode line
pragma table_info(SPY);
If the "pk" field shows as '1', then it knows it's a primary key; otherwise
it
does not.
> When I read a table I use the following SQL select statement.
> SELECT * FROM 'SPY' ORDER BY 1 ASC
"ORDER BY 1" is probably causing an additional calculation for each row, in
order to sort by whatever that means (which is probably nothing). If you
discard the ORDER BY clause, the results will be in primary key order. You
could be explicit about it by saying "ORDER BY key" (with or without the
ASC
indication, since that's the default).
> I debugged / stepped in to the step() code and noticed that it is the op
> code COLUMN making the memory allocation. I think it may be my ORDER BY
> clause that is causing it to take the path to the malloc() call.
Without looking in more detail at the code, that would be a reasonable
supposition. It probably allocates memory during each step() call, to
contain
the field which will be sorted by. I suspect that in your query, it's
allocating space to put the constant value "1"... a not very useful piece
of
information to be allocating for and saving.
Cheers,
Derrell
