SQLite Forum

Query's first run, slow...
Login

Query's first run, slow...

(1) By jose isaias cabrera (jicman) on 2021-01-05 14:32:08 [link] [source]

Greetings!

When I run a query, most times, the first time I run it, it runs slow. But, after running the same query again, it's really fast. I know that perhaps this has been said before here, and I may have even read it, but I can't remember why. For example,

SELECT ProjID, CID, Project_Name, Start_date,
       Finish_Date, Target_Go_Live_Date, 
       BL_Start,BL_Finish, Ann_CapexP, Tot_CapexP, 
       Progress, Status, Project_Manager       
       FROM Project_List a WHERE 
  a.PMO_Board_Report != 'No' 
AND
(
   (
     a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15'
   )
   OR
   (
     a.Target_Go_Live_Date = '' AND a.Finish_Date >  substr(date('now'),1,4) || '-01-15'
   )
)
AND
  a.InsertDate = 
  (
    SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID
  )
group BY a.ProjID
;

timer result is:
Run Time: real 4.195 user 0.125000 sys 0.750000

If I run this right back, this is the timer result:
Run Time: real 0.834 user 0.093750 sys 0.218750

and again,
Run Time: real 0.764 user 0.140625 sys 0.156250

once more,
Run Time: real 0.721 user 0.125000 sys 0.203125

Why is that again? Thanks.

(2) By Gunter Hick (gunter_hick) on 2021-01-05 14:36:44 in reply to 1 [link] [source]

This is usually due to the operating system keeping recently used disk blocks in memory and/or SQLite doing the same with recently used pages from the database file.

(3) By jose isaias cabrera (jicman) on 2021-01-05 16:06:38 in reply to 2 [source]

Thanks.