SQL performance..

Admiral Huddy

Admiral Huddy

Admiral Huddy

Man of Honour
Joined
17 Feb 2003
Posts
29,640
Location
Chelmsford
a chap I've been working with never uses Inner, or outer joins, let, right joins, but instead relies on self joins.. for example..

<code>
SELECT column_name(s)
FROM table1 a, table1 b, table c
WHERE a.client = b.client and a.client = c.client and b.country = c.country </code>

Now I'm wondering is there a performance issue using this kind of technique?
 
Tend to use "normal" joins myself but I think SQL Server would interpret them the same anyway.

I just tried a quick query using both methods and they evaluated to near identical execution plans querying 200k records.

Others more knowledgeable may be able to offer more insight.
 
Is this on an Oracle DBMS?

It's a very dated way of doing things but still works ok on really old versions, as that's what the engine was built around back then. It's also a pain in terms of readability.
 
As above I'm pretty sure the backend creates the same execution plan or very similar either way it's just not very nice for anyone else having to work with it.
 
There should not be performance issues, but by default you are getting results based on inner joins using that syntax - unless you use (+) as well. Whilst this will still work with most oracle dbms, it it outdated and should be updated.
 
WTF LEFT join is the only join to use ;-). Best thing to do, is rewrite it and show him the performance gainz. Try and use bigger tables to exaggerate the difference more
 
swillsy said:
WTF LEFT join is the only join to use ;-). Best thing to do, is rewrite it and show him the performance gainz. Try and use bigger tables to exaggerate the difference more
Click to expand...
This explains a lot when it comes to supporting your old reports...:p
 
You must log in or register to reply here.
Back
Top Bottom