SQLite Forum

Runtime of and opcode counts of ANALYZE
Login
Platform: Windows, SQLite 3.31.0, 64 Bit

## What I do

My software uses SQLite for a long time to manage databases of all sizes.

One feature in my software is a *diagnosis routine* which performs tons of logical checks on the data stored in the SQLite tables. And in a final step, the diagnosis code runs these two SQLite commands:

``PRAGMA integrity_check(100);``

followed by;

``ANALYZE;``

to let SQLite check the consistency of the database.

## When it works correctly

My software runs the diagnosis in a background thread while showing a progress dialog to the user. To enable the user to abort the lengthy integrity_check / ANALYZE, I set a **callback** using ``sqlite3_progress_handler()`` with N=50,000 opcodes.

For smaller databases, these two commands take a few seconds. For larger database, they can take several minutes.

For example, a database with **20 GB** on disk needs about **7 minutes** to complete and the callback function is called **28,000** times. Which is fine.

## The Problem

A customer reported that this step does never finish on his PC. He sent me the database today. It has **18 GB** and I had to abort the ANALYZE step after waiting for more than one hour.

According to Windows Task Manager, the ''bytes read / bytes written'' did no longer increase. Or super-slow. ~ 5% CPU load for the process, on a 12 core system). It looked like SQLite was running in a tight loop or something.
 
After about one hour, the callback function reported a call count of whooping **971,855,609,341** calls! So, this seemed to be the problem.

## Solution / Work-around

When I no longer set a callback function, the two functions take about 22 minutes to complete. Still a lot slower, but at least they finish successfully. On the other database, the time for the two steps stays **roughly the same**, with or without callback function set.

## My Question

Is there anything I can do to figure out why ANALYZE would take such a massive amount of time and produce such a massive amount of calls to the callback function for this particular database? I have never experienced something similar, and I use SQLite for many years.