Transaction when operating in two tables

Hello,

I’m reading from a table in the database to fetch some path to files to
import.

I do a loop for each path, and for each .csv file, I update another
table.

All works perfect, but it takes lot of time, 90 files and aprox.
2.000.000 rows.

After adding transactions, the speed has been great, 400% faster.

Now, as I see I have to specify the table for wich the transaction
occur, where I have to use, the firsst table (where I fetch the path to
files) or from the other one ?

I update the path file row with a timestamp, rows created/updated/… ,
so if I problem occurs with some file, I don’t want to update their
status as done nor having any new data in the other table.

for your info:

table master => has the path files
table dades => has de data
database backend => sqlite (only has only one transaction, and not per
table, per database)

thanks,

raimon

code:

def import_file_nc8_ajax

@txt_to_return = ""
@txt_rows_imported = 0

# delete all the rows in Master for nc8 ...
@result = Arxiu.find_by_sql('DELETE FROM masters')

@txt_to_return += "<p><b>Deleted all rows from NC8 table</b></p>"

@columns = Arxiu.content_columns # id, nc8, nacer


@file_paths=Arxiu.find(:all, :conditions => 'kind="nc8"')

require 'csv'


for arxiu in @file_paths

  @rows_per_file = 0

begin

Master.transaction do

CSV.open(arxiu.file_path, "r") do |row|

    @data_new=Master.new
    @data_new.nc8 = row[0]
    @data_new.nacer = row[1]
    @data_new.save

    @txt_rows_imported += 1
    @rows_per_file += 1

  end    # CSV.open... do

end # transaction

rescue

end # begin

 arxiu.update_last=Time.now()
 arxiu.update_rows = @rows_per_file
 arxiu.save

end

  @txt_to_return += "<p><b>Successfully created " +

@txt_rows_imported.to_s + " rows

"
@txt_to_return += "

from " + @file_paths.size.to_s + " files

"
render(:text => @txt_to_return)

end # import_file_nc8_ajax


ah, the question …

the question is, where I have to put the

begin

Master.transaction do

and if I have to use the Master table or the Dada table

if there are erros, the 99.99% will be in the Dada table, related to the
imported data from the .csv file …

thanks,

rai

On 10 Dec 2007, at 09:40, Raimon Fs wrote:

All works perfect, but it takes lot of time, 90 files and aprox.
2.000.000 rows.

After adding transactions, the speed has been great, 400% faster.

Now, as I see I have to specify the table for wich the transaction
occur, where I have to use, the firsst table (where I fetch the path
to
files) or from the other one ?

In your case, it doesn’t matter. In theory, different models can be
using different connections, so Master.trasaction means ‘start a
transaction on the connection that Master users’
Since you’re not using different dbs, it doesn’t matter. However you
should note that rails doesn’t allow you to write transactions that
cover multiple databases. In your particular case the speed game
probably comes from not having to flush to disk after each row update,
so if all you care about is the speed then putting the transaction on
Master would be the write thing since that’s where all the writes are.

Fred

Frederick C. wrote:

On 10 Dec 2007, at 09:40, Raimon Fs wrote:

All works perfect, but it takes lot of time, 90 files and aprox.
2.000.000 rows.

After adding transactions, the speed has been great, 400% faster.

Now, as I see I have to specify the table for wich the transaction
occur, where I have to use, the firsst table (where I fetch the path
to
files) or from the other one ?

In your case, it doesn’t matter. In theory, different models can be
using different connections, so Master.trasaction means ‘start a
transaction on the connection that Master users’
Since you’re not using different dbs, it doesn’t matter. However you
should note that rails doesn’t allow you to write transactions that
cover multiple databases. In your particular case the speed game
probably comes from not having to flush to disk after each row update,
so if all you care about is the speed then putting the transaction on
Master would be the write thing since that’s where all the writes are.

Fred

ok, thanks.

here I’m using only one connection for all models, and just only one
database, I wasn’t aware that Table.transaction opens a transaction for
the connection of that table, I don’t know wahy I thought it had
something about the table that opened it, now I see that is a
transaction for the connection that this table/model uses.

thanks for the clarification

regards,

rai

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs