System.Data.SQLite

Hello,

yes, i'm doing everthing inside a single transaction, with a single prepared command ;-)

I would like to insert ~1 000 000 rows in a classic SQLite file db, and everything works fine until about 100 000 rows (~5 000 row/sec). After that, it's horribly slow like 500 row/sec or less.

Any hint ?

Thanks for your help.

Without seeing your code and database schema, I couldn't venture a guess.

Ok, here is the code...

 i'm using NHibernate to generate the db Schema :

create table Foobar (
        ID TEXT not null,
        ... (~60 TEXT columns, ~10 NUMERIC and ~10 DATETIME columns, all nullable)
        primary key (ID)
    )

    

Now the C# code :   
    
        private IDbCommand _cmd;
        
        public void SQLiteInsertTest()
        {    
            using (IDbConnection cnx = NHibernateContext.SessionFactory.OpenSession().Connection)
            using (IDbTransaction tx = cnx.BeginTransaction())
            using (_cmd = cnx.CreateCommand())
            {    
                BuildInsertCmd(_cmd);
            
                ReadCsv("foobar.csv", MapRow);
                tx.Commit();
            }
        }
        
        private void MapRow(Dictionary<string, object> fields)
        {
            foreach (KeyValuePair<string, object> kvp in fields)
            {
                _paramMap[kvp.Key].Value = kvp.Value;
            }
            _cmd.ExecuteNonQuery();
        }
        

        private Dictionary<string, IDbDataParameter> _paramMap = new Dictionary<string, IDbDataParameter>();

        private static void BuildInsertCmd(IDbCommand cmd)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();

            sb.Append("insert into FOOBAR(");

            bool first = true;
            foreach (string column in PropertyMap.Keys)
            {
                if (!first)
                {
                    sb.Append(", ");
                    sbValues.Append(", ");
                }
                else
                {
                    first = false;
                }
                sb.Append(column);
                sbValues.AppendFormat(":{0}", column);
            }

            sb.AppendFormat(") values ({0})", sbValues.ToString());

            cmd.CommandText = sb.ToString();

            cmd.Parameters.Clear();
            foreach (NHibernate.Mapping.Property p in PropertyMap.Values)
            {
                IDbDataParameter param = cmd.CreateParameter();
                param.ParameterName = p.Name;
                param.DbType = GetDbType(p);
                cmd.Parameters.Add(param);
                _paramMap[p.Name] = param;
            }
        }

        private static DbType GetDbType(NHibernate.Mapping.Property p)
        {
            if (p.Type.ReturnedClass == typeof(decimal))
            {
                return DbType.Decimal;
            }
            else if (p.Type.ReturnedClass == typeof(DateTime))
            {
                return DbType.DateTime;
            }
            else
            {
                return DbType.String;
            }
        }
        
        private Dictionary<string, NHibernate.Mapping.Property> _propertyMap;
        
        // Property Map to retrieve property Name/Type
        private Dictionary<string, NHibernate.Mapping.Property> PropertyMap
        {
            get
            {
                if (_propertyMap == null)
                {
                    _propertyMap = new Dictionary<string, NHibernate.Mapping.Property>();

                    foreach (NHibernate.Mapping.Property p in NHibernateContext.Configuration.GetClassMapping("FOOBAR").PropertyIterator)
                    {
                        _propertyMap[p.Name] = p;
                    }

                    NHibernate.Mapping.Property idProp = NHibernateContext.Configuration.GetClassMapping("FOOBAR").IdentifierProperty;
                    if (idProp != null)
                    {
                        _propertyMap[idProp.Name] = idProp;
                    }
                }
                return _propertyMap;
            }
        }
        
        
        public void ReadCsv(string fileName, Action<Dictionary<string, object>> rowMapper)
        {
            TextFileDataSource source = new TextFileDataSource(fileName);
            using (IRecordReader reader = source.CreateReader())
            {
                DataHeader header = reader.ReadHeader();
                Dictionary<string, object> row = new Dictionary<string, object>(header.ColumnCount);
                
                while (reader.HasNext())
                {
                    rowMapper(reader.ReadData());                    
                }
            }
        }

I've also tested using "synchronous=off" and inserts are ok until 250 000 rows and still bad after.