Multhread - Timeout Problem

[paulnamroud]

Hello,

I have simple ASP.net web form. With a simple click on a button i need to call a the Multithread process in order to execute my querry in SQL Server DB. I know that my querry willl take up to 45 min to be executed. While the process is running i need to display some status information like: Starting time, Time elapsed, % of completed...

So after running the process i got the following error message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

So i have 2 problems to fix:

Avoid getting timeout while processing/calling the stored procedure. I tried to add cmd.CommandTimeout = 1000 but it didn't work out!
Display correctly Time elapsed and % Completed while processing.
I used the code on this link to build my code:

http://www.sitepoint.com/print/threading-asp-net/

Thank for your help


Paul

http://www.geniatek.com

Here's my code in the main form:


Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO

Imports System.Threading

Partial Public Class Inventory
Inherits System.Web.UI.Page

Private v_thread As Thread

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

If Not Page.IsPostBack Then

Me.lblErrorMessage.Text = ""
Me.lblErrorMessage.Visible = False
Me.pnlErrorMessage.Visible = False

Me.rdResults.Visible = False

End If

If Session("ProcessStart") = True Then
Response.Redirect("archive_style_result.aspx")
End If

' --------------------------------------------------------------------------------------------
' Add this new section in order to display the Status of the archiving while proceeding
' --------------------------------------------------------------------------------------------
SyncLock Session.SyncRoot
' Initialize pour session variables to hold our results
Session("ProcessStart") = False
Session("Complete") = False
Session("ProcessStartAt") = ""
Session("ProcessEndAt") = ""
Session("TotalTime") = ""
Session("Status") = ""
Session("Phrase") = ""
End SyncLock

End Sub


Protected Sub BtnRebuild_Click(ByVal sender As Object, ByVal e As EventArgs)
If Page.IsPostBack Then

lblErrorMessage.Text = ""
lblErrorMessage.Visible = False
pnlErrorMessage.Visible = False

Try

Dim v_thread As New System.Threading.Thread(AddressOf Me.RebuildInventory)
'v_thread = New Thread(AddressOf Me.ArchiveStyle1)

v_thread.IsBackground = True
v_thread.Priority = ThreadPriority.Lowest
v_thread.Start()

'now redirect to the results page
Response.Redirect("archive_style_result.aspx")

lblErrorMessage.Text = "Rebuild inventory is in process... This may take some times. <BR>Please verify later on your Global Inventory. "
lblErrorMessage.Visible = True
pnlErrorMessage.Visible = True

Catch ex As Exception
lblErrorMessage.Text = ex.Message.ToString
lblErrorMessage.Visible = True
pnlErrorMessage.Visible = True

End Try

End If

End Sub

Sub RebuildInventory()

Try

Dim v_string As String = ""
Dim i As Integer = 0

Dim v_start_time_ticks As Long = 0
Dim v_string_start_time As String = ""

Dim v_total_sleep_time As Double = 0.0
Dim v_total_thread_life As Integer = 2

Dim v_length As Integer = 100000

' log our start time, in ticks, and in Long Date format
v_start_time_ticks = DateTime.Now.Ticks
v_string_start_time = DateTime.Now

' Get phrase
v_string = "Archiving style in process ..."

'convert users time from seconds to milliseconds
v_total_sleep_time = 1000.0
v_total_sleep_time = v_total_sleep_time * CInt(v_total_thread_life)
v_total_sleep_time = (v_total_sleep_time / v_length)


' ------------------------------------------------------------------------
Dim v_connection As SqlConnection = Nothing
Dim v_sql_connection_string As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
v_connection = New SqlConnection(v_sql_connection_string)

Try

SyncLock Session.SyncRoot
Session("ProcessStart") = True
Session("ProcessStartAt") = v_string_start_time
Session("Phrase") = v_string
End SyncLock

' Open Connection
v_connection.Open()

Dim cmd As SqlCommand

