SQLite Forum

SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes
Login
> I decided to run the tests. I get pretty much identical results 

Until you wrote that, I was under the impression that we needed the SQLite DB file with the schema built in order to see the problem. I didn't see that this program *created* the schema.

Yes, above, Ted said this program created the DB, but I took that to be "created" in the trivial sense: SQLite will create a new empty DB file by default if it doesn't already exist. Then there's all that back-and-forth about whether the problem occurs with an "empty" DB, which confused the issue. I don't consider the `TestDatabase.sql` file resulting from running this program to be "empty": it has a large schema resulting in a DB that's 212 kB even after VACUUMing with `page_size=512`. That's not "empty" to my way of thinking.

With the confusion swept aside, I tried it here on my Mac with the latest dotnet packaged for Homebrew, and I see the symptom also. Repro steps:

```shell
brew install dotnet
dotnet new console -lang=vb -n=many-index-test
cd many-index-test
pbpaste > Program.vb         # save VB.net code above from clipboard
dotnet add package System.Data.SQLite
dotnet build
dotnet run
```

Same speed profile as originally posted.

I apologize for the confusion. However, I think it should stand as a lesson that providing the above reproduction command sequence would've avoided a lot of the confusion up-front.

This also shows that my original guess was correct: the problem *isn't* in SQLite proper, it's in the SQLite-specific .NET interface class.

Further proof:

```shell
$ time sqlite3 TestDatabase.sdb 'select * from TestTable'

real	0m0.013s
user	0m0.008s
sys	0m0.004s
```

That is, opening the resulting SQLite DB in the shell and executing a trivial query is fast, regardless of the ridiculous schema. This shows it's the .NET code doing something suboptimal, not SQLite.

I've temporarily uploaded a copy of the resulting DB [here][1] for those who want to try other experiments without going through the dotnet steps, though I'm not sure what you can learn, given that the problem seems to be in System.Data.SQLite.

[1]: https://drive.google.com/file/d/1wjBuc8VDN0d4MZh0ULPIsV9EygzCO139/view?usp=sharing