4 ms·

The article may be unclear on this point: anti-virus was turned off for these tests. What that paragraph is pointing out is that performance tanks badly (roughl

The article may be unclear on this point: anti-virus was turned off for these tests. What that paragraph is pointing out is that performance tanks badly (roughly a factor of 10x) when you turn on Windows Defender, and presumably even worse with other anti-virus software. Credential: I was one of several people who vetted the article before it was published. I argued that Defender should be enabled, since it is a platform default. D. Richard Hipp chose to take the charitable path and not report those results alongside the others. The test is easily replicated yourself. The source code for it is part of the SQLite source tree, and command lines for building and running the test are given in the article.

Spooky23 9y ago
Thanks for the clarification. In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.
- wyoung2 9y ago
  My contention was that this article is about least-effort methods (i.e. fopen() vs default SQLite) so a lot of Windows SQLite users are going to have Defender (or heaven help them, something much worse) installed. Once you start getting into optimizations, much of this article starts to sway back and forth. Put enough effort into optimization, and you can outdo SQLite for a specific application, simply because SQLite isn't magic, it's just C. Well-written and highly-optimized C, but purpose-written code always has the potential to do better than general-purpose code. The point of SQLite in general and this report in specific is that you get a lot of performance, power, and safety for free with SQLite as opposed to `fopen()` and such. Good performance while antimalware interferes is just one way this manifests.
rusanu 9y ago
Can you clarify then wether the AV was simply not scanning the SQLite files (ie. the file extension or the file location was exempt in AV config) ?
- wyoung2 9y ago
  As I understand it, drh simply disabled Windows Defender entirely for these tests. SQLite gets an additional advantage with Defender enabled because Defender doesn't try to scan inside the SQLite DB file for the sub-files, whereas with separate files, Defender must at least look at each file's header to determine whether it's a potentially dangerous file type. While I was vetting this article, I did some of my own tests, and I found that simply excluding the directory containing the test tree was sufficient to get the same benefit. My point in other posts here is that you don't need to disable Defender to get this benefit when you use SQLite. But let's be clear: the 35% claimed performance benefit has nothing to do with Windows Defender. That's a wholly separate issue. Defender pays more attention to a pile of individual files than to an amalgamation of that same data into a single file, and this magnifies SQLite's advantages, but it is not the source of the advantage the linked report talks about.

Spooky23 9y ago

Thanks for the clarification. In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.

wyoung2 9y ago
My contention was that this article is about least-effort methods (i.e. fopen() vs default SQLite) so a lot of Windows SQLite users are going to have Defender (or heaven help them, something much worse) installed. Once you start getting into optimizations, much of this article starts to sway back and forth. Put enough effort into optimization, and you can outdo SQLite for a specific application, simply because SQLite isn't magic, it's just C. Well-written and highly-optimized C, but purpose-written code always has the potential to do better than general-purpose code. The point of SQLite in general and this report in specific is that you get a lot of performance, power, and safety for free with SQLite as opposed to `fopen()` and such. Good performance while antimalware interferes is just one way this manifests.

rusanu 9y ago

Can you clarify then wether the AV was simply not scanning the SQLite files (ie. the file extension or the file location was exempt in AV config) ?

wyoung2 9y ago
As I understand it, drh simply disabled Windows Defender entirely for these tests. SQLite gets an additional advantage with Defender enabled because Defender doesn't try to scan inside the SQLite DB file for the sub-files, whereas with separate files, Defender must at least look at each file's header to determine whether it's a potentially dangerous file type. While I was vetting this article, I did some of my own tests, and I found that simply excluding the directory containing the test tree was sufficient to get the same benefit. My point in other posts here is that you don't need to disable Defender to get this benefit when you use SQLite. But let's be clear: the 35% claimed performance benefit has nothing to do with Windows Defender. That's a wholly separate issue. Defender pays more attention to a pile of individual files than to an amalgamation of that same data into a single file, and this magnifies SQLite's advantages, but it is not the source of the advantage the linked report talks about.