Hi,
I start my adventure with rails and I meet seriously problem.
When I try save data to DB - save operation take ~0.5 second per row.
I generate 5000 records with 6 numbers and later every position from
my array is save to db.
My table schema is :
CREATE TABLE "results" ("id" INTEGER PRIMARY KEY
AUTOINCREMENT NOT
NULL, "restime" date, "no1" byte, "no2" byte,
"no3" byte, "no4" byte,
"no5" byte, "no6" byte, "created_at" datetime,
"updated_at" datetime);
This is controller
class AllresultsController < ApplicationController
def index
@result = Result.new #create model class
@results = @result.get_all #get 5000 instances result class as an
array
timest = Time.now
puts "start saving to db " + timest.to_s
@results.each do |res| #for every generated result save it to
db
print "Saving " + res.to_s
sstart = Time.now
res.save
puts " (#{(Time.now - sstart).to_s}s)"
end
puts "finish in " + (Time.now - timest).to_s + "seconds"
end
end
example output on server console is :
Saving 2002-09-04 - 2, 9, 11, 22, 35, 45 (0.481178s)
Saving 2002-08-31 - 3, 18, 26, 29, 45, 48 (0.455533s)
Saving 2002-09-11 - 7, 16, 17, 25, 38, 44 (0.464589s)
Saving 2002-09-07 - 4, 5, 10, 17, 30, 40 (0.472471s)
but in db log i see that save operation do not take that amount of
time :
[4;35;1mResult Create (0.7ms)[0m [0mINSERT INTO
"results" ("created_at", "updated_at",
"no1", "restime", "no2", "no3",
"no4", "no5", "no6") VALUES(''2009-10-29
21:57:08'', ''2009-10-29
21:57:08'', 1, ''2004-01-21'', 6, 28, 38, 39, 48)[0m
[4;36;1mResult Create (0.8ms)[0m [0;1mINSERT INTO
"results" ("created_at", "updated_at",
"no1", "restime", "no2", "no3",
"no4", "no5", "no6") VALUES(''2009-10-29
21:57:09'', ''2009-10-29
21:57:09'', 6, ''2004-01-24'', 13, 16, 20, 36, 44)[0m
[4;35;1mResult Create (0.8ms)[0m [0mINSERT INTO
"results" ("created_at", "updated_at",
"no1", "restime", "no2", "no3",
"no4", "no5", "no6") VALUES(''2009-10-29
21:57:09'', ''2009-10-29
21:57:09'', 1, ''2004-01-28'', 3, 11, 17, 24, 35)[0m
Finally save all records gets :
"finish in 2237.331956seconds" ~= 37 minutes ;/
I use vesry fast db - sqlite3. I''m try do this on webrick server.
My rails version is : 2.3.4
My ruby version is : ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64-
linux]
Why it is so slow? What I must do to made this faster?
Thanks
Grzegorz
Wow I have basically the same setup and I''m not experiencing that. I''ll set up a mini project with exactly the same db schema and code that you''re using and see what happens, maybe I''ll see the problem. It''ll take a little while but I''ll get back to you with the results. On Thu, Oct 29, 2009 at 6:43 PM, ggolebio <ggolebio-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hi, > > I start my adventure with rails and I meet seriously problem. > When I try save data to DB - save operation take ~0.5 second per row. > > I generate 5000 records with 6 numbers and later every position from > my array is save to db. > > My table schema is : > CREATE TABLE "results" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT > NULL, "restime" date, "no1" byte, "no2" byte, "no3" byte, "no4" byte, > "no5" byte, "no6" byte, "created_at" datetime, "updated_at" datetime); > > > This is controller > > class AllresultsController < ApplicationController > def index > @result = Result.new #create model class > @results = @result.get_all #get 5000 instances result class as an > array > > timest = Time.now > puts "start saving to db " + timest.to_s > > @results.each do |res| #for every generated result save it to > db > print "Saving " + res.to_s > sstart = Time.now > res.save > puts " (#{(Time.now - sstart).to_s}s)" > end > puts "finish in " + (Time.now - timest).to_s + "seconds" > end > end > > > example output on server console is : > Saving 2002-09-04 - 2, 9, 11, 22, 35, 45 (0.481178s) > Saving 2002-08-31 - 3, 18, 26, 29, 45, 48 (0.455533s) > Saving 2002-09-11 - 7, 16, 17, 25, 38, 44 (0.464589s) > Saving 2002-09-07 - 4, 5, 10, 17, 30, 40 (0.472471s) > > but in db log i see that save operation do not take that amount of > time : > [4;35;1mResult Create (0.7ms)[0m [0mINSERT INTO > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:08'', ''2009-10-29 > 21:57:08'', 1, ''2004-01-21'', 6, 28, 38, 39, 48)[0m > [4;36;1mResult Create (0.8ms)[0m [0;1mINSERT INTO > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > 21:57:09'', 6, ''2004-01-24'', 13, 16, 20, 36, 44)[0m > [4;35;1mResult Create (0.8ms)[0m [0mINSERT INTO > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > 21:57:09'', 1, ''2004-01-28'', 3, 11, 17, 24, 35)[0m > > Finally save all records gets : > "finish in 2237.331956seconds" ~= 37 minutes ;/ > I use vesry fast db - sqlite3. I''m try do this on webrick server. > My rails version is : 2.3.4 > My ruby version is : ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64- > linux] > Why it is so slow? What I must do to made this faster? > > Thanks > Grzegorz > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks. I have hope that finally it''ll be something wrong in my configuration. On 30 Paź, 15:53, Jillian Galloway <jwa...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Wow I have basically the same setup and I''m not experiencing that. > I''ll set up a mini project with exactly the same db schema and code that > you''re using and see what happens, maybe I''ll see the problem. > > It''ll take a little while but I''ll get back to you with the results. > > On Thu, Oct 29, 2009 at 6:43 PM, ggolebio <ggole...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Hi, > > > I start my adventure with rails and I meet seriously problem. > > When I try save data to DB - save operation take ~0.5 second per row. > > > I generate 5000 records with 6 numbers and later every position from > > my array is save to db. > > > My table schema is : > > CREATE TABLE "results" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT > > NULL, "restime" date, "no1" byte, "no2" byte, "no3" byte, "no4" byte, > > "no5" byte, "no6" byte, "created_at" datetime, "updated_at" datetime); > > > This is controller > > > class AllresultsController < ApplicationController > > def index > > @result = Result.new #create model class > > @results = @result.get_all #get 5000 instances result class as an > > array > > > timest = Time.now > > puts "start saving to db " + timest.to_s > > > -04Gazou6+pmN7sh4/YkoqQ@public.gmane.org do |res| #for every generated result save it to > > db > > print "Saving " + res.to_s > > sstart = Time.now > > res.save > > puts " (#{(Time.now - sstart).to_s}s)" > > end > > puts "finish in " + (Time.now - timest).to_s + "seconds" > > end > > end > > > example output on server console is : > > Saving 2002-09-04 - 2, 9, 11, 22, 35, 45 (0.481178s) > > Saving 2002-08-31 - 3, 18, 26, 29, 45, 48 (0.455533s) > > Saving 2002-09-11 - 7, 16, 17, 25, 38, 44 (0.464589s) > > Saving 2002-09-07 - 4, 5, 10, 17, 30, 40 (0.472471s) > > > but in db log i see that save operation do not take that amount of > > time : > > [4;35;1mResult Create (0.7ms)[0m [0mINSERT INTO > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:08'', ''2009-10-29 > > 21:57:08'', 1, ''2004-01-21'', 6, 28, 38, 39, 48)[0m > > [4;36;1mResult Create (0.8ms)[0m [0;1mINSERT INTO > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > > 21:57:09'', 6, ''2004-01-24'', 13, 16, 20, 36, 44)[0m > > [4;35;1mResult Create (0.8ms)[0m [0mINSERT INTO > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > > 21:57:09'', 1, ''2004-01-28'', 3, 11, 17, 24, 35)[0m > > > Finally save all records gets : > > "finish in 2237.331956seconds" ~= 37 minutes ;/ > > I use vesry fast db - sqlite3. I''m try do this on webrick server. > > My rails version is : 2.3.4 > > My ruby version is : ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64- > > linux] > > Why it is so slow? What I must do to made this faster? > > > Thanks > > Grzegorz
Additionally I check it with sqlite2 and behaviour is similar. Save one row operation is usual little faster and takes ~0.35sec per row but insert into db is little slower ~2ms per row. Finally performance for sqlite2/3 is horrible. On 30 Paź, 22:34, ggolebio <ggole...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks. I have hope that finally it''ll be something wrong in my > configuration. > > On 30 Paź, 15:53, Jillian Galloway <jwa...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Wow I have basically the same setup and I''m not experiencing that. > > I''ll set up a mini project with exactly the same db schema and code that > > you''re using and see what happens, maybe I''ll see the problem. > > > It''ll take a little while but I''ll get back to you with the results. > > > On Thu, Oct 29, 2009 at 6:43 PM, ggolebio <ggole...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > Hi, > > > > I start my adventure with rails and I meet seriously problem. > > > When I try save data to DB - save operation take ~0.5 second per row. > > > > I generate 5000 records with 6 numbers and later every position from > > > my array is save to db. > > > > My table schema is : > > > CREATE TABLE "results" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT > > > NULL, "restime" date, "no1" byte, "no2" byte, "no3" byte, "no4" byte, > > > "no5" byte, "no6" byte, "created_at" datetime, "updated_at" datetime); > > > > This is controller > > > > class AllresultsController < ApplicationController > > > def index > > > @result = Result.new #create model class > > > @results = @result.get_all #get 5000 instances result class as an > > > array > > > > timest = Time.now > > > puts "start saving to db " + timest.to_s > > > > -04Gazou6+pmN7sh4/YkoqQ@public.gmane.org do |res| #for every generated result save it to > > > db > > > print "Saving " + res.to_s > > > sstart = Time.now > > > res.save > > > puts " (#{(Time.now - sstart).to_s}s)" > > > end > > > puts "finish in " + (Time.now - timest).to_s + "seconds" > > > end > > > end > > > > example output on server console is : > > > Saving 2002-09-04 - 2, 9, 11, 22, 35, 45 (0.481178s) > > > Saving 2002-08-31 - 3, 18, 26, 29, 45, 48 (0.455533s) > > > Saving 2002-09-11 - 7, 16, 17, 25, 38, 44 (0.464589s) > > > Saving 2002-09-07 - 4, 5, 10, 17, 30, 40 (0.472471s) > > > > but in db log i see that save operation do not take that amount of > > > time : > > > [4;35;1mResult Create (0.7ms)[0m [0mINSERT INTO > > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:08'', ''2009-10-29 > > > 21:57:08'', 1, ''2004-01-21'', 6, 28, 38, 39, 48)[0m > > > [4;36;1mResult Create (0.8ms)[0m [0;1mINSERT INTO > > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > > > 21:57:09'', 6, ''2004-01-24'', 13, 16, 20, 36, 44)[0m > > > [4;35;1mResult Create (0.8ms)[0m [0mINSERT INTO > > > "results" ("created_at", "updated_at", "no1", "restime", "no2", "no3", > > > "no4", "no5", "no6") VALUES(''2009-10-29 21:57:09'', ''2009-10-29 > > > 21:57:09'', 1, ''2004-01-28'', 3, 11, 17, 24, 35)[0m > > > > Finally save all records gets : > > > "finish in 2237.331956seconds" ~= 37 minutes ;/ > > > I use vesry fast db - sqlite3. I''m try do this on webrick server. > > > My rails version is : 2.3.4 > > > My ruby version is : ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64- > > > linux] > > > Why it is so slow? What I must do to made this faster? > > > > Thanks > > > Grzegorz