Optimizing the MODx data structure for faster page loads

I think the plugin way sounds very optimal for this use compared to a seperate module. I can’t wait to see it.

Why I think it’s a good current work-around
- simplicity for user. Content changed inside current documents.
- data is optimized in your table for the queries on the front-end

The only negative I see is duplicated data. But that seems small.

The duplication of data is a minor negative point that doesn’t concern me too much right now. I am concerned, though, that if the entire document object (TVs, templates, document, etc.) is always retrieved in its entirety no matter what -- even if TVs are not used with the regular TV syntax: [*my_tv*] -- then any separate method for retrieving the data will duplicate the task of data retrieval. The TVs will be retrieved as a part of the document object, and the TVs (and perhaps other data) will be retrieved again through my custom snippet.

I don’t know that this is the case though, which is why I asked the question: Are all of the TVs retrieved even if they’re not used?

I believe that all the TVs are retrieved, but calculating which were needed and generating a custom query on the fly might take even more time, especially since theTVs are not separated into categories (there’s no table for "instructor" TVs).

Having done academic class/room scheduling (way before my MODx days), I can say that it’s a remarkably difficult DB challenge because the relationships are so complex. There are a hideous number of many-to-many relationships between classes, instructors, rooms, buildings, days, hours, class periods, and students compared to, say, a manufacturing parts inventory or a typical e-commerce site. Trying to handle this complexity with TVs is kind of like trying to build a sailboat with a salad fork. I don’t think it’s quite fair to blame this on MODx.

I seriously doubt if you could find a CMS that does it any faster than MODx could. Many couldn’t do it at all. It’s entirely possible in MODx to have a well-indexed custom db with lots of stored queries and a good indexing system and build editing pages that would make it a pleasure for users to maintain the data. The retrieval would probably be as fast as your connection to the site.

The down side is that you’re going to have to do the work of designing and creating it, but if you want fast response times with that complex a data structure, you’re going to have to do it anyway.

there’s no table for "instructor" TVs

What if there were? What if every template had an associated table with the corresponding TVs? I don’t know all the consequences of such a structure, but I think it might have some benefits:

• The database structure would be more like a typical custom database solution. The information for a document would be in "modx_site_content" and the corresponding table for the template, e.g. "modx_site_template_instructor_tvs" or something like that (or, more likely "modx_site_template_12_tvs", where "12" is the template number)
• A MySQL view could be created of the join of those two tables, allowing for simplified querying of documents. (Note that views can also be created now but they require a join for every template variable, and query performance degrades with each additional template variable because of the accumulation of joins)
• Having a dedicated table for the TVs would allow us to also specify the data type (integer, text, char, etc.), length, and other field-specific settings, which would optimize the database for the data it is serving. It would also allow for more precision in creating code to extract it (e.g. integers would be true integers).

The number of tables would increase, of course, and there may be negative consequences as a result of a larger number of tables, but from what I’ve read, MySQL handles large numbers of tables well enough. Maybe there are other negative consequences, I don’t know, but having a dedicated table for each set of template variables makes sense to me.

@paulb

Currently to grab a TV value:


Your suggestion seems to be to combine site_tmplvar_contentvalues and modx_site_tmplvars and introduce proper data typing. Whether you have a separate table, using some automagic table creation for each template, or one big TV table, you’d only be dropping one join. For general purpose CMS it’s not going to be worth it, is it, given the loss of flexibility (TVs become unique to one template) and the redundancy you’ve introduced (potentially multiple TVs of the same datatype per template)? You might as well write a custom data structure for your application if you’re going to do this, surely?

As I said before, one area of performance that could addressed with a plugin is the relationship between documents, since this is where MODx is weak. If you want to relate a document modeling a course to another document modeling a room, then you have to use a TV, which is inefficient. Currently MODx only supports a hierarchical relationship via a self join - the site tree - with the parent field. It might be useful to have a custom xref table to make many-to-many and one-to-many relationships possible between documents as well. Then you could easily generate a resultset of all the courses assigned to a room, for example, using only 1 join.

