13

I have a somewhat complex query with multiple (nested) sub-queries, which I want to make available for the applications developers. The query is generic and generates a view with computed values over a collection of data sets, and the developer is expected to need only some records from what the query returns (i.e. they will limit the result for some entity's ID or a date range or some such).

I can see 3 ways to implement this:

  1. Let the developers embed the query into each application and add their own WHERE clauses as needed.
  2. Create a stored procedure that accepts as parameters all the conditions I expect developers to need (for the sake of the argument lets say that I can predict what will be needed for the foreseeable future), and the procedure will run the complex query and filter it according to the parameters passed.
  3. Implement the query as a view with several sub views (because MySQL doesn't allow sub-queries in views) and have the developers use this as a table and use WHERE to have each application applies the filters they need. Currently I'm looking at 3 additional sub-views, mostly because some sub-queries are used multiple times and doing them as sub-views prevents duplication - otherwise it could have been worse ;-).

What will be better performance wise? (assuming all indexing is equivalent in all cases) Go for worst case scenarios, if you may.

what will be better in code maintenance terms, do you think?

Guss
  • 30,470
  • 17
  • 104
  • 128
  • if the query is complex and does several things i would prefer 2. For performance, it's the same for every answer, but a little better performance for 2 (views in mysql are not new tables). For maintenance i like the view most, but any of the answers if done properly won't have manteinance problems. – Packet Tracer Oct 20 '11 at 16:33
  • you could do a stored procedure that calculates the results in a temporary table (each period of time, for example 1 day), and let the developers query that table. if the query is used a lot, and the time between updates is not too short, that would improve performance a lot. – Packet Tracer Oct 20 '11 at 16:36
  • 1
    @Feida - If I do updates I need them to be immediately visible in the output, which will be more complex to achieve that way (I'd need to update using stored procedures). I also don't like to use this kind of "manual caching" - the database should handle caching automatically and if it does badly I'll help with indexes and maybe a redesign, but another manually maintained caching layer inside the database using temporary tables is a solution I never liked. Regarding complexity, is not really a multi-step program - just a lot of joining, sorting and calculations. But write it up as an answer? – Guss Oct 20 '11 at 21:39
  • you can add some triggers for the temporary tables update. it's a tedious solution, but with a very good performance if the query is complex. if the query is not such complex, just go for the stored procedure, or maybe the view. the view is the most clear way for a developer, coz it's like querying a regular table. writing this as an answer? would you vote positive? :D – Packet Tracer Oct 21 '11 at 11:21
  • I always vote positive for answers that are thoughtful. – Guss Oct 21 '11 at 14:39
  • then I'll try to developer a good explanation :) – Packet Tracer Oct 24 '11 at 09:19
  • +1 for asking about performance AND maintainability – Neville Kuyt Oct 24 '11 at 11:38

2 Answers2

7

I like questions that define "good" - you've specifically asked about performance and maintainability, which allows answers to talk about that trade-off.

From a performance point of view, I don't think there's likely to be any difference between the 3 options, as long as the queries and data fit within your expected scenarios. I'd test with 100 times more data, and potentially widening the "where" clause to see what happens, but the indexing structure etc. is more likely to affect the performance than whether you execute the same SQL from a stored proc, through a view, or from a client application.

The best way to answer that question is to test it - there are, of course, many specific details that could invalidate the general "I'd expect x, y, or z" answers we overflowers can give. If performance is a critical concern, use a database filling tool (Redgate make on, I've used DBMonster in the past) and try all 3 options.

From a maintenance point of, view, I'd provide an option 4, which - in my view - is by far the best.

Option 4: build a data access library which encapsulates access to your data. Have the library expose methods and parameters to refine the selection of records. Consider using the specification pattern (http://en.wikipedia.org/wiki/Specification_pattern). Use whatever queries are best inside the library, and don't bother the developers with the implementation details.

If that doesn't work - heterogeneous application code, too much of a change for a simple requirement - I'd evaluate the options as follows:

  1. Embedded SQL: depending on the number of times this SQL is re-used, this may be okay. If there's only one part of the code that runs the SQL, it's logically similar to the data access library. If, however, the same snippet needs to get re-used in lots of places, it's a likely source for bugs - a small change in the SQL would need to be repeated in several places.

  2. Stored procedure: I generally dislike stored procedures for maintenance reasons - they tend to become brittle by over-loading, and create a procedural way of thinking. For instance, if you have other requirements for using this SQL calculation in a separate stored procedure, very quickly you end up with a procedural programming model, with stored procs calling each other.

  3. Views: this is probably the best choice. It puts the specific data logic in a single place, but promotes the use of set-based logic because the access route is through a SELECT statement, rather than by executing a procedural statements. Views are easy to incorporate into other queries.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • The main problem I have is that there are currently 3 users of the query, each using a different technology - a website written in PHP, a web service API written in Python and a command line tool written in Ruby, so maintaining a single library with data access commands for all 3 platforms will be very problematic. – Guss Oct 24 '11 at 14:09
  • yeah, that's often the case - I'd use a view. – Neville Kuyt Oct 24 '11 at 14:26
  • Thanks for the input. After some testing I've decided to go with the view as the stored procedure seemed a bit clunky to me and I didn't see any significant performance improvement over view. – Guss Nov 02 '11 at 18:55
3

If well implemented, any of the three solutions would be fine for manteinance, but bear in mind how would you treat each of them in a migration process (code or database migration).

If the query is big, the stored procedure will give you a bit of extra performance due to less bandwith overhead because it's sending a smaller sized query. You may also gain a little extra security with this solution.

For a manteinance solution, I would prefer the 1st and 2nd solution, coz you can make any changes on the query without doing any database changes. If you choose the 1st solution, I would wrap the query call within a function so you'll have only one place to make changes.

From a developer point of view, I would choose the view solution beacuse is the most transparent one, I mean it's like querying just a regular table, you can check table structure with a describe command, or just select the fields and conditions you need to query, or join with another table, etc...

About the where clause flexibility, you can achieve it with any of the proposed solutions. You can add a where parameter in your wrapping function (1), you can add a where parameter to the stored procedure but be cautious with injections (2), or the developer can add a where clause as usual with the view (3)

Having in mind that in MySQL views are not temporary tables, if the query is very complex this solutions wouldn't be the best if the query is used a lot and in different ways (disabling cache performance boost). I would consider a temporary table solution (counter table) that updates each time period with a programmed task / cron (for example a day, a week, whenever needed) or gets updated by setting the propper triggers. This solution could improve performance quite a bit.

Hope this helps, I like the view solution the most but maybe it's more complex to develope from a database point of view.

Packet Tracer
  • 3,884
  • 4
  • 26
  • 36
  • The query is rather complex (uses several nested sub queries, some of which are rather long) and there are several users of it - each with their own developer. I don't understand how the temporary table approach would work - supposedly there is a table (that looks like how the view would look) and triggers on all the concrete tables in the query would cause the query to be run in the database and insert into the table? Why do you call this "temporary table", as "temporary table" in MySQL means it gets automatically deleted when the client disconnects. – Guss Oct 24 '11 at 14:08
  • You should insert on the original tables and update the temporary with triggers. Though you may insert in the temporary and update the origanl tables with triggers. – Packet Tracer Oct 24 '11 at 14:18
  • With temporary table I mean engine=memory, but you can do it with a regular table. So it resides on ram, and is much faster though it requires memory :I – Packet Tracer Oct 24 '11 at 14:19