dbcon query

[Former Member]

hey all

i need to update few records in remote DB only where i have a line with a maximal date .

the table is very large and it takes a lot of time to update the lines .

im using this query with native sql ( query to remote DB ), the code is :

exec:

select max(datum)

into max_date

from ora1

where pr = :prm1

endexec.

and then :

exec :

update ora1

set x = :y

where date = :max_date

endexec.

as i said it takes a lot of time .

is there any other sql statments that improve the time of this action ?

[Former Member]

Hi

Sadly you did not give us enough information to give you a good solution. We cannot even see if the first statement is taking long, or the second. We don't know how many rows are in table ora1 and how many of them match pr = :prm1. And we don't know how many tend to match date = :maxdate.

So this is only a guess, but this might work. To do only the update in one go might help (you probably need an index with pr and date):

update ora1
set x = :y
where pr = :prm1 and date = max(datum)

One issue here might be, that max(datum) is not the same as pr = :prm1 and date = max(datum). So be careful.

Best regards, Michael

[Former Member]

hey and thanks for the answer ,

more details :

the first statement takes a lot of time ( table ora1 is a very large table with many records ) .

how can i combine the statments in 2 one ?

update ora1

set x = :y

where pr = :prm1 and ** date = max(datum) **

is the starked section above is syntax correct ?

will it improve the query time ?

BR

ASA

[Former Member]

date = max(datum) **
is the starked section above is syntax correct ? 

Oh, you seem right, this does not work like this. You would need to add another nested query, which basically leads to the solution you already have.

But to speed up the first query you can try to create an index containing at least pr. But this is only a good thing as long as your select on pr is selective (meaning you get only a fraction of rows with your where clause).

Personally i also would include the datum column to that index, in that case you would not need to access the table blocks at all.

Regards, Michael

[Former Member]

hey

can you give example of the nested query syntax ?

BR

ASA

[Former Member]

well...

update ora1
set x = :y
where pr = :prm1 and
date = ( select max(datum) from ora1
where pr = :prm1 );

But this most probably won't be faster, than the original version...