• Welcome to SQLitening Support Forum.


Welcome to the SQLitening support forums!

Main Menu

Upgraded database to SQLite3 now working.

Started by David L Morris, September 17, 2007, 07:47:15 AM

Previous topic - Next topic

David L Morris

Thanks Paul for your research.  The developer of SQLiteSpy.exe considers this may be a bug, likely in the query optimizer of the latest SQLite3 and suggests a ticket be raised with the full database schema and sql at www.sqlite.org

I will look at this as soon as I can overcome one problem.  My database and code is on one Win98 pc which refuses to connect to www.sqlite.org (page not found etc)  I can connect to almost any other sites easily.  I am currently using a notebook with XP to visit SQLite.org.

In the meantime, I am happy if Mark Strickland would like to have a look at the database and comment.

When can your latest server be released?



David L Morris

Hello again Paul.  The developer of the SQliteSpy.exe utility, has examined my database and SQL and reports the following in an email just received:-

Quote: I can confirm your findings. Execution times slow down with release 3.3.5.

>Please feel free to raise the subject with the sqlite developers if you determine that is appropriate step.

I have created the following ticket:



You can read the ticket titled, "Ticket 2662: Serious Performance Degradation from 3.3.4 to 3.3.5 & latest versions" at the above URL

I am looking forward to reports back from the SQLite developers and will advise.  They may appear in the ticket Remarks.

Regards David

Paul Squires

Excellent - hopefully the answer will come quickly from the SQLite people. Good luck!
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

David L Morris

Answers back.  Recommended Modifications made.  SQL now runs at lightening speed.

Paul Squires

Cool! Were the modifications made to the SQL DLL itself or to your SQL query string?
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Jos? Roca

Internally, the "fast" query is using sqlite3_prepare() and sqlite3_step until all records are returned when it finally calls sqlite_finalize. The "slow" query is using sqlite3_get_table to get all of the records into memory at one time (this is the approach that I use and most other sqlite database managers use). The benefit is that after the query executes you will know exactly the number of returned rows. Using the other method you will not know the number of rows until you iterate through the entire recordset. A downside of the sqlite3_get_table approach is that the whole recordset is created in memory.... what if your query retruns a billion records? Ouch, I expect that the query would then fail whereas the other approach would continue to work. There are pros and cons with both approaches.

Obviously, the most efficient method with queries that will return many results is to use sqlite3_exec and a callback function, since sqlite only will allocate memory for a record. Yes, you won't know the number of returned rows until you have retrieved all the records, but calling sqlite3_get_table you won't get anything until sqlite has done this work for you. So you will end waiting the same amount of time and using much more memory. No doubt sqlite3_get_table is nice for small queries, but not for big queries, specially when you don't need to know the number of records in advance or to store the information in memory, but simply send it to a printer or a file.