Kerbeross
asked on
Processing takes a lot of CPU
Hi Experts,
I did 3 tier program using SQL server. One program at client side, one server program and a SQL 2k db. the db just hold the data, the server program doing the request and processing. Let's say I have 30000 records in a table and want to query the table in a 2 cycle query, meaning a loop in other loop. It will take the whole CPU to process and this will make the server like hanging.
How to avoid this situation. If can, I want to see the process running with status of the searching displayed interactively on the server program interface, so that I know the process is still running.
Please help, if necessary I will program the code for you Experts to have a look and comment.
Thanks.
I did 3 tier program using SQL server. One program at client side, one server program and a SQL 2k db. the db just hold the data, the server program doing the request and processing. Let's say I have 30000 records in a table and want to query the table in a 2 cycle query, meaning a loop in other loop. It will take the whole CPU to process and this will make the server like hanging.
How to avoid this situation. If can, I want to see the process running with status of the searching displayed interactively on the server program interface, so that I know the process is still running.
Please help, if necessary I will program the code for you Experts to have a look and comment.
Thanks.
ASKER
Yes, it's VB. And here's the code (it search for duplicate list):
Do While Not rs.EOF
If meet my requirements Then
rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
If rs1.RecordCount > 1 Then
Do While Not rs1.EOF
SendData to client
rs1.MoveNext
Loop
End If
End If
rs.MoveNext
X=X+1 'just to update the client the send status, I want to display this
Loop
Do While Not rs.EOF
If meet my requirements Then
rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
If rs1.RecordCount > 1 Then
Do While Not rs1.EOF
SendData to client
rs1.MoveNext
Loop
End If
End If
rs.MoveNext
X=X+1 'just to update the client the send status, I want to display this
Loop
Kerbeross,
DoEvents won't hurt your program, so you can use it multiple times .. I would suggest:
> Do While Not rs.EOF
DoEvents ' <-- Here
> If meet my requirements Then
> rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
> If rs1.RecordCount > 1 Then
> Do While Not rs1.EOF
DoEvents ' <-- And Here
> SendData to client
> rs1.MoveNext
> Loop
> End If
> End If
> rs.MoveNext
> X=X+1 'just to update the client the send status, I want to display this
.. Alan
DoEvents won't hurt your program, so you can use it multiple times .. I would suggest:
> Do While Not rs.EOF
DoEvents ' <-- Here
> If meet my requirements Then
> rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
> If rs1.RecordCount > 1 Then
> Do While Not rs1.EOF
DoEvents ' <-- And Here
> SendData to client
> rs1.MoveNext
> Loop
> End If
> End If
> rs.MoveNext
> X=X+1 'just to update the client the send status, I want to display this
.. Alan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kerbeross,
Actually, To ensure processor has interrupts to update the display, the outer loop DoEvents might be better after updating your counter (and displayiong it .. ?
> Do While Not rs.EOF
> If meet my requirements Then
> rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
> If rs1.RecordCount > 1 Then
> Do While Not rs1.EOF
DoEvents ' <-- And Here
> SendData to client
> rs1.MoveNext
> Loop
> End If
> End If
> rs.MoveNext
> X=X+1 'just to update the client the send status, I want to display this
DoEvents ' <-- Here
Loop ' I presume this (end of outer loop) is here somewhere . . ?
.. Alan
Actually, To ensure processor has interrupts to update the display, the outer loop DoEvents might be better after updating your counter (and displayiong it .. ?
> Do While Not rs.EOF
> If meet my requirements Then
> rs1.Open SQL, db, adOpenKeySet, adLockReadOnly
> If rs1.RecordCount > 1 Then
> Do While Not rs1.EOF
DoEvents ' <-- And Here
> SendData to client
> rs1.MoveNext
> Loop
> End If
> End If
> rs.MoveNext
> X=X+1 'just to update the client the send status, I want to display this
DoEvents ' <-- Here
Loop ' I presume this (end of outer loop) is here somewhere . . ?
.. Alan
Idle_Mind,
Sorry, I beg to differ. A sufficiently intensive processing loop, particularly if nested, can make an entire system seem as if it is locked up, not just the GUI. DoEvents releases control of the CPU to allow Windows to respond to _Any_ Windows messages, not just those for the target program. Sleep may be useful in a highly multi-tasking environment when other processes must have their 'whack' at the processor, but if the system is otherwise fairly idle, it'll just slow down the processing , particularly if it's in the inner loop - 50 ms in every inner cycle??
.. Alan
Sorry, I beg to differ. A sufficiently intensive processing loop, particularly if nested, can make an entire system seem as if it is locked up, not just the GUI. DoEvents releases control of the CPU to allow Windows to respond to _Any_ Windows messages, not just those for the target program. Sleep may be useful in a highly multi-tasking environment when other processes must have their 'whack' at the processor, but if the system is otherwise fairly idle, it'll just slow down the processing , particularly if it's in the inner loop - 50 ms in every inner cycle??
.. Alan
DoEvents only allows YOUR application to process the messages in ITS message queue. It doesn't allow other applications to do the same.
If your application is hogging the CPU at 99%, then the only way to allow other applications to "play" is to reduce your apps CPU cycles. When TIGHT looping is involved, this is achieved by the Sleep() API.
And yes, Sleep() does slow down processing....BUT DoEvents() does as well. You can't have the best of both worlds though! If you want your application to do something as quickly as possible then updating the GUI shouldn't be your main concern...
If your application is hogging the CPU at 99%, then the only way to allow other applications to "play" is to reduce your apps CPU cycles. When TIGHT looping is involved, this is achieved by the Sleep() API.
And yes, Sleep() does slow down processing....BUT DoEvents() does as well. You can't have the best of both worlds though! If you want your application to do something as quickly as possible then updating the GUI shouldn't be your main concern...
Idle_Mind,
I quote from the MSDN library:
DoEvents switches control to the operating-environment kernel. Control returns to your application as soon as all other applications in the environment have had a chance to respond to pending events. This doesn't cause the current application to give up the focus, but it does enable background events to be processed.
From MSDN library fo Sleep:
dwMilliseconds
Specifies the time, in milliseconds, for which to suspend execution. A value of zero causes the thread to relinquish the remainder of its time slice to any other thread of equal priority that is ready to run. If there are no other threads of equal priority ready to run, the function returns immediately, and the thread continues execution. A value of INFINITE causes an infinite delay.
As far as I can see, DoEvants corresponds to Sleep(0) :
.. Alan
I quote from the MSDN library:
DoEvents switches control to the operating-environment kernel. Control returns to your application as soon as all other applications in the environment have had a chance to respond to pending events. This doesn't cause the current application to give up the focus, but it does enable background events to be processed.
From MSDN library fo Sleep:
dwMilliseconds
Specifies the time, in milliseconds, for which to suspend execution. A value of zero causes the thread to relinquish the remainder of its time slice to any other thread of equal priority that is ready to run. If there are no other threads of equal priority ready to run, the function returns immediately, and the thread continues execution. A value of INFINITE causes an infinite delay.
As far as I can see, DoEvants corresponds to Sleep(0) :
.. Alan
We're talking apples and oranges here...you need both if you want to make your application behave well. DoEvents is necessary to make your app responsive while processing. Sleep (with a value greater than zero) is necessary to reduce your apps CPU usage. Using both together is better overall for the entire system...
*** steps off soapbox ***
*** steps off soapbox ***
The alternative way is set your task to low priority. This method will still use up 100 % process, but other task will use cpu before your task
Const THREAD_BASE_PRIORITY_IDLE = -15
Const THREAD_BASE_PRIORITY_LOWRT = 15
Const THREAD_BASE_PRIORITY_MIN = -2
Const THREAD_BASE_PRIORITY_MAX = 2
Const THREAD_PRIORITY_LOWEST = THREAD_BASE_PRIORITY_MIN
Const THREAD_PRIORITY_HIGHEST = THREAD_BASE_PRIORITY_MAX
Const THREAD_PRIORITY_BELOW_NORM AL = (THREAD_PRIORITY_LOWEST + 1)
Const THREAD_PRIORITY_ABOVE_NORM AL = (THREAD_PRIORITY_HIGHEST - 1)
Const THREAD_PRIORITY_IDLE = THREAD_BASE_PRIORITY_IDLE
Const THREAD_PRIORITY_NORMAL = 0
Const THREAD_PRIORITY_TIME_CRITI CAL = THREAD_BASE_PRIORITY_LOWRT
Const HIGH_PRIORITY_CLASS = &H80
Const IDLE_PRIORITY_CLASS = &H40
Const NORMAL_PRIORITY_CLASS = &H20
Const REALTIME_PRIORITY_CLASS = &H100
Private Declare Function SetThreadPriority Lib "kernel32" (ByVal hThread As Long, ByVal nPriority As Long) As Long
Private Declare Function SetPriorityClass Lib "kernel32" (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long
Private Declare Function GetThreadPriority Lib "kernel32" (ByVal hThread As Long) As Long
Private Declare Function GetPriorityClass Lib "kernel32" (ByVal hProcess As Long) As Long
Private Declare Function GetCurrentThread Lib "kernel32" () As Long
Private Declare Function GetCurrentProcess Lib "kernel32" () As Long
Private Sub Form_Load()
'KPD-Team 2000
'URL: http://www.allapi.net/
'E-Mail: KPDTeam@Allapi.net
Dim hThread As Long, hProcess As Long
'retrieve the current thread and process
hThread = GetCurrentThread
hProcess = GetCurrentProcess
'set the new thread priority to "lowest"
SetThreadPriority hThread, THREAD_PRIORITY_LOWEST
'set the new priority class to "idle"
SetPriorityClass hProcess, IDLE_PRIORITY_CLASS
'print some results
Me.AutoRedraw = True
Me.Print "Current Thread Priority:" + Str$(GetThreadPriority(hTh read))
Me.Print "Current Priority Class:" + Str$(GetPriorityClass(hPro cess))
End Sub
Const THREAD_BASE_PRIORITY_IDLE = -15
Const THREAD_BASE_PRIORITY_LOWRT
Const THREAD_BASE_PRIORITY_MIN = -2
Const THREAD_BASE_PRIORITY_MAX = 2
Const THREAD_PRIORITY_LOWEST = THREAD_BASE_PRIORITY_MIN
Const THREAD_PRIORITY_HIGHEST = THREAD_BASE_PRIORITY_MAX
Const THREAD_PRIORITY_BELOW_NORM
Const THREAD_PRIORITY_ABOVE_NORM
Const THREAD_PRIORITY_IDLE = THREAD_BASE_PRIORITY_IDLE
Const THREAD_PRIORITY_NORMAL = 0
Const THREAD_PRIORITY_TIME_CRITI
Const HIGH_PRIORITY_CLASS = &H80
Const IDLE_PRIORITY_CLASS = &H40
Const NORMAL_PRIORITY_CLASS = &H20
Const REALTIME_PRIORITY_CLASS = &H100
Private Declare Function SetThreadPriority Lib "kernel32" (ByVal hThread As Long, ByVal nPriority As Long) As Long
Private Declare Function SetPriorityClass Lib "kernel32" (ByVal hProcess As Long, ByVal dwPriorityClass As Long) As Long
Private Declare Function GetThreadPriority Lib "kernel32" (ByVal hThread As Long) As Long
Private Declare Function GetPriorityClass Lib "kernel32" (ByVal hProcess As Long) As Long
Private Declare Function GetCurrentThread Lib "kernel32" () As Long
Private Declare Function GetCurrentProcess Lib "kernel32" () As Long
Private Sub Form_Load()
'KPD-Team 2000
'URL: http://www.allapi.net/
'E-Mail: KPDTeam@Allapi.net
Dim hThread As Long, hProcess As Long
'retrieve the current thread and process
hThread = GetCurrentThread
hProcess = GetCurrentProcess
'set the new thread priority to "lowest"
SetThreadPriority hThread, THREAD_PRIORITY_LOWEST
'set the new priority class to "idle"
SetPriorityClass hProcess, IDLE_PRIORITY_CLASS
'print some results
Me.AutoRedraw = True
Me.Print "Current Thread Priority:" + Str$(GetThreadPriority(hTh
Me.Print "Current Priority Class:" + Str$(GetPriorityClass(hPro
End Sub
Idle_Mind,
Conceded . :-[
Kerbeross
We're not really having a flame fest here, just looks like it! What we're both saying is that you need to allow other processes (including your GUI) to respond to their message queues. How you do it (DoEvents, Sleep) is really going to depend on what else is going on in the system at the time, and how comfortable you are with the concepts. I could throw another concept in the bag here and suggest that you look at process priority, but I won't!
Anyway, Happy coding.
.. Alan
Conceded . :-[
Kerbeross
We're not really having a flame fest here, just looks like it! What we're both saying is that you need to allow other processes (including your GUI) to respond to their message queues. How you do it (DoEvents, Sleep) is really going to depend on what else is going on in the system at the time, and how comfortable you are with the concepts. I could throw another concept in the bag here and suggest that you look at process priority, but I won't!
Anyway, Happy coding.
.. Alan
Idle_Mind,
> I could throw another concept in the bag here and suggest that you look at process priority, but I won't!
EDDYKT did that whilst I was typing :-(
.. Alan
> I could throw another concept in the bag here and suggest that you look at process priority, but I won't!
EDDYKT did that whilst I was typing :-(
.. Alan
ASKER
I'm open to every way :-) My purpose in the code is 1. to minimize the CPU usage, 2. make the program respond to my click, 3. update my client of the current searching status. I will try every suggestion here by AdSaunders, Idle_Mind and EDDYKT. Wow! It's a bunch if quick response.
I would also like to put in my experience. I have a lot of application for my in house use that involves hours of processing at times. Initially it used to take almost 90 to 98% of CPU processing and working on anything else would become impossible. Then I used Doevents and nothing much changed. Now I am using DoEvents and Sleep together and the world has changed :-) I experience this almost everyday with my applications and using Sleep is must!
ASKER
Thank U everybody for solving the problem. The combination of DoEvents and Sleep sometimes reduce the CPU usage for 50%. Now I can stop the program whenever I want to.
You've asked this q in the VB area, so I'm assuming that you want a VB solution. The answer is to interrupt one of your loops (preferrably the inner) to allow the processor to handle other requests. The way to do this in VB is to use the DoEvents statement.
Pseudo code
'Loop 1 (outer)
Loop Start ' (Do while, For Each Etc. .
' Outer Process
Loop Start ' Inner Loop
DoEvents 'Allow perocessor to interrupt each inner loop cycle
' Inner Process
Loop End ' (Inner) Loop, Next etc.
' Any more outer loop processing
Loop End '(Outer)
Regards .. Alan