Fastest way to get COUNT(*)

Databases
41

Undoubtedly it would become complex. Unfortunately you can’t attach the in memory db the the user db because there is no file to specify. If you could do that it would be easy because you could refer to the in memory database.

We need a feature like this in Xojo:

if SQLiteDB.Attach(InMemoryDB, "memory") Then
42

Not having read the entire thread, here’s a thought: The “bad” performance may be due to large amounts of records, i.e. file data, to be read. To optimize this, I suggest:

  • Use a larger pagesize in the DB. This reduces the fragmentation, which can make a significant difference, especially when the DB is on a hard disk, not an SSD.

  • Split the table up into two, so that the table on which you perform the COUNT uses as little data per record as possible, thereby getting more records into each page, and thereby reducing the number of blocks that need reading from disk.

43
  • I have tried a larger page size and it didn’t seem to make a significant difference (was surprised)
  • not an option, these are user tables and the structure cannot be altered
44

Oh bummer. And, of course, you have tried all kinds of indexes over the table to no avail. You are way ahead of me, then.

45

Adding indexes entails altering the users database, again… not an option :slight_smile:

46

Huh, just curious: Why were you able to change the page size, which requires much more effort (such as exporting and re-importing), thereby making a much large change to the DB than just adding an index. I mean, you can add indexes on the fly, and it does not make any changes to the database content or visual structure. Indexes are just internal helpers, just like changing the page size, I’d think.

47

Cannot change pagesize on an existing database… I was thinking cache-size.

And since when does adding an index not alter the content of the database?
It just added a boatload of data, and who decides what it get named? not to mention the legal and ethical ramifications of doing something to private data without the consent of the owner.

48

The content is still the same, it’s just the organization of the data on the binary level that changes. But if you dump the database contents, it’ll still be the same contents, after either changing the page size or adding indexes. And you can always choose a generic name for the index that’s both unlikely to collide with the customer’s namings and even indicates that it was added by your software, to avoid any confusion. And you can ask the user beforehand, for his permission, I imagine.

Whether the owner of the DB allows you to optimize the DB for faster access is up to you and them. I was just making suggestions for viable solutions in that direction. Since you did not attempt to either change the page size or add indexes, while you apparently have a database for testing, I still suggest you at least try these out, on your own copy of the DB, to see if they DO help. If they provide a significant change, you can then still think about suggesting those optimizations, along with making a backup first, just in case. And if it turns out that my suggestions do not help, well, then there’s no need to go on with it :slight_smile: It’s up to you, I just offer options.

49

thanks

50

Didn’t read the entire topic aswell. Did you consider online backup into memory and conducting your queries there as an option?

51

actually I am testing that option right now

52

Another option is to add an index in a transaction, but that might end up slower.

53

If you’re testing try this too:

  1. read all your IDs seperated in a single Query as String: SELECT group_concat(ID,’;’) AS Something FROM YourTable
  2. Add them into string: Somestring = rs.Field(“Something”).getString.DefineEncoding(Encodings.UTF8)
  3. Split this string into an Array and Count the Items.

Would be interesing to know if this is faster than COUNT

54

[quote=331186:@Neil Burkholder]Unfortunately you can’t attach the in memory db to the user db because there is no file to specify. If you could do that it would be easy because you could refer to the in memory database.

We need a feature like this in Xojo:

if SQLiteDB.Attach(InMemoryDB, "memory") Then

Not sure I understand what you’re saying here. I attach memory databases to open databases all the time, thus:

[code]// Open the database file
Dim dbh As New SQLiteDatabase, dbFile As FolderItem, result As Boolean
dbfile = getfolderitem ("/path/to/my/db", FolderItem.PathTypeNative)
dbh.DatabaseFile = dbFile
result = dbh.connect ()

// And later, attach a memory db to it
dbh.SQLExecute (“attach database ‘:memory:’ as mem”)[/code]

Or was it an already existing memory db you want to attach?

55

took 153.5 seconds, not counting time to split etc.

took 229.13

took 230.6

interesting I would have said group_concat would take 10x longer

56

…and time to split everything up into an Array? And we still haven’t used memoryblocks…

57

doesn’t matter… 153 seconds is too long

58

Do other tools handle this file any better? There may not be a solution for the monstrosity you’ve created. It seems like a good worst-case scenario: 18Gb of non-indexed data. (Does that even qualify as a database?) It also sounds like you’re getting decent results from more “sane” databases, so your approach is solid.

59

[quote=331277:@Tim Streater]// And later, attach a memory db to it
dbh.SQLExecute (“attach database ‘:memory:’ as mem”)
[/quote]
Where is the double like! I’m not sure where I could have found that in the docs.

[quote=331277:@Tim Streater]
Or was it an already existing memory db you want to attach?[/quote]

Is there a way to do that?

60

Neil… not sure I understand your question.
You can attach a physical DB to a memory one
OR
You can attach a memory DB to a physical one

you just need to use the “special” reference of “:memory:”