1

I have developed an application win QT which uses SQLIte database. The copy of database is located on each site.

On one site let's say site 'BOB1' it works perfectly without any problem. But when we try to use it on another site lets say 'BOB2' it takes long time to open a database connection(approx 2000 milliseconds).

I thought that perhaps there is a network problem, So they tried to use the server of the site 'BOB1' as their server, which works fine. But when i tried to use the server of the site 'BOB2' from the site 'BOB1', I have the same problem. So i thought it may not be the network issue.

Another thing that came to my mind was that, perhaps there is a problem of DNS resolution. But when i tried to ping the server using IP and hostname, the response time is the same.

Any idea or pointer that what can be the problem.

PS: Server + database file path is specified in the setDatabasePath() fuinction using enviornment variables.

user1703942
  • 317
  • 3
  • 15
  • What do you mean by server? SQLite is a server-less database, it's just a file which you open from the drive (using SQL driver). – Googie Oct 01 '14 at 12:57
  • I mean to say, the The SQLite file is placed on a server at each site – user1703942 Oct 01 '14 at 13:01
  • But you said you specify _Server + database file_ in the setDaatabasePath() - so do you specify only a file, or do you specify some server here as well? If server too, then what server? – Googie Oct 01 '14 at 13:08
  • I think may be I did not state clearly. I meant to say that i specify the complete file path. e.g. "\\Servername\somedirectory\Sqlitefile". – user1703942 Oct 01 '14 at 13:12
  • So to be clear - the file is on the Microsoft shared network drive. This is actually quiet common issue with SQLite. Using it over network drives tends to be slow. It's usually not recommended. See for yourself, look up in the internet for "sqlite network drive". – Googie Oct 01 '14 at 13:20
  • I see i try to look for that. Just another question. What can be the disadvantages, if i keep my database open all the time, and close it only when my application will be closed. At the moment, what i was doing that, after connecting the database and performing operation. i was closing it instantly. PS: more than one people can access the same database file at the same time. – user1703942 Oct 01 '14 at 13:28
  • You don't need to close it. Important thing is that you don't hold any uncommited transaction, because this means (in most cases) some locks being set for some tables. In this case other client would not be able to modify such tables (and in extreme situations even read tables). Other than that you should be fine. Anyway, if you have a requirement of sharing database, you should consider migrating with your application to the proper clicnt-server solution, like PostgreSQL, MySQL, or other database. – Googie Oct 01 '14 at 14:03
  • My application will only be reading data from the database, so i guess it will not be any problem. The reason i chose SQLite was that, it is free and does not need any installation. Can you suggest any other database, which does not need any installation and is free. – user1703942 Oct 02 '14 at 07:53
  • No, I don't know any other. I'd suggest to not store SQLite database on a network shared drive, use a local drive instead. – Googie Oct 02 '14 at 11:09

1 Answers1

0

Consider copying the database to the local machine (eg temp folder if transient, or other suitable location if permanent). You can safely use either file copy, or consider using the qt backup API to ensure that the transfer happens successfully (plus you get the option of progress feedback)

https://sqlite.org/backup.html

You could even "backup" the file from the remote server to in-memory if the file is small and you say you're reading only?

You can see some sample code here on how to import an sqlite DB into a Qt QSqlDatabase. Note that when you do this, you want to make sure the version of sqlite native API that you're using is the same as that compiled into Qt, or you may get error messages from sqlite or Qt.

DrDew
  • 1