rails gotchas: nested transactions

So I've been adding some new plugins for testing that I've never tried before, in this case: shoulda and stump, and found that stump just isn't playing nice with SQLite :P

The problem only occurred when I tried to declare a proxy! function on an existing model. ok, I didn't get around to trying everything, so maybe it occurs on other things too, but it seemed to be happy with stub and mock.

The code would simply fail with the following error:

SQLite3::SQLException: cannot start a transaction within a transaction
<insert useless backtrace here>

It's been driving me crazy for the last few days trying all sorts of ways to get it to work. I almost abandoned transactional fixtures entirely before I finally found this snippet at the very *bottom* of the ActiveRecord::Transactions page in APIdock

"Most databases don’t support true nested transactions. At the time of writing, the only database that we’re aware of that supports true nested transactions, is MS-SQL."

So the problem is not transactions... but SQLite's lack of support of proper nested transactions.

The solution: Install MySQL.

It's annoying to have to go back to creating a MySQL db/user and granting rights before I can run my tests - but if it makes the test code work, then it's worth that extra step.

SQLite is nice, but it's ability to blow away the db by just doing a quick rm isn't worth not being able to properly test my code. Besides - the real db is on MySQL anyway, so I figure I might as well.

Performance tips > indexing matters

As I've mentioned in a previous post, I've been doing a lot of performance tweaking recently. By far and away the biggest gains I've garnered have been by tweaking the SQL/DB in various ways. One of the biggest things I learned was to use indexes appropriately. This means two things:

1. Put an index on anything important
2. Get the field order right

So what's important?

From the perspective of an index, immportant means anything that you will need to fetch repeatedly - especially if that fetch is in a tight loop. This is one place where RubyProf is your friend. You can also take a look at the SQL queries scrolling up the screen while watching the console output to get a rough idea of the most important queries.

What about the field order?

Indexes work better if you put the "most discerning" fields before the less-discerning ones. As an example, suppose you have a (completely spurious) table as follows:

owner_name	pet_name	toy_name
Bob	Fido	Bone
Bob	Fido	Rubber chicken
Bob	Whiskers	Jingly ball
Bob	Whiskers	paper on string
Jane	Woofy	chew toy
Jane	Woofy	bouncy ball
Jane	Kitty	paper bag
Jane	Kitty	laser pointer

Assume for some reason it's important to have an index on all three fields above. The field that is most unique is the "toy_name" - doing a lookup on that field will get the best bang for your buck, so it should come first in your index, followed by the next-most discerning field. In the example data above it's the pet_name, but maybe you know your intended audience a bit better. If you know that a lot of people are calling their dogs 'fido' and their cats 'kitty', you might index on the owner_name. The most likely circumstance is that you'll have the owners listed in a users list and there's actually an owner_id - in which case you'd definitely set that as the second key for the index.

Thus you'd end up with:
add_index :pet_toys, [:toy_name, :owner_id, :pet_name], :name => :idx_pettoys_by_name_oid_pet

Is it really that important?

The contract I'm currently working on requires us to display reports with tens of thousands of orders and trades in them, cross-referenced against multiple calculated benchmark values. It's imperative that the indexes are set up to pull that data out of the db in the most efficient way possible.

I acheived an order of Magnitude speedup just by changing around the ordering of the indexes! This was worth it for us - YMMV