If you are looking to improve your database performance, you might
want to index your database, esspecially if it is large. Here are
some rake tasks that will look at your code and active record and
automatically add indexes to your devel db.
These two tasks are fairly rough drafts. I am open to any input that
would expand on what I have here.
lib/tasks/db_indexing.rake
(then run rake db_indexing:index_fks_in_model and rake
db_indexing:index_sql_calls_in_app )
------
require ''ActiveRecord''
require(File.join(RAILS_ROOT, ''config'',
''environment''))
namespace :db_indexing do
desc "This task will search text files for rails queries and,
checking for duplicates, generate sql that properly indexes these
commands"
task :index_fks_in_model do
connection = ActiveRecord::Base.connection
connection.tables.each do |table|
begin
table_columns = eval(table.singularize.capitalize +
".column_names")
table_columns.each do |column_string|
if column_string.include?(''_id'')
connection.execute("CREATE INDEX " + table +
"_" +
column_string + "_index" + "ON" +
"#{table}(#{column_string});")
end
end
rescue
end
end
puts "Task completed"
end
desc "This task will iterate through the objects in your model and
find out which columns end with _id and generate an sql commands to
index these columns"
task :index_sql_calls_in_app do
connection = ActiveRecord::Base.connection
controller_files = File.join("**", "*_controller.rb")
helper_files = File.join("**", "*_helper.rb")
no_of_calls = 0
array_of_files = Dir.glob(controller_files).concat(Dir.glob
(helper_files))
array_of_files.each do |file|
temp_f = File.new(file)
temp_f.each do |line|
if line.to_s.include?(":conditions")
array_of_columns_to_index = []
critical_string = line.to_s.match(''([:][c][o][n][d][i][t][i]
[o][n][s].[=][>].)\[.+\]'')[0] if
line.to_s.match(''([:][c][o][n][d][i]
[t][i][o][n][s].[=][>].)\[.+\]'')
table = line.to_s.match(''=.+'')[0]
table = table[1, table.index(''.'') - 1]
table = table.chomp.strip
temp_string = ''''
table.each_char do |char|
if char.downcase.to_s == char.to_s
temp_string = temp_string + char
else
temp_string = temp_string + ''_'' + char.downcase
end
end
table = temp_string[1, temp_string.length]
table = table.chomp.strip.downcase.pluralize
cs2 = critical_string.to_s.match(''\[.+\]'')[0] if
critical_string.to_s.match(''\[.+\]'')
if cs2
cs3 = cs2[1, cs2.index('','') - 1] if
cs2.index('','')
else
end
if cs3
cs3 = cs3[1,cs3.length - 2]
cs3.to_s.each('' '') do |tstr|
tstr = tstr.chomp.strip
tstr = tstr.downcase
if !tstr.match(''\W'') && tstr !=
''and'' && tstr != ''or'' &&
tstr != ''is'' && tstr != ''1''
&& tstr != ''null'' && tstr !=
''0'' && tstr !
= ''id''
array_of_columns_to_index << tstr
end
end
end
index_name = table.to_s + "_"
index_string2 = ''''
if array_of_columns_to_index.length > 0
array_of_columns_to_index.each do |column_to_index|
index_name = index_name + column_to_index + "_"
index_string2 = index_string2 + column_to_index +
'',''
end
index_string2 = index_string2[0,index_string2.length - 1]
index_name = index_name + ''index''
sql_command = "CREATE INDEX " + index_name + " ON
" +
table.to_s + "(" + index_string2 + ")" + ";"
begin
puts sql_command
connection.execute(sql_command)
rescue StandardError =>e
puts "ERROR"
# if !e.clean_message.to_s.include?("42000")
puts e.clean_message.to_s
puts e.backtrace
# end
end
end
end
end
end
puts "Task completed"
end
end
------
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
[:][c][o][n][d][i][t][i][o][n][s].[=][>]. Er, what? Why not just match for :conditions? ----- Ryan Bigg Freelancer http://frozenplague.net On 06/01/2009, at 10:03 AM, Ben wrote:> > If you are looking to improve your database performance, you might > want to index your database, esspecially if it is large. Here are > some rake tasks that will look at your code and active record and > automatically add indexes to your devel db. > > These two tasks are fairly rough drafts. I am open to any input that > would expand on what I have here. > > lib/tasks/db_indexing.rake > (then run rake db_indexing:index_fks_in_model and rake > db_indexing:index_sql_calls_in_app ) > ------ > > require ''ActiveRecord'' > require(File.join(RAILS_ROOT, ''config'', ''environment'')) > > namespace :db_indexing do > desc "This task will search text files for rails queries and, > checking for duplicates, generate sql that properly indexes these > commands" > task :index_fks_in_model do > connection = ActiveRecord::Base.connection > connection.tables.each do |table| > begin > table_columns = eval(table.singularize.capitalize + > ".column_names") > table_columns.each do |column_string| > if column_string.include?(''_id'') > connection.execute("CREATE INDEX " + table + "_" + > column_string + "_index" + "ON" + "#{table}(#{column_string});") > end > end > rescue > end > end > puts "Task completed" > end > > desc "This task will iterate through the objects in your model and > find out which columns end with _id and generate an sql commands to > index these columns" > task :index_sql_calls_in_app do > connection = ActiveRecord::Base.connection > controller_files = File.join("**", "*_controller.rb") > helper_files = File.join("**", "*_helper.rb") > no_of_calls = 0 > array_of_files = Dir.glob(controller_files).concat(Dir.glob > (helper_files)) > array_of_files.each do |file| > temp_f = File.new(file) > temp_f.each do |line| > if line.to_s.include?(":conditions") > array_of_columns_to_index = [] > critical_string = line.to_s.match(''([:][c][o][n][d][i][t][i] > [o][n][s].[=][>].)\[.+\]'')[0] if line.to_s.match(''([:][c][o][n][d][i] > [t][i][o][n][s].[=][>].)\[.+\]'') > table = line.to_s.match(''=.+'')[0] > table = table[1, table.index(''.'') - 1] > table = table.chomp.strip > temp_string = '''' > table.each_char do |char| > if char.downcase.to_s == char.to_s > temp_string = temp_string + char > else > temp_string = temp_string + ''_'' + char.downcase > end > end > table = temp_string[1, temp_string.length] > table = table.chomp.strip.downcase.pluralize > cs2 = critical_string.to_s.match(''\[.+\]'')[0] if > critical_string.to_s.match(''\[.+\]'') > if cs2 > cs3 = cs2[1, cs2.index('','') - 1] if cs2.index('','') > else > end > if cs3 > cs3 = cs3[1,cs3.length - 2] > cs3.to_s.each('' '') do |tstr| > tstr = tstr.chomp.strip > tstr = tstr.downcase > if !tstr.match(''\W'') && tstr != ''and'' && tstr != ''or'' && > tstr != ''is'' && tstr != ''1'' && tstr != ''null'' && tstr != ''0'' && tstr ! > = ''id'' > array_of_columns_to_index << tstr > end > end > end > index_name = table.to_s + "_" > index_string2 = '''' > if array_of_columns_to_index.length > 0 > array_of_columns_to_index.each do |column_to_index| > index_name = index_name + column_to_index + "_" > index_string2 = index_string2 + column_to_index + '','' > end > index_string2 = index_string2[0,index_string2.length - 1] > index_name = index_name + ''index'' > sql_command = "CREATE INDEX " + index_name + " ON " + > table.to_s + "(" + index_string2 + ")" + ";" > > > begin > puts sql_command > connection.execute(sql_command) > rescue StandardError =>e > puts "ERROR" > # if !e.clean_message.to_s.include?("42000") > puts e.clean_message.to_s > puts e.backtrace > # end > end > end > end > > end > end > puts "Task completed" > end > > > end > > > ------ > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
If you have a suggestion for the replacing the line of code, I''ll test it. Regular expressions aren''t my forte. On Jan 5, 4:07 pm, Ryan Bigg <radarliste...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> [:][c][o][n][d][i][t][i][o][n][s].[=][>]. > > Er, what? Why not just match for :conditions? > ----- > Ryan Bigg > Freelancerhttp://frozenplague.net > > On 06/01/2009, at 10:03 AM, Ben wrote: > > > > > If you are looking to improve your database performance, you might > > want to index your database, esspecially if it is large. Here are > > some rake tasks that will look at your code and active record and > > automatically add indexes to your devel db. > > > These two tasks are fairly rough drafts. I am open to any input that > > would expand on what I have here. > > > lib/tasks/db_indexing.rake > > (then run rake db_indexing:index_fks_in_model and rake > > db_indexing:index_sql_calls_in_app ) > > ------ > > > require ''ActiveRecord'' > > require(File.join(RAILS_ROOT, ''config'', ''environment'')) > > > namespace :db_indexing do > > desc "This task will search text files for rails queries and, > > checking for duplicates, generate sql that properly indexes these > > commands" > > task :index_fks_in_model do > > connection = ActiveRecord::Base.connection > > connection.tables.each do |table| > > begin > > table_columns = eval(table.singularize.capitalize + > > ".column_names") > > table_columns.each do |column_string| > > if column_string.include?(''_id'') > > connection.execute("CREATE INDEX " + table + "_" + > > column_string + "_index" + "ON" + "#{table}(#{column_string});") > > end > > end > > rescue > > end > > end > > puts "Task completed" > > end > > > desc "This task will iterate through the objects in your model and > > find out which columns end with _id and generate an sql commands to > > index these columns" > > task :index_sql_calls_in_app do > > connection = ActiveRecord::Base.connection > > controller_files = File.join("**", "*_controller.rb") > > helper_files = File.join("**", "*_helper.rb") > > no_of_calls = 0 > > array_of_files = Dir.glob(controller_files).concat(Dir.glob > > (helper_files)) > > array_of_files.each do |file| > > temp_f = File.new(file) > > temp_f.each do |line| > > if line.to_s.include?(":conditions") > > array_of_columns_to_index = [] > > critical_string = line.to_s.match(''([:][c][o][n][d][i][t][i] > > [o][n][s].[=][>].)\[.+\]'')[0] if line.to_s.match(''([:][c][o][n][d][i] > > [t][i][o][n][s].[=][>].)\[.+\]'') > > table = line.to_s.match(''=.+'')[0] > > table = table[1, table.index(''.'') - 1] > > table = table.chomp.strip > > temp_string = '''' > > table.each_char do |char| > > if char.downcase.to_s == char.to_s > > temp_string = temp_string + char > > else > > temp_string = temp_string + ''_'' + char.downcase > > end > > end > > table = temp_string[1, temp_string.length] > > table = table.chomp.strip.downcase.pluralize > > cs2 = critical_string.to_s.match(''\[.+\]'')[0] if > > critical_string.to_s.match(''\[.+\]'') > > if cs2 > > cs3 = cs2[1, cs2.index('','') - 1] if cs2.index('','') > > else > > end > > if cs3 > > cs3 = cs3[1,cs3.length - 2] > > cs3.to_s.each('' '') do |tstr| > > tstr = tstr.chomp.strip > > tstr = tstr.downcase > > if !tstr.match(''\W'') && tstr != ''and'' && tstr != ''or'' && > > tstr != ''is'' && tstr != ''1'' && tstr != ''null'' && tstr != ''0'' && tstr ! > > = ''id'' > > array_of_columns_to_index << tstr > > end > > end > > end > > index_name = table.to_s + "_" > > index_string2 = '''' > > if array_of_columns_to_index.length > 0 > > array_of_columns_to_index.each do |column_to_index| > > index_name = index_name + column_to_index + "_" > > index_string2 = index_string2 + column_to_index + '','' > > end > > index_string2 = index_string2[0,index_string2.length - 1] > > index_name = index_name + ''index'' > > sql_command = "CREATE INDEX " + index_name + " ON " + > > table.to_s + "(" + index_string2 + ")" + ";" > > > begin > > puts sql_command > > connection.execute(sql_command) > > rescue StandardError =>e > > puts "ERROR" > > # if !e.clean_message.to_s.include?("42000") > > puts e.clean_message.to_s > > puts e.backtrace > > # end > > end > > end > > end > > > end > > end > > puts "Task completed" > > end > > > end > > > --------~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---