Select trandate serseqno, acctno, voukind vouno, memomsg, cdflag, amount and trancode, oppaccno oppacctname, flag,
The from glsinacctlist a
Where cancelflag='0'
And trancode not in (' 548300 ', 'T00000) and acctno=' 6217000088103047 'and trandate=' 2017-04-17 '
And (((trancode='TC0001 or trancode=' TP0001 ') and (flag='1' or flag='2')) or (trancode & lt;> 'TC0001 and trancode & lt;> 'TP0001 and flag not in (' B', 'C', 'D', 'E')))
And not the exists (select 1 from GLSINACCTLIST where acctno=a.a cctno and trandate=a.t randate and cancelstan=a.s erseqno and cancelflag='1') order by a.s erseqno

This statement at execution time, sometimes fast, sometimes slow, the executive has surged to 100% CPU
There are about 80 million data in table glsinacctlist
Great god a optimization Suggestions to the door, it is best to explain why, in the small white ah, next week online
Hurry hurry!

CodePudding user response:

Look at the execution plan, estimate relatively large amount of calculation

CodePudding user response:

reference 1st floor wmxcn2000 response:

look at the execution plan, estimate the amount of calculation is bigger

Well,,, see don't know much about the execution plan, what a great god

CodePudding user response:

This is the execution plan, ask everybody to help, thank you

CodePudding user response:

Perform and looking at, nothing abnormal,

Collect the two tables of statistics,

CodePudding user response:

Select trandate serseqno, acctno, voukind vouno, memomsg, cdflag, amount and trancode, oppaccno oppacctname, flag,
The from glsinacctlist
a.
Not the exists (select 1 from GLSINACCTLIST where acctno=a.a cctno and trandate=a.t randate and cancelstan=a.s erseqno and cancelflag='1')

What is the significance of this connection, can not directly into cancelflag!='1'
?