Allow me to set happens: My application handles gift certificates. Whenever we create cards they need to have a unique string the user may use to redeem it with. Then when someone orders our gift certificates, just like a store, we have to make lots of new card objects and store them within the DB.

Knowing that, I am attempting to observe how rapidly I'm able to have my application generate 100,000 Cards. Database expert, I'm not, and so i need anyone to explain this little phenomena: After I create 1000 Cards, it requires 5 seconds. After I create 100,000 cards it will take 500 seconds right?

Now we all know what you are attempting to see, the credit card creation method I am using, since the first assumption is it's getting reduced since it is checking the originality of a lot of cards, more as the story goes along. However I can display you my rake task

desc "Creates cards for a retailer"
task :order_cards, [:number_of_cards, :value, :retailer_name] => :environment do |t, args|
  t = Time.now
  puts "Searching for retailer"
  @retailer = Retailer.find_by_name(args[:retailer_name])
  puts "Retailer found"
  puts "Generating codes"
  value = args[:value].to_i
  number_of_cards = args[:number_of_cards].to_i
  codes = []
  top_off_codes(codes, number_of_cards)
  while codes != codes.uniq
    codes.uniq!
    top_off_codes(codes, number_of_cards)
  end
  stored_codes = Card.all.collect do |c|
    c.code
  end
  while codes != (codes - stored_codes)
    codes -= stored_codes
    top_off_codes(codes, number_of_cards)
  end
  puts "Codes are unique and generated"
  puts "Creating bundle"
  @bundle = @retailer.bundles.create!(:value => value)
  puts "Bundle created"
  puts "Creating cards"
  @bundle.transaction do
    codes.each do |code|
      @bundle.cards.create!(:code => code)
    end
  end
  puts "Cards generated in #{Time.now - t}s"
end

def top_off_codes(codes, intended_number)
  (intended_number - codes.size).times do
    codes << ReadableRandom.get(CODE_LENGTH)
  end
end

I am utilizing a jewel known as readable_random for that unique code. If you go through all that code, you'll notice that it will all it's originality testing before it ever begins creating cards. Additionally, it creates status updates towards the screen while it's running, also it always sits for some time at creating. Meanwhile it flies with the originality tests. So my question towards the stackoverflow community is: Exactly why is my database slowing down lower when i increase the cards? Why not really a linear function when it comes to time per card? I am sure the answer is easy and I am only a moron you never know nothing about data storage. And when anybody has any suggestions, how does one optimize this process, and just how fast do you consider you can get it to produce 100,000 cards?

(After I plotted out my occasions on the graph and did a fast curve fit to obtain my line formula, I calculated how lengthy it might decide to try create 100,000 cards with my current code also it states 5.5 hrs. That maybe drastically wrong, I am unsure. But when it stays at risk I curve fitted, it might be around there.)

No response to your question, but a few suggestions regarding how to result in the place faster:

  • Use Ruby's Hash to get rid of replicates - making use of your card codes as hash secrets, adding these to a hash until your hash develops towards the preferred size. You may also use class [cde] rather (however i doubt it's any faster than Hash).
  • Use bulk place in to the database, rather than number of Place queries. Most DBMS's provide the possibility: create text file with new records, and tell database to import it. Listed here are links for MySQL and PostgreSQL.

My first ideas could be around transactions - for those who have 100,000 pending changes waiting to be devoted within the transaction that will slow things lower just a little, but any decent DB should have the ability to handle that.

What DB are you currently using?

What indexes have established yourself?

Any DB optimisations, eg clustered tables/indexes.

Unsure from the Ruby transaction support - may be the @bundle.transaction line something from ActiveModel or any other library you're using?