Mark

For general purpose CMS it’s not going to be worth it, is it, given the loss of flexibility (TVs become unique to one template)

Here’s one way that it could work:
The modx_site_tmplvars table could still provide a way of specifying general purpose TVs, which could be used in multiple templates. When you go to create a template, you could still select from among the available TVs in that table, but upon doing so, instead of throwing the values in site_tmplvar_contentvalues, it would put the values in a table dedicated to that template. That’s a straightforward change to the base code that doesn’t change what MODx is doing, conceptually, but it does widen the possibilities as far as relationships between types of data because it makes join queries across data categories (courses, instructors, rooms) more straightforward.

If the user updates the general TV in site_tmplvars (changes the data type, name, or other attribute), MODx could find out which tables are using that TV and update the tables accordingly (and/or warn the user that this might truncate data if the field length is being shortened, or change the values if the data type is being changed, etc.).

This wouldn’t solve all of the data relationship scenarios (e.g. it doesn’t create relationship tables for many-to-many or one-to-many relationships), and that would need to be addressed separately, but it simplifies one-to-one relationship queries at least. Here’s a simple hypothetical example of querying all of the course instances for a given semester, combined with the generic information about the courses (not semester-specific):


I like the ease of creating simple queries like that. Is there a similarly streamlined method for creating the equivalent results table in MODx now? As far as I can tell, there isn’t. I could specify each TV one by one as additional LEFT JOINs, or, I don’t know, maybe there’s some other better method (which I would be VERY interested in hearing), but it doesn’t seem as straightforward to me.

... and the redundancy you’ve introduced (potentially multiple TVs of the same datatype per template)?

Having multiple TVs of the same data type in the same template doesn’t seem like an issue at all to me, if what you mean is that there may be 3 fields that are integer types, 6 that are varchar, 2 that are text, etc. Don’t most tables have more than one field of the same data type?

You’d need to duplicate the TV name and its properties for each template table or that WHERE clause wouldn’t be possible (you’d need another join), so this relies on the plugin to maintain parity for all the instances of the TV in all those different template tables. This is what I meant by redundancy, and normally you’d want to eliminate that by using a separate table to manage the TV metadata... which brings us back to how modx is actually set up!

I can’t see another way of doing what you want without around 6 left joins (assuming 3 tvs), and would also be very interested to know if anyone else knows another way.

Paul, I tried to create plugin for Quick Access to Documents and TV-values.
It is here: http://modxcms.com/Quid-2305.html (just alpha).
The idea is like your thinks - to automatically create tables in database for each template.

Hi,

sometimes is better not to use TVs.
I have a database in oracle in order to get gps data which is saved every minute for about 1400 trucks.
so imaging the data. we talk about 23 million records. a database which is about 50 gb of size.

i use mysql, mssql and oracle all three together.
mysql is simple some basic data as modx, telefondata, some simple adresses etc etc.
and oracle for the heavy stuff. mssql is used for digital archive.
i wrote a plugin to connect to oracle and can connect whenever i want to oracle just by setting the focus on oracle.
with the chunktpl from wendy i am able to make good, fast tables out of oracle mixed up with mysql.
i even need mssql to get documents out of our digital archive. all this in one list.

within the same plugin i wrote some code for oracle and mssql.

works fine.

if i imaging to use tv for this... hmm my administrator will kill me i guess

Quote from: triden at Jan 20, 2009, 02:09 AM

sometimes is better not to use TVs.
...

if i imaging to use tv for this... hmm my administrator will kill me i guess

Indeed triden; thanks for reinforcing my view of this. I still try to convince people to think in terms of actual business model data (which belongs in transactional databases) vs. view or configuration data (which mysql and MODx and TVs are great at) for visualizing it.