I have a VB6 application that uses an MS Access local database.
In the app, I execute the following open recordset command - each time it runs it gets new parameters and returns ~2000 records.
The first time it runs is very fast (1-2 seconds), but when I change the passed in numbers and re-run, it takes almost 25 seconds.
1st run - fast!
SELECT DISTINCT G_Id FROM myTable WHERE H_ID IN (18705,3211,17401,11007,31109,7667,17407,20029,18279,14999,22138,17494,6859,33480,15584)
subsequent runs - slow!!
SELECT DISTINCT G_Id FROM myTable WHERE H_ID IN (25006,17460,8761,29293,14302,7074,29289,12997,10005,32576,3226,31235,31518,7194)
* verifying recordset object is .closed, set = nothing
* closing and reopening the database object between runs
* setting recordset as a global level and not closing/setting to nothing & leaving database open
* Using recordset type options of dbOpenDynaset, dbOpenSnapShot; both with and without dbReadOnly.
Nothing changes the subsequent run delay.