SQLite Analyze (sqlite_stat4). System.Data.SQLite vs Microsoft.Data.Sqlite

Question

I am trying to execute ANALYZE command for Sqlite. But it works only for System.Data.SQLite. It does not work for Microsoft.Data.Sqlite version, no errors, no exceptions, just nothing happens.

When you execute ANALYZE - new system table will be created sqlite_stat4. It helps SQLite to optimize the query plan. https://www.sqlite.org/lang_analyze.html

How to make it work for EntityFramework/Microsoft.Data.Sqlite ?

    // .NET 6
    public class Program
    {
        public static void Main(string[] args)
        {
            SystemDataSQLite();
            MicrosoftDataSqlite();

            Console.WriteLine("End...");
            Console.ReadLine();
        }
        
        private static void SystemDataSQLite()
        {
            // System.Data.SQLite version 1.0.118
            string connectionString = PrepareNewDbConnectionString("SystemDataSQLite.db");
            using SQLiteConnection connection = new SQLiteConnection(connectionString);
            connection.Open();
            using var command = connection.CreateCommand();
            command.CommandText = "ANALYZE";
            command.ExecuteNonQuery();
        }

        private static void MicrosoftDataSqlite()
        {
            // Microsoft.Data.Sqlite version 6.0.25
            string connectionString = PrepareNewDbConnectionString("MicrosoftDataSqlite.db");
            using SqliteConnection connection = new SqliteConnection(connectionString);
            connection.Open();
            using var command = connection.CreateCommand();
            command.CommandText = "ANALYZE";
            command.ExecuteNonQuery();
        }
        
        private static string PrepareNewDbConnectionString(string dbFile)
        {
            string baseDir = AppDomain.CurrentDomain.BaseDirectory;
            var dir = Directory.CreateDirectory(baseDir + "db");
            string filePath = dir.FullName + "\\" + dbFile;
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            return $@"Data Source={filePath};";
        }
    }

I create example with System.Data.SQLite and Microsoft.Data.Sqlite.

Answer 1

The sqlite_stat4 table is only created by ANALYZE and used by the query planner if the version of sqlite being used was compiled with the SQLITE_ENABLE_STAT4 option defined. It defaults to not being enabled.

The two C# libraries you're testing likely include two different versions of sqlite built with different options.

You should still be seeing a sqlite_stat1 table after running ANALYZE with both; it just has less detailed statistics about table contents than you get with stat4.