Performance difference with LEFT JOIN

Classic Servoy
1

is there a performance difference between

SELECT * 
FROM client
LEFT JOIN adres ON client.client_id = adres.client_id

AND

SELECT * 
FROM client,adres
WHERE client.client_id = adres.client_id

Reason why I ask this is topic Servoy 3.1 only loading 1.000 records from dataset - Classic Servoy - Servoy Community. I am rewriting all queries into controller.loadRecords(sql,params) but LEFT JOIN is not supported.

2

Hi Tweetie,

Using a JOIN or a LEFT JOIN will/can give you 2 very different results so I think that performance is the least of your worries.
With a JOIN you only see the records that have a join to the other table.
With a LEFT JOIN you get all matching records even those that don’t join to the other table.

And as you noticed LEFT JOINs are not (yet?) supported in the loadRecords() function.
You (still) need to use the databaseManager for that and yes you are then subject to that 1000 record limit.

Hope this helps.

3

Thankx for your answer.

ROCLASI:
Using a JOIN or a LEFT JOIN will/can give you 2 very different results so I think that performance is the least of your worries.
With a JOIN you only see the records that have a join to the other table.
With a LEFT JOIN you get all matching records even those that don’t join to the other table.

Of course there is difference between JOIN and LEFT JOIN, but I usually use something like AND client.client_id = 1. So as far as I can see the results are mostly the same. So the perfomance question is still an issue.

ROCLASI:
And as you noticed LEFT JOINs are not (yet?) supported in the loadRecords() function.
You (still) need to use the databaseManager for that and yes you are then subject to that 1000 record limit.
Hope this helps.

My problem is not the 1000 record limit (Well mostly not :D ). But as I understood using loadRecords(dataset) uses 1 extra expensive query (SELECT with IN) to perform compared to loadRecords(query). So I am trying to optimize my solution. It should improve the speed…since I have been using loadRecords(dataset) almost everywhere :cry:

4

But isn’t “the results are mostly the same” a bit vague? As Robert said, there is a big difference in the result set. And of course, since its two different queries, the performance is different.

5

tweetie:
Thankx for your answer.

ROCLASI:
Using a JOIN or a LEFT JOIN will/can give you 2 very different results so I think that performance is the least of your worries.
With a JOIN you only see the records that have a join to the other table.
With a LEFT JOIN you get all matching records even those that don’t join to the other table.

Of course there is difference between JOIN and LEFT JOIN, but I usually use something like AND client.client_id = 1. So as far as I can see the results are mostly the same. So the perfomance question is still an issue.

Sorry?
Are you saying you are using a LEFT JOIN because you think it might be faster? Even though your results are ‘mostly the the same’ ??
I guess it depends on your solution but as a developer (and user) I like to get the correct results of a query, not something that is ‘somewhat’ like the correct result.

Anyway as far as I know a JOIN is faster than a LEFT JOIN.

Hope this helps.

6

patrick:
But isn’t “the results are mostly the same” a bit vague? As Robert said, there is a big difference in the result set. And of course, since its two different queries, the performance is different.

I am aware of the differences. But the queries I am trying to optimize returns the same result. The examples I am using in this topic are just for illustration and will in this case result in differente sets.

In some old queries I might have been using LEFT JOIN and JOIN incorrectly in the past. So that’s why I am optimizing the stuff…and the queries where the result are the same I was wondering which one is quicker.
If you use

SELECT client.client_id
FROM client,adres
WHERE client.client_id = adres.client_id
AND adres.omschrijving = 'blahblah'

or

SELECT client.client_id
FROM client
LEFT JOIN adres ON client.client_id = adres.client_id
WHERE adres.omschrijving = 'blahblah'

the sets will be the same

I am sorry if I am confusing stuff…but anyway Roclasi gave my a good answer…so my quest for knowledge is fullfilled :D

7

Since loadRecords(query) only supports inner joins, your decision is set whatever the performance difference might be (honestly I don’t know which one is faster) :wink: