Minimizing db queries

Question

I have a table containing about 30k rows of data and there's several table associations. The problem is that there's like 18 columns, and each column requires 1 db query.

Here's an example of what I'm doing

if col_num == 0
    tmp_string_text = finding_phase
elsif col_num == 1
    tmp_string_text = ""
elsif col_num == 2
    tmp_string_text = ""
elsif col_num == 3
    tmp_string_text =  finding.vuln_finding.severity
elsif col_num == 4
    tmp_string_text = finding_node_ip
elsif col_num == 5
    tmp_string_text =  finding.node.host_name
elsif col_num == 6
    tmp_string_text =  finding.node.dns_name
elsif col_num == 7
    tmp_string_text = finding.port
elsif col_num == 8
    tmp_string_text =  finding.pentest_finding.name
elsif col_num == 9
    tmp_string_text =  finding.vuln_finding.name
elsif col_num == 10
    tmp_string_text = finding.vuln_finding.description
elsif col_num == 11
    tmp_string_text = finding.vuln_finding.solution
elsif col_num == 12
    tmp_string_text = finding.additional_output
elsif col_num == 13
    tmp_string_text = finding.cve
elsif col_num == 14
    tmp_string_text = finding.node.os
elsif col_num == 15
    tmp_string_text =  finding.node.device_type
elsif col_num == 16
    tmp_string_text =  finding.node.scan_time
end

As a result, there's going to be 15 queries to the DB per 1 row. Is there any way that I can make this more efficient? This takes a REALLY long time when I have 30k rows.

You really, really need to learn about the case statement in Ruby. tmp_string_text = case (col_num) ... is the best plan for this type of code as a case statement will return a value that can be used for assignment. This is full of useless duplication. — tadman
– tadman, Commented Jan 22, 2016 at 0:14
Interesting. I used case for something similar before all of the if conditions, but sounds like I was missing something else as well. Thanks for the input! — LewlSauce
– LewlSauce, Commented Jan 22, 2016 at 1:13

Arturo Diaz · Accepted Answer · 2016-01-21 22:23:49Z

1

I suggest using the includes method to get all the columns at once and then iterate through the results from memory.

Model.includes(:column1, :column2, column3: [:subcolumn1, :subcolumn2])

answered Jan 21, 2016 at 22:23

Arturo Diaz

192 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

LewlSauce Over a year ago

If for example "Book" has many pages, and pages has many paragraphs, how does this work? Book.includes(:page_name, [:page_paragraph]) or something? Not familiar with this but thanks for your help. Really interested in how this works.

Arturo Diaz Over a year ago

To be honest I haven't tried it with more than one nested resource. But you could try: Book.includes(pages: [paragraphs: [:word]])

Arturo Diaz Over a year ago

The parameters for the includes method are the associated model you are trying to query. Internally it translates to a SQL JOIN query. Check this

tadman Over a year ago

It would help if you explained what your model looked like. If it's this hard to query your database maybe the schema is sub-optimal.

LewlSauce Over a year ago

Damn. Using includes seems to have been a great idea for reducing the number of queries, but it seems like calling associations still takes awhile. Like test = User.include(:addresses), and then looping through that with something like test.each do {|user| puts user.address.street_name}. Works but still takes awhile :(

|

rii · Accepted Answer · 2016-01-23 18:57:31Z

1

You should also add indexes for columns you are going to be querying against on a regular basis. As a rule of thumb you usually want to add indexes for:

foreign keys
columns that need to be sorted,
lookup fields
columns that are used in a GROUP BY

answered Jan 23, 2016 at 18:57

rii

1,6681 gold badge19 silver badges23 bronze badges

Collectives™ on Stack Overflow

Minimizing db queries

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related