4 ms·

Can you explain this some more? You don't really list what you're trying to achieve in your example and I can't think of a case where looping over a large data

by torme 16y ago

Can you explain this some more? You don't really list what you're trying to achieve in your example and I can't think of a case where looping over a large data set in code is better than querying against it.

iaskwhy 16y ago
I believe it's not easy to explain but let me try again and if it doesn't work and you are really interested in it drop me a line by email and I try with some more complex examples, ok? Once I had this problem with a calendar on some application from a client. It was a month calendar and for every day there were some conditions that needed to be verified: is it an holiday?, is it fully scheduled?, is there at least one person available that day?, and so on. How was it working? Well, for every day it would query the database for each one of those conditions which would in the end sum up to 5 minutes since it was a not so small dataset. So how could we fix this? I started with the usual approach, let's just try to get this done in one query and some joins. There was some improvement but it as a messy query full of joins and even some sub-queries. It took something like 20 seconds to load that calendar and that wasn't enough. Next approach: divide the most complex joins and sub-queries in some really simple queries. Examples: get a set of all the holidays, get a set of all the fully booked days, etc. Then, when you loop to show the calendar you just check if the iterating day is on the holidays set, then on the next one until you find one that works; if you don't just leave it blank. This made the calendar loading right away. Benefits: cleaner and (much) quicker code (and also easier to cache if you can, just cache each query individually since some of them don't change that often, like holidays). Is it making any sense now?
- torme 16y ago
  This is much clearer thank you. From your initial example, I thought you were suggesting to just load 2 large tables into memory at once and iterate through them to find matches. Also, at the surface, this seems like it would be a good application for using materialized views. I obviously don't know the ins and outs of what the usage here was, but this seems like a case where slowing down the insert would be a good speed sacrifice for retrieval. I'm not going to even feign being an expert at database optimization, but that seems like it might be a more efficient way to approach it.
  - iaskwhy 16y ago
    I actually didn't know about materialized views but from what I've read it seems like this wouldn't be a good place to put it to work just because the conditions on the calendar where changed pretty frequently (most of them weren't, like I said on my previous post, but some were and from that I read that could be a problem since the view needed to be updated frequently). It feels really good to learn something new, I'm thinking of some scenarios which might improve with this type of views, thanks!

by torme 16y ago

iaskwhy 16y ago

I believe it's not easy to explain but let me try again and if it doesn't work and you are really interested in it drop me a line by email and I try with some more complex examples, ok? Once I had this problem with a calendar on some application from a client. It was a month calendar and for every day there were some conditions that needed to be verified: is it an holiday?, is it fully scheduled?, is there at least one person available that day?, and so on. How was it working? Well, for every day it would query the database for each one of those conditions which would in the end sum up to 5 minutes since it was a not so small dataset. So how could we fix this? I started with the usual approach, let's just try to get this done in one query and some joins. There was some improvement but it as a messy query full of joins and even some sub-queries. It took something like 20 seconds to load that calendar and that wasn't enough. Next approach: divide the most complex joins and sub-queries in some really simple queries. Examples: get a set of all the holidays, get a set of all the fully booked days, etc. Then, when you loop to show the calendar you just check if the iterating day is on the holidays set, then on the next one until you find one that works; if you don't just leave it blank. This made the calendar loading right away. Benefits: cleaner and (much) quicker code (and also easier to cache if you can, just cache each query individually since some of them don't change that often, like holidays). Is it making any sense now?

torme 16y ago
This is much clearer thank you. From your initial example, I thought you were suggesting to just load 2 large tables into memory at once and iterate through them to find matches. Also, at the surface, this seems like it would be a good application for using materialized views. I obviously don't know the ins and outs of what the usage here was, but this seems like a case where slowing down the insert would be a good speed sacrifice for retrieval. I'm not going to even feign being an expert at database optimization, but that seems like it might be a more efficient way to approach it.
- iaskwhy 16y ago
  I actually didn't know about materialized views but from what I've read it seems like this wouldn't be a good place to put it to work just because the conditions on the calendar where changed pretty frequently (most of them weren't, like I said on my previous post, but some were and from that I read that could be a problem since the view needed to be updated frequently). It feels really good to learn something new, I'm thinking of some scenarios which might improve with this type of views, thanks!