Architecture, Array vs SQL

Architecture, Array vs SQL

by Thomas Stickzr -
Number of replies: 1

Hi,

im currently working on a Block Plugin that loads some data via the Competency-, Course Module- and Gradebook- API into arrays, calculates some stuff and writes it into the block content. Changes are only necessary if a student gets a new grade or something in the course structure changes. Since I want to build it with best practise and good performance architecture Im thinking about the following points:

- I tried to do everything with API calls, parsing the needed data out of them. I could also put everything together in one self made SQL Query with some Joins. Which option is the better one?

- Should I hold all the data in arrays or create and insert a new DB table? I guess arrays would have the better performance but the database solution maybe prettier and easier to maintain?

- At the moment Im executing everything in the get_content() function in the main file, so everything is loaded all the time with every site refresh, which is not optimal regarding performance and modularity I guess. How can I design that better?

- With a database solution I would calculate everything and store it in a table. Then I would have to listen to events and everytime an activity gets graded or something in the course changes, like a new activity gets created, I would have to update the table in the database. Does that make sense?


I hope somebody can give me some advice regarding these issues! Also a link to an example plugin covering some of this things would help a lot!

Thank you!

Average of ratings: -
In reply to Thomas Stickzr

Re: Architecture, Array vs SQL

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
- I tried to do everything with API calls, parsing the needed data out of them. I could also put everything together in one self made SQL Query with some Joins. Which option is the better one?
This is a bit of a toss-up between raw performance and maintainability.  Sticking everything into a big SQL query will probably be more performant (as long as you write the SQL well), but it will probably be less obvious what's going on when you or someone else comes back to this code at a later date. Also, API functions will take care of any underlying changes to the database structure, which you'd otherwise have to watch out for.

- Should I hold all the data in arrays or create and insert a new DB table? I guess arrays would have the better performance but the database solution maybe prettier and easier to maintain?
- At the moment Im executing everything in the get_content() function in the main file, so everything is loaded all the time with every site refresh, which is not optimal regarding performance and modularity I guess. How can I design that better?
- With a database solution I would calculate everything and store it in a table. Then I would have to listen to events and everytime an activity gets graded or something in the course changes, like a new activity gets created, I would have to update the table in the database. Does that make sense?
Have you looked at caching?  It sounds like that would be better suited to this case than storing the display data in the database.  You can just stick the display data in the cache, then check if it's there before doing new queries/API calls.  You can also define events which will automatically invalidate data in the cache.
Average of ratings: -