Rails , db performance tip

Just a quick tip for those who have banged heads with sorting issues. In particular when working accross database technologies, you may end up getting bitten by default behavior differences.

So here’s something to keep in mind, Indexes are to Sorting as Hardware is to Software.. You can define default order behavior if you have the right index.

How did this come up recently for me? Sorting differences on a multilayer ed hierarchy of data , never noticed anything until deploying to production which used PGSQL instead of MYSQL

Sqlite ..

I love it, but have learned over time, repeatedly, in spite of my self, that I must move out of it as soon as I can.

How to connect Ruby 1.9.1, Rails 2.3.5 to SQLServer

annoyingly enough , i had figured out the concept. Use DBI ODBC instead of DBD ADO… now ADO is going through some tug of war bullshit , which eventually will fade out when Microsoft gets its paws untangled

in the meanwhile , there towards the bottom is the answer

updated link

this is signigicant because the dbi gem in Ruby 1.9.1 is already on another version greater than the one desperately needed to make things work.

It should be noted that this version of the adapter was developed using both the ancient 0.0.23 version of DBI up to the current stable release of 0.4.1. Note that DBI 0.4.1 is the minimal for ruby 1.9 compatibility. Because later versions of DBI will be changing many things, IT IS HIGHLY NECESSARY that you max your install to version 0.4.1 which the examples below show. For the time being we are not supporting DBI versions higher than 0.4.1 this they settle down on new internal implementations.

Performance is seemingly OK

AMAOF

One quick tip for db performance on Rails

Make sure you add indexes to your migrations. Most of the time db performance problems are SQL related. Most of the time the SQL is fairly innocuous. A lot of the time though, in loading the objects the SQL will work best if the candidate keys on the many side of the equation are indexed columns. Create models through generators and either add the indexes to the added migrations or add new migrations to make sure that not only the id primary key indexes are created, but the supporting indexes (like user_id, tagging_id, rateable_asset_id) are also indexed.

Finally, note that cardinality and composition is important in indexes:
Given an index on column A, index on B, index on C
And a query which searches for conditions on A,B,C , then index A is being used most probably alone. If Index A is a low cardinality index (like a flag) then the query is going to scan the whole index which is just as bad as a full table read. The execution plan will say Index read but performance will be awful . B or C might be even faster choices than A, but by having query look at A and an index on A your query is going to go with the slow performing read.

So make sure your indexes are useful, if B gives better spread than A, and your query is asking for conditons on A,B,C then make your index a composite B,A,C order and the SQL will fly.

All of this of course should be transparent to the app side in rails. All of this can be handled via migrations, and will work equally well on all DB choices Rails provides.