Use MySQL's Modulo Operator to Update in Batches

Sometimes you need to roll out your nefarious plan slowly and MySQL's modulo operator can help.  Here we do the first of ten batches:

Rails ActsAsList - Initializing position Values

Recently I worked on a project that had a model acting as a tree with the children acting as a list within the scope of the child's parent. The old school ActsAsTree and ActsAsList plugins used in combination, handle this scenario nicely. There was one problem however - we had a large "tree" to load in where only some of the children were ordered under their parent. The rest of the children had a NULL value sitting in the position column. Because ActsAsList works by setting "position = position + 1 WHERE position >= x" when insert_at(x) is called this wasn't going to work with the data as is. "NULL = NULL + 1" just doesn't seem to work out too well. I decided to pre-process the data putting the children in their natural position order. In this case the natural position order would be by the provided position with a secondary sort on short_name if the position was not given. Also, when a child list contains both numeric positions and NULLs the NULL children should go to the bottom. Given the number of rows I was processing I knew this was going to take some time so I decided to see how this would perform when implemented as a MySQL stored procedure. Here is the code I came up with shown in the migration that creates the stored proc:



And here is the rake task to call it:



The stored procedure took just over a minute to do the renumbering. After implementing I decided to compare this to a straight SQL version that would be portable to different databases - but not as fast. I tried to avoid an ActiveRecord tax here and did the SQL in raw execute statements. Here is the rake task:



The final results showed the stored procedure technique to be about 5 times faster than the SQL technique.

Quicker Rails Seed Data Loading

The word is that Rails 3.0 will feature a way to load seed data. This is sure to be a handy and needed feature, however, when loading large amounts of seed data you are probably going to have to abandon ActiveRecord and / or fixture style loading in your db/seeds.rb file in order to get the kind of performance you want. Recently I set up a way to load seed data for a Rails 2.2.2 project which exploits the "LOAD DATA INFILE..." command that MySQL provides. This cut the data loading time for my particular data set from over 15 minutes (I gave up waiting) to less than 30 seconds. This technique is likely to remain relevant for your future db/seed.rb files. The following rake tasks set up my Rails application to load ".psv" and ".yml" seed data files from the db/seed directory. The ".yml" files are normal YAML fixture files and are loaded via my rake tasks using an ordinary fixture technique. The ".psv" files are pipe separated files which are loaded use the above mentioned "LOAD DATA INFILE..." command. The way I have this set up here the order of the columns in your ".psv" needs to match the column order in your database so you may want to tweak this code a bit and provide parameters to specify a different column order. In other words, YMMV, anyway, code follows:



One thing to look at carefully when using this technique is what your database product does with "empty" values in your data set. MySQL didn't want to seem to let a empty numeric value be NULL which caused me to go with some data massaging before and / or after load.

Ugly SQL Pivot Reporting Queries Saved for Posterity

Here are some pivot reporting queries stored for future reference:





For SQL help and insight I recommend this book: SQL Cookbook (Cookbooks (O'Reilly))

I Canz haz Ur Nullz at Bottom?

SQL Server when doing ORDER BY will put rows with null values in the sort column at the top - of course a simple adjustment to your ORDER BY can fix that:



Aloha on Rails is coming! I'll blog a bit more about this in the future but this should be a great conference with great sessions and Waikiki Beach just across the street!

SQL - Last Index Of lastIndexOf

Quick flush - lastIndexOf in two varieties of SQL plus using the technique to cut off the last segment in a materialized path:

Rails Reporting, Raw SQL, and ActiveRecord

Most of the time when you dump some data out using Rails you are only dumping out a page of data at a time into an XHTML representaton. This is often done with some kind of pagination and it all works pretty well and speed doesn't become an issue. When doing reporting, however, you may be providing a much larger number of records to the user in the form of an Excel document or CSV file that the user will use third party software to do number crunching on. In this case the process of Rails reconstituting all of those model objects can really slow things down and you will likely need to resort to writing raw SQL and avoiding ActiveRecord. You don't, however, have to abandon ActiveRecord completely as it has some very helpful methods for constructing the SQL queries you will issue. Some of these methods are private but you can invoke them using the send method. Here is an example that uses ActiveRecord to append where and order clauses to the manually constructed sql statement:



A few notes...When you call select_all you are returned an array of hashes with the column names as keys. If you call execute you will get an array of arrays and you will have to use column positions to index into the array (probably a bit faster than select_all on producing that record set). As of Rails 2.0 the record set that select_all returns can be used as the :collection parameter in a partial. Lastly, in addition to the :add_conditions! and :add_order! methods there is a very handy :construct_finder_sql as well which accepts the same options your would pass to a regular finder.