[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
