I want to do this is the purpose of the

UPDATE the NBA a1 SET jj1=(... And sj2 BETWEEN the date '2007-0-01' and the date '2007-03-31') where sj2 BETWEEN the date '2007-0-01' and the date '2007-03-31';
commit;
Performed above, then the
UPDATE the NBA a1 SET jj1=(... And sj2 BETWEEN the date '2007-4-01' and the date '2007-06-30') where sj2 BETWEEN the date '2007-4-01' and the date '2007-06-30' ';
commit;
Then
UPDATE the NBA a1 SET jj1=(... And sj2 BETWEEN the date '2007-7-01' and the date '2007-09-30') where sj2 BETWEEN the date '2007-7-01' and the date '2007-09-30';
commit;
Then
UPDATE the NBA a1 SET jj1=(... And sj2 BETWEEN the date '2007-10-01' and the date '2007-12-31') where sj2 BETWEEN the date '2007-10-01' and the date '2007-12-31';
commit;

sj2 date field is the system function
Then the next 9, 2008200 a year until 2020, are executed in sequence like this

Don't know where is my database problem, a quarter of hundreds of thousands of data updates will be more than half an hour, more than ten years of data a little manual update is too trival,

So think of use for,,, such as loop statements, but due to the beginner, baidu for a few days all make uncertain, so think of ask the teacher or the higher-ups in the BBS to help write, save time,

Similar to declare

I the INTEGER:=0;
For I in 2007.. 2020 loop
UPDATE the NBA a1 SET jj1=(... And sj2 BETWEEN date 'I - 07-1 and the date' I - 12-31 ') where sj2 BETWEEN date 'I - 07-01' and date 'I - 12-31';
commit;
end loop;
Similar to the above statements like this, can a month to a month in order to finish are updated from 2007 to 2020, can also according to a quarter after quarter, or even a year after year can also
Don't know which teacher and large coffee, free to move to help out, thank you very much!

CodePudding user response:

I don't know you what is the logic of the assignment, can consider to try the temporary table

 
DECLARE @ I INT 
DECLARE @ BEGIN_PERYEAR DATE 
DECLARE @ END_PERYEAR DATE 

The SET @ I=0 
The SET @ BEGIN_PERYEAR='2007-01-01' 
The SET @ END_PERYEAR='2007-12-31' 

WHILE @ I<=13 

The BEGIN 

UPDATE the NBA 
The SET jj1=(... And sj2 BETWEEN DATEADD (YEAR, @ I, @ BEGIN_PERYEAR) and DATEADD (YEAR, @ I, @ END_PERYEAR)) 
Where sj2 BETWEEN DATEADD (YEAR, @ I, @ BEGIN_PERYEAR) AND DATEADD (YEAR, @ I, @ END_PERYEAR) 

The SET @ I=@ I + 1 

END 

CodePudding user response:

If the subquery is slow, want to see the logic, a statement is more than half an hour, there must be some problem, please provide the complete syntax, basically see a few table joins, whether there is an index on the field

CodePudding user response:

Thank you teacher!
I update statement is
UPDATE the NBA a1 SET var51=(select vi from (select/* + no_merge (a2) use_hash (a2) */dh, sj2, sum (jg) over (order by dh, sj rows between var50 PRECEDING and current row) as the vi from mytest. The NBA where sj2 between the date '2018-01-1' and the date '2018-12-31') a2 where a1. Dh=a2. Dh and a1. Sj2=a2. Sj2 and sj2 between the date '2018-01-01' and the date '2018-12-31');
More than a year there are about 80 ten thousand data, update the time it will be more than 70 minutes, half a year to more than 50 minutes, a quarter more than 20 minutes
If two years more than 160 ten thousand data, five or six hours, the longer the more slow, in the end is extremely slow, so I had to think of some way to split the time, year after year in the most updated
Some say I have a bottleneck in the database, don't know whether I have this bottleneck, also don't know how to find bottlenecks
Trouble the teacher give directions, thank you!