SQLite Forum

Performance decrease over-time using SELECT
Login
Greetings, 

I am using the latest release of SQLITE3 as of this writing ( Version 3.34.0 ).

After inserting 60,000 records into the following table (for test purposes):

CREATE TABLE PlayerConnections
(
	TargetPlayerId INTEGER NOT NULL,
	SourcePlayerId INTEGER NOT NULL
);

When attempting to execute the following same exact query 60,000 times in a simple for() loop: 

"SELECT 1 FROM PlayerConnections WHERE (TargetPlayerId = 20000 AND SourcePlayerId = 1)"

Executing this loop takes 60-80 seconds to perform such a simple query
The interesting thing is that the first queries are executed quickly, as expected, 500-1000 queries take <1ms. 

By the time the loop reaches the 50000th iteration, approximately 1 in 3 queries cost 15ms to execute, sometimes 30ms, and sometimes 45ms resulting in a serious performance decrease over-all.