Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Update with join
Tue, Dec 15 2020 2:45 AMPermanent Link

Yusuf Zorlu

MicrotronX

Any chance that we can get an UPDATE with JOIN for ElevateDB?

At the moment we can do updates like this:

update table1 set column1='newvalue'
where table1.idfield in (select idfield from table2 where x=x)

and this is rather slow. We have changed code so we do a

1. query for subselect
2. loop in delphi for each row in subselect and update table1

Would be cool, if this can be done with ONE update command.

--
--
Yusuf Zorlu | MicrotronX
Tue, Dec 15 2020 8:32 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Yusuf Zorlu

<<
update table1 set column1='newvalue'
where table1.idfield in (select idfield from table2 where x=x)
>>

There is an index available (first field) for table1.idfiled and another one to table2.x ?

If not, try creating the indexes and see the results.

Eduardo
Wed, Dec 16 2020 1:07 AMPermanent Link

Yusuf Zorlu

MicrotronX

Jose Eduardo Helminsky wrote:

> Yusuf Zorlu
>
> <<
> update table1 set column1='newvalue'
> where table1.idfield in (select idfield from table2 where x=x)
> > >
>
> There is an index available (first field) for table1.idfiled and
> another one to table2.x ?
>
> If not, try creating the indexes and see the results.
>
> Eduardo

Hi Eduardo, all fields are indexed. The problem lies within the IN
statement which slows down somehow.

--
--
Yusuf Zorlu | MicrotronX
Wed, Dec 16 2020 4:10 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Yusuf


My understanding (could be wrong) is that the IN test is being carried out for every row in the source table. Its possible that its the WHERE clause in the subselect that's preventing optimisation.

If you can post a copy of the database with information and the actual query I'll be happy to see if I can find a faster method.

Roy Lambert
Thu, Dec 17 2020 2:52 AMPermanent Link

Yusuf Zorlu

MicrotronX

Roy Lambert wrote:
> My understanding (could be wrong) is that the IN test is being
> carried out for every row in the source table. Its possible that its
> the WHERE clause in the subselect that's preventing optimisation.
>

Hi Roy, i also think that the IN is beeing done for each row. I don't
know if the optimizier can change the IN into a INNER-JOIN
automatically?

At the moment we have a solution which is good ... but best solution in
my opinion would be, when the IN will automatically optimized into
INNER JOIN.

--
--
Yusuf Zorlu | MicrotronX
Image