SQLite is way faster than SQL Server

[Geert Bellekens]

I've been doing some performance tuning on one of my add-ins (EA Message Composer) and I noticed that a model in SQLite is a lot faster than a model in SQL Server (running on my local machine)

For one testcase I have a process that takes somewhere between 250 and 300 seconds in SQL Server, and only 100 seconds in SQLite.

Other things like applying user locks on a big part of the model also seem to be much much faster.

Anyone seeing the same results? Any ideas of what I could do to speed up my SQL Server?
Because of this, having a central database such as SQL Server comes with a serious performance penalty, making it a lot less attractive.

Geert

[wivel]

Hi Geert

Perhaps this can point you in the right direction? https://docs.microsoft.com/en-us/sql/relational-databases/performance/database-engine-tuning-advisor?view=sql-server-ver16

Here is what I did:

• Opened SQL Server Management Studio
• Started the Profiler (Tools -> SQL Server Profiler)
• Selected to save to a file
• Pressed the 'Run' button
• Played around with EA
• Stopped the profiler
• Opened the Database Engine Tuning adviser (Tools -> ...)
• Selected the trace file I just generated
• Sat 'Database for workload analysis' to my EA database
• Selected my EA database in 'Select databases and tables to tune' to my EA Database
• Pressed 'Start Analysis' and let it finish

Let it run and it will provide you with a list of suggestions to make the database perform better. I got a lot of Index suggestions on the small test I just made. To get a more accurate result, the profiler should run for a longer period under normal workload conditions.

Henrik

[Richard Freggi]

Hey Geert,
I'd be be curious to know if you have found a way to make the SQLite repository work in-memory ("file::memory:" parameter).
I don't know if EA supports this repository mode, and it would not be ACID until the database is saved to disk.  But potentially it could be a real ripper!

p.s. also PRAGMA journal_mode = off and PRAGMA synchronous = off may speed even further if EA can tolerate them.

[Geert Bellekens]

Thanks Henrik,

Will definitely try that.
The problem is that I don't want to update the standard EA database schema, so I'm specifically searching for other ways to improve the performance.
EA's database use is pretty specific. It executes lots of tiny queries.
Maybe there are some parameter we can tweak to make SQL server handle this type of load better.

Geert

[Geert Bellekens]

Hey Geert,
I'd be be curious to know if you have found a way to make the SQLite repository work in-memory ("file::memory:" parameter).
I don't know if EA supports this repository mode, and it would not be ACID until the database is saved to disk.  But potentially it could be a real ripper!

p.s. also PRAGMA journal_mode = off and PRAGMA synchronous = off may speed even further if EA can tolerate them.

I haven't tried anything on the SQLite side, but I can understand why this could potentially be interesting.

Geert

[wivel]

Thanks Henrik,

Will definitely try that.
The problem is that I don't want to update the standard EA database schema, so I'm specifically searching for other ways to improve the performance.
EA's database use is pretty specific. It executes lots of tiny queries.
Maybe there are some parameter we can tweak to make SQL server handle this type of load better.

Geert

I fully understand. I didn't pull the trigger either for the same reasons. I'm not that well versed in what the MS SQL tools can do, so perhaps it can come up with suggestions that does not touch tables, indexes and the like.

Henrik