| Subject | Re: left join and join 100% cpu usage |
|---|---|
| Author | Adam |
| Post date | 2006-03-31T02:11:21Z |
--- In firebird-support@yahoogroups.com, "jasajona" <jasajona@...> wrote:
to use table1 first, because you are asking it to include all records
on the left EVEN IF there is no matching record on the right.
The difference between LEFT JOIN and JOIN is about how you want to
treat those records for which there are no matches.
The rule is:
Use inner join (aka join) whereever you are not interested in
non-matched records.
In the query above, I see no reason for the first left join, because
even those records that meet the criteria will be knocked out by the
second join which is an inner join.
The next problem is that it appears that Firebird is attempting to use
a plan that is silly. Without knowing the rough record count of each
table and the number of duplicates in your foreign keys here, it is
hard to determine where it goes AWOL.
But replacing the second join with one of the following should help.
join table3 on table2.field2+0 = table3.field1
OR
join table3 on table2.field2 = table3.field1+0
Adding 0 to the field will prevent it from using a particular index
which is not useful.
Secondly, a little trick
change your select to
select first 1 *
while playing around. For simple queries, this will let it abort after
it finds one record to return.
Locate IBPlanalyzer or equivalent and examine how the query is being
executed.
Also note that your left join will force table1 to be read first. If
your where clause has no restriction on table1 that can use an index
to knock out uninteresting records, then it will need to use a natural
read of the entire table1. If your where clause references table2 or
table3, then this can really sour performance.
install classic server then kill the AWOL process. This would not
affect other users. Firebird would eventually discover the connection
was abandonned and cleanup would occur.
Of course the real problem here is that the query is being executed
the wrong way. I know this because you say that using a left join
reduces it to 10 seconds.
Adam
>First thing to understand is that a left join will force the optimiser
> Hello,
>
> I have situation:
>
> select
> ...
> from
> table1
> left join table2 on
> table1.field1 = table2.field1
> join table3 on
> table2.field2 = table3.field1
>
> This query never ends on tables with 80000 records (if I change last
> join to left join query executes in 10s)
to use table1 first, because you are asking it to include all records
on the left EVEN IF there is no matching record on the right.
The difference between LEFT JOIN and JOIN is about how you want to
treat those records for which there are no matches.
The rule is:
Use inner join (aka join) whereever you are not interested in
non-matched records.
In the query above, I see no reason for the first left join, because
even those records that meet the criteria will be knocked out by the
second join which is an inner join.
The next problem is that it appears that Firebird is attempting to use
a plan that is silly. Without knowing the rough record count of each
table and the number of duplicates in your foreign keys here, it is
hard to determine where it goes AWOL.
But replacing the second join with one of the following should help.
join table3 on table2.field2+0 = table3.field1
OR
join table3 on table2.field2 = table3.field1+0
Adding 0 to the field will prevent it from using a particular index
which is not useful.
Secondly, a little trick
change your select to
select first 1 *
while playing around. For simple queries, this will let it abort after
it finds one record to return.
Locate IBPlanalyzer or equivalent and examine how the query is being
executed.
Also note that your left join will force table1 to be read first. If
your where clause has no restriction on table1 that can use an index
to knock out uninteresting records, then it will need to use a natural
read of the entire table1. If your where clause references table2 or
table3, then this can really sour performance.
> Even if I terminate my application server still utilises 100% cpuIts a known limitation with the current architecture. You could
> endlesly. Only way is to restart firebird. I use FBSS1.5.3 WinXP. I
> wrote simplified example of my query if there is possibilitie that
> this can be bug of FB I can write all query.
install classic server then kill the AWOL process. This would not
affect other users. Firebird would eventually discover the connection
was abandonned and cleanup would occur.
Of course the real problem here is that the query is being executed
the wrong way. I know this because you say that using a left join
reduces it to 10 seconds.
Adam