I have a piece of code that has to calculate some pretty hefty stuff over a large amount of data. Unfortantely, that large amount of data took large amount of time to load. By large amount I mean, I walked away and had time for a coffee, chit chat, about three phone calls and a relaxing bout of head banging, and it still continued to pry into the database, and likely would continue to do so until the end of time or there about.

This calculation has two main charactaristics:

1. It is vital to several core functions of the system.
2. It is very highly preferred to make this calculation on the fly. Doing it on the backend is possible, but will cause a lot of major complications.

So, in the middle of checking the price of a dozen new servers (and a safe place in Nigeria, once the client hear about this), it occur to me that while pre-mature optimization is evil, maybe optimization itself has some value and that Nigeria might have to wait for another day.

After carefully thinking about the scentific process (i.e: observing the occurances, forming a theory, preparing experiments, proving a theory, arguing for a decade about what it means, etc...) I decided to take a more direct approach and looked at what I was trying to do.

Then I added this to the query:

And I re-run the whole thing. The performance benefit was four orders of magnitude. And by that I mean that the page is still very heavy DB wise (around ~50 queries on empty cache, which is my usual benchmark), it actually complete in real time, and all the rest of that stuff are things that are very easy to solve (grab those three pieces of code in one shot, instead of five, etc).

Of course, I then had to spend about half an hour staring at the generated query and think about what it was doing (there was a CROSS JOIN there that scared me) before coming to the conclution that it really was a good to fetch all that data. Well, almost. There should have been around ~6000 rows returned from this query, but only 2 were returned.

After a long bout of head scratching, I determained that the fault was at my mapping. I had several places where I had where clauses like this one:

I'm pretty sure that you can see where this is going. In a left join scenario, this (non-nullable) column is going to be null, so it would evaluate to false, making the join into an inner join, reducing the returned data by quite a bit. I'm writing this post as I go along fixing this issue. Right now the situation is not much improved :-(

After a long and hard battle, I managed to drop it merely three order of magnitude down, and I run into some issues with the code that uses it, so I need to fix those first.

I'll try to post some ideas about how to solve the complex SELECT N+(M*N*Zm)+1 issues (where N is the number of items, M is the number of collections in each item, and Zm is the number of items in each collection in each item). (The short version, don't do this.)