System.Data.SQLite

The performance of the GetSchema call has dropped considerably somewhere between version 1.0.47.2 and 1.0.60.0. In our case, the call to SQLiteConnection.GetSchema("Columns") went up from about 250ms to about 3500ms. We tested the six most recent releases (1.0.60.0 - 1.0.65.0) and all gave bad performance. We couldn't find older versions, so we don't know where exactly the performance dropped. The version that we use now is 1.0.47.2.

I'm currently testing SQLite ADO.Net Provider (1.0.65.0) and I also noticed the same poor performance on the GetSchema method.

Well the database I'm currently testing is pretty huge (2600 tables), but I was wondering if there could be any way to improve the GetSchema speed.

In my case, a GetSchema(columns) on one table takes between about 15s. Same for the SQLiteDataReader.GetSchemaTable method.

 

Alexandre

 

There were a lot of updates to schema fetching functions between the 47 and 65 releases, most of them centered around accuracy of information.  SQLite's schema querying functionality is really poor and it's hard to parse out all of it and wedge it into ADO.NET's concepts of schema.

Can you send me an empty database with your schemas so I can profile the functions?  e-mail to robert at blackcastlesoft dot com.

Be sure and post a reply here when you've sent it -- my spamfilter is particularly brutal.

 

Thanks for your reply! I sent you a file containing our database schema. The subject of the e-mail is "Empty database for profiling GetSchema".

I can confirm that it's taking about 2.5 seconds on my computer to read the schema.  Part of the problem is that I have to do a lot of heavy lifting in SQLiteDataReader.GetSchemaTable() to get the default value(s) for a column, as well as the "isunique" property.

I'll see if I can make some optimizations  there to speed it up a little.  I never really profiled the schema functions for performance, because I never really anticipated people needing to call that code very often.

 

I've further narrowed it down to the necessity of ascertaining the "UNIQUE" flag on a column in SQLiteDataReader.GetSchemaTable().  I need this flag to deliver accurate metadata to O/R mappers, and its a huge pain in the butt to figure out.

If you tell me what your needs are for getting the column information, maybe I can help another way.  There's a lot of ways to skin that cat.

 

We use SQLite databases as our file format. Different versions of our application have different tables and columns. For backward compatibility, we need to inspect the schema of the database to convert older formats to the newest one. We need only table and column names to do this, so a full GetSchema() call seems unnecessary in our case. Is there a faster way to get this information?

Yes, there is one: 

public static DataTable GetColumns(SQLiteConnection cn)
{
    using (SQLiteCommand tablesCmd = new SQLiteCommand("select name from sqlite_master where type='table' or type='view'", cn))
    {
        DataTable columnsDataTable = new DataTable();
        columnsDataTable.Columns.Add("TableName", typeof(string));
        columnsDataTable.Columns.Add("ColumnName", typeof(string));
        columnsDataTable.BeginLoadData();
        using (IDataReader reader = tablesCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                string tableName = reader.GetString(0);
                using (SQLiteCommand selectCmd = new SQLiteCommand("select * from " + tableName, cn))
                {
                    using (IDataReader selectReader = selectCmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        for (int index = 0; index < selectReader.FieldCount; index++)
                        {
                            DataRow row = columnsDataTable.NewRow();
                            row["TableName"] = tableName;
                            row["ColumnName"] = selectReader.GetName(index);
                            columnsDataTable.Rows.Add(row);
                        }
                    }
                }

            }
        }
        columnsDataTable.EndLoadData();
        columnsDataTable.AcceptChanges();
        return columnsDataTable;
    }
}

It is much faster than SQLiteConnection.GetSchema("Columns"). I tested it using a modified version of Northwind database, and these are the results:

• SQLiteConnection.GetSchema("Columns"): 60 ms
• GetColumns(SQLiteConnection cn): 2.7 ms

 

Thanks SqlRanger, the method you supplied works like a charm!