In 2014 R2 it takes 695ms to return 1925 records
In the same code complied for 2014 R it takes 7ms!!! Yikes.
What happened?
Complied this on a 10.10 system but I believe I was seeing the same issue on my 10.9 system. Will have to confirm when I get back to the office this Friday.
Case 34474.
Anyone else seeing this issue?
Not here
10.8.5 with r1.1 returning 8192 records = 28314 microseconds
10.8.5 with r2.0 returning the same 8192 records = 27114 microseconds
I’d add a few indexes to your tables
Just adding indexes to some of the columns used in the queries brings the time down to 74
Hey Norman I tried the indexes.
It still results in R2 2014 being 22X slower than R1.1
I also tried making the same query to a DB created in 2 and 1.1 from a 3rd party app and I get the same query time. This all points to a change in the framework that is responsible for the speed hit.
With large queries this makes my app go from responsive to “get up and make a sandwich” slow.
For the first time in five years I’ve had to revert to a non shipping version of XOJO to build my releases. 
But you guys do great work so I’m confidant that you’ll get it fixed quickly!
[quote=111930:@Julian Mussi]With large queries this makes my app go from responsive to “get up and make a sandwich” slow.
For the first time in five years I’ve had to revert to a non shipping version of XOJO to build my releases.
But you guys do great work so I’m confidant that you’ll get it fixed quickly!
[/quote]
Since Norm does not see it, you need to file a bug report and include a project and a DB file that show the performance issue between the 2 IDE versions. If not, likely nothing will get done.
A bug report was filed.
[quote=111930:@Julian Mussi]Hey Norman I tried the indexes.
It still results in R2 2014 being 22X slower than R1.1
I also tried making the same query to a DB created in 2 and 1.1 from a 3rd party app and I get the same query time. This all points to a change in the framework that is responsible for the speed hit.
[/quote]
The database itself does not contain the engine so unless the third party app has both the old (3.7) and new (3.8) Sqlite engines all that tells you is that the data file (which is all the sqlite db is) can be read by both versions
Your app does indeed show a significant slow down.
Mine was a much simpler query that does not show this.
Not 100% sure if its in the the sqlite engine OR the plugin.
Will have to examine the query plans to see what, if any, differences there are.
Its really starting to look like “something” in sqlite changed which is causing this.
I used your db in a cli version downloaded from sqlite.org
That eliminates changes in the plugin being the cause
I grabbed the 3.7.17 version, which we used in 2014r1.1, and the 3.8.5 version, which we used in 2014r2.
The 3.8.5 version is 50 x slower using the exact same db with the exact same query.
There may be something we can do with optimization settings etc but there’s definitely a significant difference just in the engine that ripples into overall performance in a built app.
Norm,
Are you going to report it to the SQlite people?
This one is interesting. SQLite updated their query execution engine (as all DB engines do from time to time)- and they’ve made some things faster and some things slower. So this particular execution path may just be part of a known tradeoff.
With that said we’re looking to see if there is a way to change this particular query / set of indexes to get it closer to the prior SQLite’s execution speed.
[quote=112214:@Travis Hill]This one is interesting. SQLite updated their query execution engine (as all DB engines do from time to time)- and they’ve made some things faster and some things slower. So this particular execution path may just be part of a known tradeoff.
With that said we’re looking to see if there is a way to change this particular query / set of indexes to get it closer to the prior SQLite’s execution speed.[/quote]
If you do find out why and it’s not bug, please post it so we know what to avoid!
[quote=112205:@Karen Atkocius]Norm,
Are you going to report it to the SQlite people?
See point 4.0 Hazards Of Upgrading To NGQP
The problem is that some applications may be using low-quality and low-selectivity indices without having run ANALYZE. The older query planners look at many fewer possible implementations for each query and so they may have stumbled over a good plan by stupid luck. The NGQP, on the other hand, looks at many more query plan possibilities, and it may chose a different query plan that works better in theory, assuming good indices, but which gives a performance regression in practice, because of the shape of the data.
Julian try one thing
When you connect to the DB issue one command
ANALYZE
thats it (using sqlexecute)
And continue on your way
Tell me what that behaves like
For anyone following along
This is a change in SQLite between version 3.7 and 3.8
see http://www.sqlite.org/queryplanner-ng.html
4.0 Hazards Of Upgrading To NGQP
For most applications, upgrading from the legacy query planner to the NGQP requires little thought or effort. Simply replace the older SQLite version with the newer version of SQLite and recompile and the application will run faster. There are no API changes nor modifications to compilation procedures.
But as with any query planner change, upgrading to the NGQP does carry a small risk of introducing performance regressions. The problem here is not that the NGQP is incorrect or buggy or inferior to the legacy query planner. Given reliable information about the selectivity of indices, the NGQP should always pick a plan that is as good or better than before. The problem is that some applications may be using low-quality and low-selectivity indices without having run ANALYZE. The older query planners look at many fewer possible implementations for each query and so they may have stumbled over a good plan by stupid luck. The NGQP, on the other hand, looks at many more query plan possibilities, and it may chose a different query plan that works better in theory, assuming good indices, but which gives a performance regression in practice, because of the shape of the data.
Key points:
The NGQP will always find an equal or better query plan, compared to prior query planners, as long as it has access to accurate ANALYZE data in the SQLITE_STAT1 file.
The NGQP will always find a good query plan as long as the schema does not contain indices that have more than about 10 or 20 rows with the same value in the left-most column of the index.
Not all applications meet these conditions. Fortunately, the NGQP will still usually find good query plans, even without these conditions. However, cases do arise (rarely) where performance regressions can occur.
From the case I modified the example program to do
dim d as new SQLiteDatabase
dim r as RecordSet
d.DatabaseFile = GetOpenFolderItem(".cdb")
dim ts as integer = ticks
if d.Connect then
d.SQLExecute("Analyze")
ts = ticks
r =d.SQLSelect(s)
if R<> nil then MsgBox "Time/records " + str(ticks - ts) + " : " + str(r.RecordCount)
endBefore
R2 695 : 1925
R1.1 7 : 1925AFTER analyzing
R2 3 : 1925
R1.1 4: 1925But maybe, you can add an advice/info to the manual? That would be nice. Thank you.
I have added information about using ANALYZE to the Performance section of the SQLiteDatabase page.
You SHOULD analyze your DB from time to time anyways
The statistics that the sql query optimizer uses to determine what is the “best” path to chose through the data can and does vary as you add data to the db.
NOT analyzing periodically can result in a less than optimal query being used and unexpected slowdowns.
For business systems, doing data backups are routine. You could analyze the DB just after the backup as an extra step like:
Executing backup…Optimizing…End
Periodic maintenance is simply a fact of life
Regardless of db engine
You should vacuum from time to time
And analyze to update query engine stats
Had to do that on every SQL db I’ve ever used
hi norman, how do i analyse ?