Re: [Lazarus] SQLite performance problem. Simple query takes too long.
I have established that the problem is not in SQLite, but has to be in my code somewhere. I suspect it is related to the transaction/commit problem I posted a few days ago, and I plan to track that down today if I can. I need to finish this application by tomorrow. 2011/7/29 Martin Schreiber mse00...@gmail.com: Am Donnerstag 28 Juli 2011, 22:01:32 schrieb Howard Lee Harkness: Unfortunately, it has a lot of information that is sensitive (belongs to a client). I will look at building a database with dummy data. I can, however, provide the schema, if that would help. I tested with MSEide+MSEgui, it is instant too, see attachment. Suggestion: test with a similar simple Lazarus application. Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus -- Howard Lee Harkness (214) 390-4896 -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] SQLite performance problem. Simple query takes too long.
Am 29.07.2011 14:32, schrieb Howard Lee Harkness: I have established that the problem is not in SQLite, but has to be in my code somewhere. I suspect it is related to the transaction/commit problem I posted a few days ago, and I plan to track that down today if I can. I need to finish this application by tomorrow. Sqlite manages transactions by file locks AFAIK. A not finished resultset fetch also locks the file. So tsqlquery.packetrecords must be set to -1. MSEgui sqldb version works with implicit transactions by default which makes life with Sqlite3 easier. On the other hand writing many records to the database should be enclosed in a transaction otherwise there will be a slow filesync after every write statement. Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] SQLite performance problem. Simple query takes too long.
On Thursday 28 July 2011 17:54:03 Howard Lee Harkness wrote: I am doing a conversion of a small database application from PostgreSQL to SQLite. I have gotten the program to work, but it it about 2 orders of magnitude slower than the PostgreSQL version. In particular, a query like select distinct clients.* from clients where lastname like 'Hark%' order by lastname, firstname, middlename; Can you post the database file at a place where I can download it or send it to my Email address if it is not too big? Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] SQLite performance problem. Simple query takes too long.
Unfortunately, it has a lot of information that is sensitive (belongs to a client). I will look at building a database with dummy data. I can, however, provide the schema, if that would help. On Thu, Jul 28, 2011 at 2:10 PM, Martin Schreiber mse00...@gmail.com wrote: On Thursday 28 July 2011 17:54:03 Howard Lee Harkness wrote: I am doing a conversion of a small database application from PostgreSQL to SQLite. I have gotten the program to work, but it it about 2 orders of magnitude slower than the PostgreSQL version. In particular, a query like select distinct clients.* from clients where lastname like 'Hark%' order by lastname, firstname, middlename; Can you post the database file at a place where I can download it or send it to my Email address if it is not too big? Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus -- Howard Lee Harkness (214) 390-4896 -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] SQLite performance problem. Simple query takes too long.
Am 28.07.2011 21:01, schrieb Howard Lee Harkness: Unfortunately, it has a lot of information that is sensitive (belongs to a client). I will look at building a database with dummy data. I can, however, provide the schema, if that would help. I need a reproducible testcase, please send the dummy data. Martin -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
