Because locking over the network I assume, try opening with an exclusive lock

Are you talking about Amazon Elastic File System or NTFS Encrypted File System ? Sorry for the basic question, but you'd be amazed at the posts we get here.

What API are you using to make SQLite calls ? Is are you using the C API from your C program or using a library from another language ?

Assuming that it's NTFS, can you spin up process manager and tell us whether you can track the extra time to a process ? Is that process one which is part of your program, or one which is part of the file system ?

I'm taking about Amazon EFS.

Also I'm using SQLite with Java with the help of xerial JDBC driver

https://github.com/xerial/sqlite-jdbc

Thanks. I'm betting a lot of previous answers thought you were using the Encrypted File System on a local drive.

You won't get a speedup through caching. It's a networking file protocol. Nothing can be cached because the data on the source drive may have changed, and the only way to tell it hasn't been changed is to read from the source drive (across the network) again.

Technically, SQLite is saving time by not reading all the data again. But the time taken to do any network access takes most of the time. The difference between reading 5 bytes and 500 bytes is not big.

That's why an exclusive lock will help in this case. SQLite cannot safely assume the internal page cache is up to date without constantly checking for this unless it has exclusive access to the file.

Sorry if I'm missing any basics, but I didn't understand this part from your answer

Technically, SQLite is saving time by not reading all the data again

If SQLite is not caching how above line will be true?

There's a data version number in the database file header. You can access it yourself using this:

https://www.sqlite.com/pragma.html#pragma_data_version

So SQLite may be reading just that integer across the network to see whether it needs to reread the actual data.

However, most of the time taken for sending data across a network is used no matter how much data is sent (within reason). It's involved in making sure the right computers are talking, and that they both understand what they're talking about. The increase in time taken because they send/receive 500 bytes instead of 5 bytes is small in comparison with the time it takes to create/use/stop the session.

Sorry if I'm wrong, slightly confused here

So can we say that SQLite is caching data but still accessing EFS(through network ) to read data_version number and if data_version is same SQLite is using cache data?

Accessing the network takes 5 seconds, no matter how much data you are reading.

The first query has to read a pooper-scooper full of data -- say 5 Gigabytes. Accessing the network takes 5 seconds. Data transfer takes a few billiseconds. Total elapsed time: 5 seconds.

The second query has to read the "data_version" to see if it can use its cached data or not. Accessing the network takes 5 seconds. Four bytes are transferred (the data_version) in a few billiseconds. THe data version is unchanged, therefore the rest of the query can be serviced from the in-process cache. Total elapsed time: 5 seconds.

I don't know how to make it more clear.

Thank you 🙌, now it's clear