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 retrieve 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
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!
Of course a JOIN will be faster.
Making 20 queries will imply:
20 times20 index seeks to find the start of the index range on items20 recordsets (each with its own metadata).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
