SQLite Forum

SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes
Login
Hello Larry,


don't blame the schema design. I know, that it is bad and I would never create such a column monster. I inherited 20 years of work of my predecessor and haven't a chance, to change that on a fast way ... so currently I have to live with it.


To your question:

The 40 seconds are consumed in the call to SQLiteCommandBuilder.GetInsertCommand (respectively the first call to any GetXXXCommand of the CommandBuilder). So ... if I call the **SQLite**CommandBuilder method ... I am pretty sure, that the SQLite dotNet-Library is involved in that call. Would be very strange if not. ^^
Also ... if I open the database before this call a second time exclusive then the call will result in a "database locked" exception. I would say, that this supports my assumption.

I know, that SQLiteCommandBuilder inherits from Microsofts Data.Common.DbCommandBuilder and so it may be, that there is an awful entanglement with the Microsoft code. Anyway ... all this doesn't happen with MSSql, MySql, Oracle or Access.

Also note the parallelism (parallel NOT exponential) between time measurements and the number of indexes and columns. It seems not to be a question of "optimization for 900+ columns". It seems to be a performance problem small enough to be not noticed before (compared with the operations that follow normally).