Sign in to follow this  
Followers 0
Zouflain

[SQLite3] Major bottleneck at sqlite3_step, sqlite3_exec much faster - why?

2 posts in this topic

Clearly I'm doing something incorrectly, because the overwhelming consensus is that sqlite3_prepare is considerably faster than sqlite3_exec, but I'm experiencing an extreme bottleneck at the sqlite3_step function.
int err;
std::string sqlstr = std::string("REPLACE INTO `Entities` VALUES(")+std::to_string(id)
			+std::string(",")+std::to_string(kind)+std::string(",")
			+std::to_string(nibble)+std::string(",")+entityStr;
err = sqlite3_exec(SQLINTERFACE.getDatabase(),sqlstr.c_str(),nullptr,nullptr,nullptr);
//...error checking code...
Runs about 50~100x faster than
//ST_ENTITY_EXPORT: "REPLACE INTO `Entities` VALUES(?,?,?,?);"
sqlite3_stmt* statement = SQLINTERFACE.getStatement(Interface::ST_ENTITY_EXPORT);
	sqlite3* database = SQLINTERFACE.getDatabase();
err = sqlite3_bind_int64(statement,1,id);
genericSQLError(statement,database,err);//error checking code
err = sqlite3_bind_int64(statement,2,kind);
genericSQLError(statement,database,err);
err = sqlite3_bind_int64(statement,3,nibble);
genericSQLError(statement,database,err);
err = sqlite3_bind_text(statement,4,entityStr.c_str(),entityStr.size(),SQLITE_TRANSIENT);
genericSQLError(statement,database,err);
err = sqlite3_step(statement);
if(err!=SQLITE_DONE){
	genericSQLError(statement,database,err);
}
sqlite3_reset(statement);
I've copied it verbatim, so there's a few "blackbox" functons, but they aren't responsible for the slowdown. genericSQLError does not alter parameters, or anything referenced by them - it's a utility function for error checking. The code bottlenecks at sqlite3_step, despite sqlite3_exec recieving a directly equivalent statement. I'd appreciate any help finding a possible cause for the bottleneck. P.S> Yes, all of this is in a transaction.
0

Share this post


Link to post
Share on other sites
Thanks for the help! ...but like I said in the PS, this is all one transaction (ie all in one BEGIN/END). Even if it wasn't, that wouldn't explain why sqlite3_exec is faster than sqlite3_step - in both cases, not framing it in a transaction would cause every insert to have its own transaction. I'd appreciate any other ideas, though.
0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0