In our project, every time we get a new product from a Donor, we use InventoryNumber table to assign inventory_number to that product. InventoryNumber table has just two columns : (id, code). Code is nothing but the inventory_number that needs to be assigned to the product.

inventory_number needs to be auto-generated for every product and gets added to the InventoryNumber table once the the product is accepted by one of the admin. Also the interesting part is this inventory_number can also be entered manually by the admins while accepting the product.

Suppose we have a new product with id= 1234 and it is accepted and its inventory_number = 333 gets added to the InventoryNumber table. Looking at the inventory_number we can guess that there are 333 products that were accepted by the admin. Now lets consider that this is true.

Now suppose, a new product request arrives and admin instead of letting the auto-generated number stay with the product decides to enter manually and let's say he enters 'inventory_number' = 340 and saves the product.

Note that inventory_number needs to be unique and cannot be repeated if its already present as it used to identify the product along with its primary key. So while entering the inventory_number manually if you try to choose already taken inventory_number that will not be allowed and you will be forced to choose another unique number. Also code is in string format with size 6, So 1 is represented a "000001".

So the last 'inventory_number' = 340 and now we know that total `inventory_number'.count = 333+1 = 334. So now our auto-generation needs to consider this and generate the missing number in between these number (i.e 334,..,339) unless it again entered manually.

This auto-generation of code was done in following way:

class InventoryNumber < ActiveRecord::Base

  validates :code, presence: true, uniqueness: true

  def self.all_codes
    select("CAST(code AS integer) AS number").map(&:number)   
  end

  def self.latest_code
    select("CAST(code AS integer)").order("code desc").
      first.try(:code).try(:to_i) || 1   
  end

  def self.available_code
    code = ((1..latest_code).to_a - all_codes).first || (latest_code + 1)
    code.to_s.rjust(6, "0")   
  end 
end
  1. So we generate an array of number from 1..lastest_code which is the maximum available code in our InventoryNumber table(i.e select("CAST(code AS integer)").order("code desc"). first.try(:code).try(:to_i) || 1).
  2. Then we take intersection of all the codes in table and our array of 1 to max(code).

So this used to work fine and unless we realised that we had upto 50000 of inventory_numbers . Then we did above steps and it took like upto 30-40 seconds.

Then we decided to optimize this as we needed to have a faster response time whenever a new request for available code arrived. So we optimized it as follows

class InventoryNumber < ActiveRecord::Base

  validates :code, presence: true, uniqueness: true

  def self.next_code
    number = missing_code > 0 ? missing_code : (max_code + 1)
    number.to_s.rjust(6, "0")
  end

  def self.missing_code
    sql_for_missing_code = sanitize_sql_array([
      "SELECT s.i AS first_missing_code
        FROM generate_series(1,?) s(i)
        WHERE NOT EXISTS (SELECT 1 FROM inventory_numbers WHERE CAST(code AS INTEGER) = s.i)
        ORDER BY first_missing_code
        LIMIT 1", count])
    missing_number = ActiveRecord::Base.connection.exec_query(sql_for_missing_code).first || {}
    (missing_number["first_missing_code"] || 0).to_i
  end

  def self.max_code
    InventoryNumber.maximum('code').to_i || 0
  end

end

So if you noticed the code we have used sql instead of rails to obtain the next code. When we decided to optimize the old code we knew that we need to do whatever calculation is needed on db and not on rails. So we have done following things in above code for optimization.
For missing_code

  1. Use sql query instead of arrays or rails logic.
  2. So we decided ActiveRecord::Base.connection.exec_query method which executes the sql query as it is.
  3. In query we are generating the series using generate_series' from 1 to InventoryNmber.count. We are not using max(InventoryNumber) here as we know that if count ! InventoryNumber.last.code` that means there are some numbers missing in between. So we first need to find the missing number from 1 to count.
  4. generate_series acts like the array we used in our old code. It generates all number from 1 to count and then the select query does the job of find the intersection of codes in table and code in series. then we limit it to 1 to get the first such value of intersection result.

For 'next_code'

  1. We check if missing_code>0, as if we have count=last.code then missing_code = 0, if not zero use it for the product inventory number.
  2. If it is zero, then we max_code which is max(code)+1

Now this code takes 0.7 seconds or less to generate the new inventory_number. We saved 30 seconds of our user's life, even might want to because the more time you save for your user, happier he'll be using your app.

We used this code to generate InventoryNumbers you can use to do other cool stuffs like primary keys or license number or mobile number or SSID etc. Applications are enormous it just the matter of utilizing this for them.