Which of the following SQL queries would be faster? A join on two tables or successive queries?

I have two tables here:

ITEMS
ID| DETAILS| .....| OWNER

USERS:
ID| NAME|....

Where ITEMS.OWNER = USERS.ID

I'm listing the items out with their respective owners names. For this I could use a join on both tables or I could select all the ITEMS and loop through them making a sql query to r开发者_开发问答etrieve the tuple of that itmes owner. Thats like:

1 sql with a JOIN versus 1x20 single table sql queries

Which would be a better apporach to take in terms of speed? Thanks

Of course a JOIN will be faster.

Making 20 queries will imply:

• Parsing them 20 times
• Making 20 index seeks to find the start of the index range on items
• Returning 20 recordsets (each with its own metadata).

Every query has overhead. If you can do something with one query, it's (almost) always better to do it with one query. And most database engines are smarter than you. Even if it's better to split a query in some way, the database will find out himself.

An example of overhead: if you perform 100 queries, there will be a lot more traffic between your application and your webserver.

In general, if you really want to know something about performance, benchmark the various approaches, measure the parameters you're interested in and make a decision based on the results of the becnhmark.

Good luck!

Executing a join will be much quicker as well as a better practice

I join would be a lot quicker than performing another query on the child table for each record in the parent table.

You can also enable performance data in SQL to see the results for yourself..

http://wraithnath.blogspot.com/2011/01/getting-performance-data-from-sql.html

N