Xojo 2017R3 SQLite Soooooo SLOW

Databases
1

Hello guys,

I have no idea if anybody else has same issue, so I opened the current project that was processing 150000 records in few minutes and run it in the latest XOJO and its like not working at all, I see like 1 Rec / 3 sec, if I start the same project with the previous version 2017R2.1 I get like 50 / sec so way to small, even on windows was faster than this one now.

Did anything changed so bad that affected this ?

Thanks .

2

the verson of SQLite was bumped UP to 3.20
Xojo2016r4.3 was Sqlite 3.14.1

3

I noticed that too, on a 400k records database it’s slower than before, noticeable (but I did not quantify it)

4

Please create a Feedback case. Include a small sample project to demonstrate the slow behaviour. There are many factors that could be affecting performance (too many for me to guess at!). Just as an example, is an in-memory or file based SQLite database being used? Having a sample project that demonstrates the slow response on your system/s would be a great help.

5

Please use a transaction.
Without transactions, you do an implicit commit for each record.
And set to use a big cache like 100 MB.

6

it’s noticebly slower only in reading datas… so no transaction ?

7

for Cache, please read my blog post:
http://www.mbsplugins.de/archive/2017-10-16/Big_SQLite_Cache_for_64-bit_in/monkeybreadsoftware_blog_newest

8

seems to work faster with a big cache, thanks.

9

Release Notes for each release can be read from here .

10

[quote]for Cache, please read my blog post:
http://www.mbsplugins.de/archive/2017-10-16/Big_SQLite_Cache_for_64-bit_in/monkeybreadsoftware_blog_newest [/quote]

Cubesql is based on sqlite, should we be doing something similar for this DB too?

11

It depends.

  • is the issue being observed an issue with SQLite 3.20? and has nothing to do with Xojo itself?
  • is the issue based on configuration details that Xojo did or did not optimize?
  • do any of the suggestions bring the speed to what was observed in the past for the same situation?
  • If it is an SQLite 3.20 issue, then does CubeSQL currently use this version, and does CubeSQL already implement optimizations that Xojo does not?
  • [SQLite itself is at 3.21.0]
12

Thanks Dave, I guess its test and measure

13

FYI… cubeSQL is using SQLite 3.16.1

14

[quote=364573:@Dave S]It depends.

  • is the issue being observed an issue with SQLite 3.20? and has nothing to do with Xojo itself?
  • is the issue based on configuration details that Xojo did or did not optimize?
  • do any of the suggestions bring the speed to what was observed in the past for the same situation?
  • If it is an SQLite 3.20 issue, then does CubeSQL currently use this version, and does CubeSQL already implement optimizations that Xojo does not?
  • [SQLite itself is at 3.21.0][/quote]

We’re trying to keep SQLite more up-to-date these days, so you guys get their bug fixes more quickly. Version 3.21.0 was released after we were in beta and we usually like to wait a while to make sure there won’t be an immediate .1, .2 release fixing stuff that they broke.

I just skimmed the release notes for 3.21.0 and see no mention of speed improvements.

If increasing that cache size makes such a huge difference, please file a feature request so we can look and see if there’s an easier solution than having to set a pragma.

15

it’s not the problem of the FR, it’s providing an example project with 100k’s of records …

16

[quote=364636:@Greg O’Lone]We’re trying to keep SQLite more up-to-date these days, so you guys get their bug fixes more quickly.[/quote] :slight_smile:

17

Increasing the cache for me didn’t work. If I open the project in r2.1 the query is fast, running in < 1 second. Opening the same project r3 causes the query to run in about 20 seconds. I’m working in a web project.

Also, I use DB Browser for SQLite (Windows), and it’s now running that same query slow. I wrote the query in DB Browser initially so I know it ran fast. Nothing has changed in the database or code.

Is there any way I can use another version of SQLite without downgrading Xojo?

18

[quote=365182:@Nick Alonge]Increasing the cache for me didn’t work. If I open the project in r2.1 the query is fast, running in < 1 second. Opening the same project r3 causes the query to run in about 20 seconds. I’m working in a web project.

Also, I use DB Browser for SQLite (Windows), and it’s now running that same query slow. I wrote the query in DB Browser initially so I know it ran fast. Nothing has changed in the database or code.

Is there any way I can use another version of SQLite without downgrading Xojo?[/quote]
No.

19

download the demo for my Tadpole… it is currently using Sqlite 3.14

www.rdS.com/tadpole

fyi… .Tadpole is 100% Xojo code

20

[quote=365182:@Nick Alonge]Increasing the cache for me didn’t work. If I open the project in r2.1 the query is fast, running in < 1 second. Opening the same project r3 causes the query to run in about 20 seconds. I’m working in a web project.

Also, I use DB Browser for SQLite (Windows), and it’s now running that same query slow. I wrote the query in DB Browser initially so I know it ran fast. Nothing has changed in the database or code.

Is there any way I can use another version of SQLite without downgrading Xojo?[/quote]
That’s strange, I don’t think there is anything from Xojo than can make DB Browser slow with the same query, same version and database without changes.

The only way Xojo can find a problem like this is with an example. If you can, send a Feedback case so they can take a look.