[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 --- Comment #9 from jesse --- I still have not had time to try to figure out if the database has indexes or not. I don't use the application with the database option. >From what Martin said, the real need is based on using external programs to query the sqlite database. Without a way to know all the use cases, it may be best to either include documentation in the manual as to how and when a user would want to add indexes for their querying, or to add a few indexes to help our 80% of the use cases. However, I think the decision should be made as to whether saving to the sqlite format was intended for this purpose or not. If not, then perhaps there are no further changes required. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 flywire changed: What|Removed |Added Status|NEEDSINFO |REPORTED Resolution|WAITINGFORINFO |--- Ever confirmed|1 |0 -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 --- Comment #8 from Bug Janitor Service --- Dear Bug Submitter, This bug has been in NEEDSINFO status with no change for at least 15 days. Please provide the requested information as soon as possible and set the bug status as REPORTED. Due to regular bug tracker maintenance, if the bug is still in NEEDSINFO status with no change in 30 days the bug will be closed as RESOLVED > WORKSFORME due to lack of needed information. For more information about our bug triaging procedures please read the wiki located here: https://community.kde.org/Guidelines_and_HOWTOs/Bug_triaging If you have already provided the requested information, please mark the bug as REPORTED so that the KDE team knows that the bug is ready to be confirmed. Thank you for helping us make KDE software even better for everyone! -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 --- Comment #7 from jesse --- I interpreted the question as attempting to use the sqlite database through external programs. Interesting; Is there a reason why it uses the database as a file save(recreates the tables and loads the data each time)? Was the intention to allow users to query the database when KMY is not in use? It is likely that the database does not have any built-in indexes. https://database.guide/list-indexes-in-sqlite-database/ Can the OP or someone with a sqlite database see if that commands brings back a list of indexes? I guess the next step would be to figure out if the KMY code recreates the entire database tables or only truncate and reload the tables. If it recreates the tables, and there no indexes, each time it would need to recreate indexes too. If it only truncates the tables, then the users can create the indexes they need for their custom external applications. Statistics on the database may be needed to help the optimizer keep the database response times fast. jv -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 --- Comment #6 from Jack --- First, i don't know why, but I had interpreted the original post as claiming that accessing the sqlite database outside of KMM to be slow. Normal operations should be the same speed with any back end, possibly differing in the time to open or save a data file (see below.). If the OP was actually seeing KMM run much slower with sqlite compared to xml/kmy, then that should be tracked down as to the cause, since I don't think it should be the case. KMM does not really use any sql during it's normal operations, no matter the back end data store. Whether xml or sql, it reads the data into memory, and only writes it out when you do an explicit file/save (or an automatic save.) In all cases, it wipes the data and writes it all from scratch. (I assume someone will correct me if that's not really correct.) I also don't think KMM uses any indexes, and it uses the same sql code for sqlite, sqlcipher, mysql (which works for mariadb), and postgresql, with some specific tweaks where the differences matter. As far as I know, the only real documentation of the database layout is in the code for creating the tables. Note that there have been multiple discussions over the years of whether KMM should move to exclusively use an sql back end and which one, and whether it's use of sql should be "live" instead of only on save. However, I don't think any changes in those areas are likely in the near to mid term. Jack -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 jesse changed: What|Removed |Added CC||jvap...@yahoo.com --- Comment #5 from jesse --- I am a DBA by profession but have never looked at the KMY database set up. If the queries are doing push-down optimization, I would expect the database to respond quickly. Indexes should be examined to make sure they are set up for the right column per the queries. The hard part is that when you give 100 people access to query however they want, they will come up with 100 different way to query. You can't really optimize for everyone. You have to optimize for a few usage patterns. Perhaps if there is documentation that specifies which columns are indexed, users could tune their queries to leverage those indexes. If using a database without indexes, usually they have other objects to help optimize, like zonemaps for Netezza or synopsis tables for DB2. It makes sense to me that if the code parsed the xml file and loaded it into memory in its own structure, that will be much faster to access than any database. However, depending on how data sets are being joined or searched, the database call may be faster still since it is optimized to do those joins. Is there documentation for KMY on how the database is deploy and what indexes it creates? thanks, JV -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 Jack changed: What|Removed |Added Status|CONFIRMED |NEEDSINFO Resolution|--- |WAITINGFORINFO --- Comment #4 from Jack --- I've been using Perl's XML handling modules to produce some specific reports, and although there was a learning curve, I'm not sure the ultimate code was any harder to create than it would have been using SQL. However, I wonder why the OP found sqlite so much slower than accessing the .kmy or .xml file. There is the time course of having to pull data from the internal structures for every sql call, compared to parsing once into your own internal representation, but I can't imagine it's slow enough to really notice. I'm going to set to NEEDSINFO in case the OP can confirm that the slowness issue is still a problem for him. Otherwise, this will eventually get closed as WORKSFORME. -- You are receiving this mail because: You are watching all bug changes.
[kmymoney] [Bug 353555] Database backend is much slower than standard kmy format
https://bugs.kde.org/show_bug.cgi?id=353555 --- Comment #3 from Justin Zobel --- Thank you for the bug report. As this report hasn't seen any changes in 5 years or more, we ask if you can please confirm that the issue still persists. If this bug is no longer persisting or relevant please change the status to resolved. -- You are receiving this mail because: You are watching all bug changes.