'Insert/Update data in database
cmd = New SqlCommand("pr_update_warehouse_status_populate_all", v_connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 1000

With cmd.Parameters.AddWithValue("@p_transaction_code", "-1")
.Size = 20
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Input
End With

With cmd.Parameters.AddWithValue("@p_error_no", "")
.Size = 20
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Output
End With

With cmd.Parameters.AddWithValue("@p_error_txt", "")
.Size = 1000
.SqlDbType = SqlDbType.NVarChar
.Direction = ParameterDirection.Output
End With

' This method will put our thread to sleep for the specified number of milliseconds.
' Without the sleep, this method would execute too fast to see the thread working.
Thread.Sleep(v_total_sleep_time)

cmd.ExecuteNonQuery()

Catch ex As Exception
Throw ex

Finally
If Not v_connection Is Nothing Then
v_connection.Close()
v_connection = Nothing
End If
End Try


'we use synclock to block any other thread from accessing
'session variables while we are changing their values.
SyncLock Session.SyncRoot
Session("Status") = Format((i / (v_length - 1)) * 100, "#0.00") & "% complete."
Session("TotalTime") = Format((DateTime.Now.Ticks - v_start_time_ticks) / 10000000, "#0.00") & " sec."
Session("Phrase") = v_string
End SyncLock

' Our method is complete, so set the Session variables accordingly
SyncLock Session.SyncRoot
Session("ProcessStartAt") = v_string_start_time
Session("ProcessEndAt") = DateTime.Now
Session("Status") = "100% completed"
Session("Complete") = True
Session("ProcessStart") = False ' End of the process
Session("Phrase") = "Archiving style is completed!"
End SyncLock

'cmd.ResetCommandTimeout()
Catch ex As Exception
Throw ex

End Try


End Sub

End Class

Here's my code in the result form:


Partial Class _ArchiveStyleResult
Inherits System.Web.UI.Page

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Try
'Put user code to initialize the page here
'check the value of Session("Completed"), if it is True, stop writing

Me.lblErrorMessage.Text = ""
Me.lblErrorMessage.Visible = False
Me.pnlErrorMessage.Visible = False

If Session("Complete") <> True Then

' Make sure session variables are enabled, if not warn user
If Session("Complete") <> False Then

' Error with session variable, Session("Complete") is not True or False
lblComplete.Text = "Error with Session('Complete')"

Else

' Set page to auto refresh page every 2 seconds, until thread is done
Response.Write("<META HTTP-EQUIV=Refresh CONTENT='10; URL='>")

SyncLock Session.SyncRoot
lblProcessStartAt.Text = Session("ProcessStartAt")
lblProcessEndAt.Text = Session("ProcessEndAt")
lblStatus.Text = Session("Status")
lblTotalTime.Text = Session("TotalTime")
lblPhrase.Text = Session("Phrase")
lblComplete.Text = Session("Complete")
End SyncLock

End If


Else

' Thread is complete, stop writing refresh tag, and display results
SyncLock Session.SyncRoot
lblProcessStartAt.Text = Session("ProcessStartAt")
lblProcessEndAt.Text = Session("ProcessEndAt")
lblStatus.Text = Session("Status")
lblTotalTime.Text = Session("TotalTime")
lblPhrase.Text = Session("Phrase")
lblComplete.Text = Session("Complete")
End SyncLock

End If

Catch ex As Exception
lblErrorMessage.Text = ex.Message.ToString
lblErrorMessage.Visible = True
pnlErrorMessage.Visible = True

End Try


End Sub


End Class

 

[RiverGuy]

A value of 1000 for the CommandTimeout would only be 16 minutes. A value of 0 would be indefinite. I'm worried abut your query though. Is it a data retrieval query? I would work on optimizing it to get the time down from 45 minutes. Depending on the query, you can get a lot more performance. A couple years ago in fact, I was working at a place where I improved a nightly query from several hours down to about thirty seconds.

 

[Jasen]

I believe your timeout is happening on the server side, not in your app. SQL Server has a default timeout setting for if a query is taking too long to finish, and that's the exact message it returns.
I would also suggest trying to optimize the query and/or adjust the indexes on the affected tables if possible. Assuming you have access to modify the stored procedure or tables. Even with several million records in a table, a query should not take that long to finish.
The timeout can also be adjusted in SQL Server.


Personally, what I would do is fire the database function call off in a new thread, and declare a delegate to handle receiving %complete updates from it. This would free up your main thread from any waiting or contention while the DB call spins and waits. Then doing the time-elapsed would be a simple matter of starting a timer object to increment a counter.

 

[Jasen]

Edit to above: nevermind about the threads, I just noticed you are already doing that. I need to read more thoroughly.