SQL overhead question

[zeroace]

Hi,

While working with SQL, I needed to query through information linked by 5 tables.

I wanted to use INNER JOIN to link the tables together meanwhile my supervisor suggested using the WHERE to link the query..

Here's the two examples..

1)
SELECT * FROM tbl1 A
INNER JOIN tbl2 B ON A.id = B.id
INNER JOIN tbl3 C ON B.id2 = c.id2

OR

2)
SELECT A.*, B.*, C.*
FROM tbl1 A, tbl2 B, tbl3
WHERE A.id = B.id AND B.id2 = c.id2

My question is, which one is better suited in terms of efficiency and resources?
My supervisor says using INNER JOIN causes a lot of overhead and gets worse with larger databases needing more tables to join meanwhile (2) does the same but a lot better.
If so, when is INNER JOIN used then if it causes more overhead?

thanks in advance

 

[ameoba]

It depends on the database you're using & how it optimizes queries. In the worse case (ie - no optimization at all) not using explicit joins would result in a big-ass cartesian. Fortunately, this is a relatively simple case and any worthwhile optimizer should handle it.

When in doubt, get the DB to explain what it's doing. MySQL has the EXPLAIN command, Oracle has the "explain plan" command and most other DBs will have equivalent functionality.

 

[zeroace]

Thanks for the new command and insight ameoba.

I was wondering what A.*,B.*,etc for all the tables bringing up all unnecessary fields would do compared to explicit joins in terms of resources.

I'm going to try the explain function for MSSQL and post up what I come up with.

 

[mikeblas]

Query #1 will be faster, since #2 will not work as there's nothing in the table list named C.

I was wondering what A.*,B.*,etc for all the tables bringing up all unnecessary fields would do compared to explicit joins in terms of resources.

Again, that'll depend on the database you're using. Further, it'll depend on the indexes you have on the tables.

If your select list contains only columns covered by the index, then the database only has to read the pages in the index -- all the data needed to satisfy the query is there. But if you're asking for other columns not stored in the index, the software has to essentially join again, from the index back to the table to get the rest of the data.

The queries should retrieve only the data the applciation needs. Usually, that's not *. (And even if it is, you're better off with an explicit column list in case someone changes the table.) Not being careful with the select list leads to extra I/O for the result set, and extra I/O for the data store to retrieve the tables.

 

[braahyan]

from my experience at work, we have standards in place that actually advise against the use of the 2nd one. Our DB devs have done some research and found that in MS SQL Inner Joins and Ansi Joins worked at the same efficiency. But, that said, I'm a middle-tier dev, so i can't really go into specifics.

 

[mikeblas]

As the hand of fate has it, I can go into specifics—I work on the Query Optimizer team in the SQL Server group. If you have specific questions about what SQL Server is doing, I can probably help. Don't hesitate to ask.

In SQL Server 2005, you'll find that the ANSI syntax generally results in the same plan as FROM-list syntax. However, within the ANSI syntax, you'll notice that filters may be handled differently. That is,

SELECT A.One, A.Two, B.Three
   FROM A JOIN B ON B.Key = C.Key 
  WHERE C.Attribute = @Something

might end up optimizing differently than

SELECT A.One, A.Two, B.Three
   FROM A JOIN B ON B.Key = C.Key AND C.Attribute = @Something