Optimizing use of database queries

Question

Is it faster to do a single database query that finds all the results you need for a page, then do several loops through it with if/else statements to extract what you want to show in each section, or to do several database queries selecting the exact data you require and then just loop through each one separately?

For instance:

@completed = HostTask.find(:all, :include => [:task], :conditions =>["host_tasks.decommission_id = ? AND host_tasks.completed IS NOT NULL", params[:id]], :order => "tasks.position")
@incomplete = HostTask.find(:all, :include => [:task], :conditions =>["host_tasks.decommission_id = ? AND host_tasks.completed IS NULL", params[:id]], :order => "tasks.position")

Then loop through each to display. Or just:

@tasks = HostTask.find(:all, :include => [:task], :conditions =>["host_tasks.decommission_id = ?", params[:id]], :order => "tasks.position")

Looping through @tasks twice and only displaying if @tasks.completed is null then not null?

Answer 1

multiple roundtrips is costly, but selecting more than the required data is costly, too. only selecting the exact data with multiple db queries and not try to use complex abnormal business logic is the preffered way in most cases but it depends on the use case ofcourse. I recommend caching, caching, caching. You can gain giant performance advantages.

Answer 2

I think it will be 'faster' to do one single query as db connection and call is expensive. Are you using an app server or some other connection pooling? that could help Depends on how much data you are bringing back too.