what is more performatic? a single join or multiple selects?

Raulsassaa

In my app we have some methods where we start it by selecting a whole bunch of information (in DB), compute a lot of of things and then fire some save commands to update this new information in DB.

Well my question is what to choose when I have 2 options:

1) to query a huge SELECT command joining all needed tables all at once. Then all information I might need would be brought in this select.

2) to select the main table and then in each step of the iteration, based on what I really need, I go selecting information in other tables, passing the IDs I brought in the main table.

Why would I choose one or the other What is more performatic

I am using an ORM, which, for the moment, does not work directly with joins, so if this would be the case (joins) I would have to write a custom query to it...

hammar

As a general rule of thumb it has been my experience that a single query with multiple joins is going to perform far better than many individual select statements. Now, you might find that you write a complex query and it performs very badly, then break the query up and find that the individual select statements perform better. That doesn't mean that my rule of thumb is incorrect, it could simply mean that you haven't optimized the complex query and SQL Servers query optimizer doesn't have appropriate indexes to choose from or is choosing the wrong indexes.

There's also the issue of marshaling data. When you design an application you should always think about what's going on behind the scenes on the network. If you are developing a client server application and you are marshaling data from SQL Server to your client, then marshaling that data back from your client to SQL Server, you will take a performance hit if the data manipulation could have been done without marshaling all the data.

There's no right answer to your question. If performance is a concern and it usually is, it's best to do some testing to find out which design results in the best performance unless there are some obvious bottlenecks. An obvious bottleneck would be the situation in which you have the option of processing the data on the server or transferring the data to the client for process and back again. In that situation you won't need to test as the server only based solution will perform better. My advice to you is to spend time thinking about what all is involved in the making this piece of your application work and come up with a design that eliminates as many obvious bottlenecks as possible, then if you have several design candidates, do some performance testing to find out which one will probably yield the best performance.

Arnon Rotem Gal Oz

As Hammar mentioned if you won't perform option 2 inside a stored procedure that 1 will most likely be better

You can out perform the query optimizer if you don't have enough indexes and you have prior knowledge on what the queries would return

Note that SQL 2005 query optimizer collects statistics that you might not have (see http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx) and that you can always examine the query execution plans to help the optimizer (i.e. add indexes etc.)

On top of all that - there are many tuning tips that can make a lot of difference - you can find a lot of tips in http://www.sql-server-performance.com/ e.g. http://www.sql-server-performance.com/transact_sql.asp

Arnon

Diego Dagum

Adding my 0.05 cts, Raul:

   consider that, if the amount of retrieved records in the master table is n, and the associated tables are m, so option 2 will imply n*m+1 roundtrips to the database. Except, as Arnon suggests, you bundle all that data intensive logic in a stored procedure