CODEWITHSUNDEEP
c++performancesqlite
statguy
statguy

Reputation: 1717

How can I speed up loading from a sqlite database? (sqlite3_step is very slow)

I have maybe about 1,000,000 rows to load into C++ objects (through about 10,000 SELECTS). I've profiled the load, and note that the sqlite3_step statement here is the bottleneck.

sqlite3_stmt *stmt;
std::string symbol = stock->getSymbol();
boost::format sql("SELECT date,open,high,low,close,volume FROM Prices WHERE symbol=\"%s\" ORDER BY date DESC");
sql % symbol;

if (sqlite3_prepare_v2(databaseHandle_, sql.str().c_str(), -1, &stmt, NULL) == SQLITE_OK) {
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int date = sqlite3_column_int(stmt, 0);
        float open = sqlite3_column_double(stmt, 1);
        float high = sqlite3_column_double(stmt, 2);
        float low = sqlite3_column_double(stmt, 3);
        float close = sqlite3_column_double(stmt, 4);
        int volume = sqlite3_column_int(stmt, 5);
        Price *price = new Price(new Date(date), open, close, low, high, volume);
        stock->add(price);
    }
} else {
    std::cout << "Error loading stock" << std::endl;
}

I am using the amagalmated sqlite.h/c version 3.15.0. Any ideas how I can speed up performance?

More info:

CREATE TABLE Prices (symbol VARCHAR(10) NOT NULL, date INT(11) NOT NULL, open DECIMAL(6,2) NOT NULL, high DECIMAL(6,2) NOT NULL,low DECIMAL(6,2) NOT NULL, close DECIMAL(6,2) NOT NULL, volume INT(10) NOT NULL, PRIMARY KEY (symbol, date))

CREATE INDEX `PricesIndex` ON `Prices` (`symbol` ,`date` DESC)

EXPLAIN QUERY PLAN SELECT * FROM Prices WHERE symbol="TSLA" ORDER BY date DESC;

returns

SEARCH TABLE PRICES USING INDEX PricesIndex (symbol=?)

Further Note: Such SELECTs as shown above take 2ms in SQLite Browser for Mac Execute SQL.

Upvotes: 2

Views: 2200

Answers (1)

CL.
CL.

Reputation: 180070

Your index already speeds up searching for matching rows, and returns them in the correct order so that no separate sorting step is required.

However, the database still has to look up the corresponding table row for each index entry. You can speed up this particular query by creating a covering index on all the used columns:

CREATE INDEX p ON Prices(symbol, date, open, high, low, close, volume);

But instead of duplicating all data in the index, it would be a better idea to make this table a clustered index:

CREATE TABLE Prices (
    symbol VARCHAR(10) NOT NULL,
    date INT(11) NOT NULL,
    open DECIMAL(6,2) NOT NULL,
    high DECIMAL(6,2) NOT NULL,
    low DECIMAL(6,2) NOT NULL,
    close DECIMAL(6,2) NOT NULL,
    volume INT(10) NOT NULL,
    PRIMARY KEY (symbol, date)
) WITHOUT ROWID;

Upvotes: 2

Related Questions