Have Geek Brain

Will db update 6000 times take a few minutes?

I am writing a test program with Ruby and ActiveRecord and it reads a 6000 word document. And then I just count the words

recordWord = Word.find_by_s(word);
if (recordWord.nil?)
  recordWord = Word.new
  recordWord.s = word
end
if recordWord.count.nil?
  recordWord.count = 1
else
  recordWord.count += 1
end
recordWord.save

and so this part of the loop goes through 6000 times ... and it takes a few minutes to work at least using sqlite3. This is normal? I was expecting this to work within seconds ... can MySQL speed it up a lot?

+1


a source to share


7 replies


Take a look at AR: Extensions to handle volumetric inserts.



http://rubypond.com/articles/2008/06/18/bulk-insertion-of-data-with-activerecord/

+5


a source


With 6000 calls to write to the database, you will see performance issues. I would store the various labels in memory and store them in the database once at the end, not 6000 times along the way.



+13


a source


I wrote a quick code in perl that just does:

  • Create database
  • Insert a record containing only one integer
  • Check out the most recent entry and make sure it returns what it inserted.

And he does steps # 2 and # 3 6000 times. This is obviously a significantly lighter workload than having the entire object-relational bridge. This trivial SQLite case still took 17 seconds to execute, so your desire to have it takes "a couple of seconds" is not realistic on "traditional hardware".

Using the monitor, I verified that it was primarily disk activity that was slowing it down. Based on this, if for some reason you really need a database to behave this fast, I suggest one of two options:

  • Do what people have suggested and found around this requirement.
  • Try to buy some solid state drives.

I think # 1 is a good way to start :)

the code:

#!/usr/bin/perl

use warnings;
use strict;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=/tmp/dbfile', '', '');

create_database($dbh);
insert_data($dbh);

sub insert_data {
  my ($dbh) = @_;

  my $insert_sql = "INSERT INTO test_table (test_data) values (?)";
  my $retrieve_sql = "SELECT test_data FROM test_table WHERE test_data = ?";

  my $insert_sth = $dbh->prepare($insert_sql);
  my $retrieve_sth = $dbh->prepare($retrieve_sql);

  my $i = 0;
  while (++$i < 6000) {
     $insert_sth->execute(($i));
     $retrieve_sth->execute(($i));

     my $hash_ref = $retrieve_sth->fetchrow_hashref;

     die "bad data!" unless $hash_ref->{'test_data'} == $i;
  }
}

sub create_database {
   my ($dbh) = @_;

   my $status = $dbh->do("DROP TABLE test_table");
   # return error status if CREATE resulted in error
   if (!defined $status) {
     print "DROP TABLE failed";
   }

   my $create_statement = "CREATE TABLE test_table (id INTEGER PRIMARY KEY AUTOINCREMENT, \n";
   $create_statement .= "test_data varchar(255)\n";
   $create_statement .= ");";

   $status = $dbh->do($create_statement);

   # return error status if CREATE resulted in error
   if (!defined $status) {
     die "CREATE failed";
   }
}
+2


a source


What kind of database connection are you using? Some databases allow you to connect directly and then use a TCP network connection that goes through the network stack. In other words, if you make an internet connection and send data this way, it can slow things down.

Another way to improve the performance of a database connection is to group SQL statements into a single command.

For example, creating one 6000 line SQL statement that looks like this

"update words set count = count + 1 where word = 'the'
update words set count = count + 1 where word = 'in'
...
update words set count = count + 1 where word = 'copacetic'"

and run it as one command, the performance will be much better. MySQL has a package size limit of 1 megabyte by default, but you can change this in the my.ini file if you like.

Since you divert your calls from the database through ActiveRecord, you don't have much control over how commands are issued, so it can be difficult to optimize your code.

Another subtle thing you could do would be to keep the word count in memory and then only insert the final total into the database and then do an update every time you come across one word. This will probably reduce the number of inserts, because if you update the word "the" every time it is a huge, huge waste. Words have a "long tail" and the most common words are extremely common than the more obscure words. Then the underlying SQL would look like this:

"update words set count = 300 where word = 'the'
update words set count = 250 where word = 'in'
...
update words set count = 1 where word = 'copacetic'"

If you are worried that you are taking up too much memory, you can count the words and flush them periodically. So, read a couple megabytes of text, then spend a few seconds updating the totals, and then update each word every time you come across it. If you want to improve performance even further, you should consider issuing SQL commands in batches directly

+1


a source


Not knowing about Ruby and Sqlite, some general hints:

create a unique index in Word.s (you didn't mention if you have)

define default value for Word.count in database (DEFAULT 1)

optimize counter assignment:

recordWord = Word.find_by_s(word);
if (recordWord.nil?)
    recordWord = Word.new
    recordWord.s = word
    recordWord.count = 1
else
    recordWord.count += 1
end
recordWord.save
+1


a source


Use BEGIN TRANSACTION before your updates, then COMMIT at the end.

+1


a source


ok, I found a general rule of thumb:

1) use a hash to save the count first, not the db 2) at the end, wrap the entire insert or updates in one transaction so it doesn't hit the db 6000 times.

0


a source






More articles:


All Articles