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 </b></p>"
@txt_to_return += "<p><b>from " +
@file_paths.size.to_s + " files
</b></p>"
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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 10 Dec 2007, at 09:40, Raimon Fs wrote:> > 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 ? >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> 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 </b></p>" > @txt_to_return += "<p><b>from " + @file_paths.size.to_s + " files > </b></p>" > > 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 > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung 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. > > Fredok, 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---