Quote:
Originally Posted by z.nina
@[I've set the environment variable on my main PC (many many thanks for the detailed explanation !!!!) and the problem is solved   |
OK, to me that implies the fundamental problem is sqlite3 transaction handling. As I mentioned earlier, on windows a transaction consists of a file creation, some writes, then a file deletion. These are "expensive" operations.
NTFS is a complex journaled file system that operates a bit like a database (see
http://www.ntfs.com/ntfs.htm for a lot of info). Creating a file requires several read and write operations. Most of the read operations can come from the disk cache. Which leads me to speculate ...
Glancing through this thread I didn't see how much memory your computer has. A 2TB disc will have an enormous amount of control space (file allocation tables, journals, open file tables, etc) that for performance reasons should always be in the cache. Unfortunately, if the computer doesn't have enough memory, the cache will be too small and performance will suffer. My guess is that with a disk that size, you would want at least 4GB of RAM so your cache can be large enough. More would be better. Do you have that much?
One reason moving the database to a smaller drive would improve performance is that more of that drive's control space could fit in the cache. The fact that you are seeing a performance improvement supports my argument.