SQL performance..

Admiral Huddy · 14 Dec 2020 at 09:36

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?

chroniclard · 14 Dec 2020 at 09:56

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.

Mesai · 14 Dec 2020 at 10:06

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.

tom_e · 14 Dec 2020 at 10:28

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.

hood1971 · 14 Dec 2020 at 10:44

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.

Admiral Huddy · 14 Dec 2020 at 10:58

It's over a Sybase DB.. I was thinking about how the execution plan would interept this.

Thanks for trying that chroniclard.

chroniclard · 14 Dec 2020 at 11:15

Admiral Huddy said:
It's over a Sybase DB.. I was thinking about how the execution plan would interept this.

Thanks for trying that chroniclard.

Only thing to note there is I use SQL Server, might be different. Should be easy enough to run some tests on Sybase and see if it impacts anything.

swillsy · 18 Dec 2020 at 14:15

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

tom_e · 18 Dec 2020 at 14:17

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

This explains a lot when it comes to supporting your old reports...

Vince · 18 Dec 2020 at 14:25

tom_e said:
This explains a lot when it comes to supporting your old reports...

Straight in with the Burn...

swillsy · 18 Dec 2020 at 15:18

TF Tom - you mean when the reports used to work fine