Performance decrease over-time using SELECT
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.
You didn't mention what programming language and interface library you are using to access SQLite. My initial guess (based on very little information) is that the problem is in the interface to your programming language of choice, and not in SQLite itself.
Can you provide complete code so that we can reproduce your problem and hence chase it to ground?