Query optimisation

hessodreamy

Just wanted to ask which is the faster way of performing queries in this situation.
If I have a number of products, which may or may not be associated with a supplier, and I wanted to retrieve the product details along with details of their supplier, am I best off performing a left join between products and suppliers, or, for each product I have performing a separate query to get supplier details?
I'm going with the left join option but not 100% sure that its faster.
The tables feature about 45000 products and 80 suppliers.
A query will typically concern 4 or 5 products.

Cheers

Roger_Ramjet

1 query will (almost) always be quicker than several.

hessodreamy

Thanks. Thats what I thought. Reading my question I think I should clarify though.
Given that left joins are so much slower than inner joins. Would a single query with a left join still be quicker than multiple queries with inner joins?

Roger_Ramjet

Even if left joins are slower than inner joins, this is offset by the overhead involved in processing every query no matter what it's type and complexity.

Your script has to wait for the db engine to service the connection in an environment with multiple active connections - how many depends on how busy the site is. This could be quite a considerable parts of the total execution time on a busy server. Multiple queries means multiple waits.
SQL is interpretted at runtime. Each query has to be parsed and optimised before being executed. Again, this represents a significant part of any query execution time.
Disk I/O is always slow in comparison to memory operations. With multiple queries every record is definitely going to require disk seek and read time. With 1 query for multiple records, some records may not require their own disk I/O. This will especially apply to reading the indexes to locate the records.