I would need the sqlite format for interoperability with other programs (extract address info for Mailings etc.), but operation with sqlite is much slower than with the native kmy format (which in turn is not readable by other programs). Reproducible: Always
I can confirm this. But the changes to fix this issue are too complex for the available developer time. The kmy format is a gzipped xml file. It is not hard to parse it, maybe you should give it a try. Btw: KMyMoney 4.7 has some improvements regarding the database backend. It should be bit faster (only a bit!).
This is disappointing because readability by other programs through a database is one of the big benefits of this program; a major reason for me to leave a propriety software package. Much easier in a database than xml. For example, customised processing of tagged transactions externally to integrate the finances to your project and a whole lot of other data such as mapped output. Now that's value add!!
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.
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.
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
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
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
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!
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.