[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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.
0
Kerbeross
Asked:
Kerbeross
  • 7
  • 3
  • 3
  • +2
1 Solution
 
ADSaundersCommented:
Hi Kerbeross,
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
0
 
KerberossAuthor Commented:
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
0
 
ADSaundersCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Mike TomlinsonMiddle School Assistant TeacherCommented:
DoEvents will allow your programs GUI to be more responsive...but it won't reduce your applications CPU usage.  To do that you need to use the Sleep() API.  Try using both in combination...

Private Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

...

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

            DoEvents ' Keep app responsive
            Sleep 50 ' Reduce CPU usage

            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
0
 
ADSaundersCommented:
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
0
 
ADSaundersCommented:
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
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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...


0
 
ADSaundersCommented:
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
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
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 ***
0
 
EDDYKTCommented:
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_NORMAL = (THREAD_PRIORITY_LOWEST + 1)
Const THREAD_PRIORITY_ABOVE_NORMAL = (THREAD_PRIORITY_HIGHEST - 1)
Const THREAD_PRIORITY_IDLE = THREAD_BASE_PRIORITY_IDLE
Const THREAD_PRIORITY_NORMAL = 0
Const THREAD_PRIORITY_TIME_CRITICAL = 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(hThread))
    Me.Print "Current Priority Class:" + Str$(GetPriorityClass(hProcess))
End Sub
0
 
ADSaundersCommented:
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
0
 
ADSaundersCommented:
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
0
 
KerberossAuthor Commented:
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.
0
 
SethiCommented:
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!
0
 
KerberossAuthor Commented:
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now