VB.net Windows service response time

Question

I have a windows service that utilizes an infinite loop and thread.sleep. The sleep time for the thread is 10 seconds.

When the service is initially started AND has work to do (data exist in SQL), it immediately does it's processes. It continues to be responsive and process tasks every 10 seconds as long as it has work to do. If no data is present in SQL for an extended period (i.e. 15-20 minutes) then the service starts responding very slowly. It will eventually pick the data up and process it, but it takes more like 10 minutes instead of 10 seconds.

There's no logic in the code telling the service to sleep. Any ideas?

Answer 1

Without seeing your code, it is impossible to give a definitive answer; It could be the case that you aren't tidying up your database connections quickly enough.

If you've something like:

   While( True )
      Dim con as new SqlConnection(connectionString)
      con.Open()

      Dim cmd as New SqlCommand("usp_getJob", con)
      cmd.CommandType = CommandType.StoredProcedure

      Dim dr as SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)
      If( dr.Read() )
          DoSomething(dr)
      Else
          Thread.Sleep(10)
      End If
   End While

...then it will open connections faster than they will go away by themselves.

There are several alternates, and I'd advocate using Using blocks to allow the connection to be returned to the connection pool as fast as possible:

   While( True )
      Using con as new SqlConnection(connectionString)
          con.Open()

          Using cmd as New SqlCommand("usp_getJob", con)
            cmd.CommandType = CommandType.StoredProcedure

            Using dr as SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.SingleRow Or CommandBehavior.CloseConnection)
                If( dr.Read() )
                    DoSomething(dr)
                    dr.Close()
                Else
                    Thread.Sleep(10)
                End If
             End Using
          End Using
      End Using
   End While

These will then call Dispose() on the SqlDataReader, SqlConnection and SqlCommand - either of the first two will cause the connection to be released to the connection pool (given we've asked the SqlDataReader to CloseConnection on its close)

Answer 2

Totally agree with Rowland on this one. Without seeing the code it would hard to determine but most likely culprit is your db code.