Solved

Help with threading database routine?

Posted on 2010-11-12
11
351 Views
Last Modified: 2012-05-10
Can someone help me out here.  I am attempting to implement some threading within a db class, yet I am not getting anything returned.

What can I do to get my returnerd properties back from the class?

Code:
Partial Class test
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.Write("<h1>Threading</h1>")

        Test()
        'Test2()

    End Sub

    Public Sub Test()
        Dim objDb As New TurboKits.DataWork
        With objDb
            .ConnectionString = Common.ConnString
            .CommandType = 1
            .Query = "Select * From TblCustomerRideCategories"
            .ParamNames = New String() {}
            .ParamValues = New String() {}
            .ExecuteDR()
            HttpContext.Current.Response.Write(.TotalRecords)
            'If .TotalRecords > 0 Then
            '    For i As Long = 0 To .TotalRecords - 1
            '        HttpContext.Current.Response.Write(.ReturnValues(1, i)(1) & "<br />")
            '    Next
            'Else
            '    HttpContext.Current.Response.Write("No records returned")
            'End If
        End With
        objDb = Nothing
    End Sub

    Public Sub Test2()
        Dim objDb As New TurboKits.DataWork
        With objDb
            .ConnectionString = Common.ConnString
            .CommandType = 1
            .Query = "Select * From TblParts"
            .ParamNames = New String() {""}
            .ParamValues = New String() {""}
            .ExecuteDR()
            If .TotalRecords > 0 Then
                For i As Long = 0 To .TotalRecords - 1
                    Response.Write(.ReturnValues(4, i)(1) & "<br />")
                Next
            End If
        End With
    End Sub


End Class

Open in new window

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Web.Caching
Imports System.Text
Imports System.IO
Imports System.Threading
Imports System.Web

Namespace TurboKits

    Public Class DataWork
#Region "Properties"

#Region "Public Properties"

#Region "Set"

        ''' <summary>
        ''' Sets the connection string
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ConnectionString() As String
            Set(ByVal value As String)
                _ConnString = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the command type: 1 = Inline SQL, 4 = Stored Procedure
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property CommandType() As Integer
            Set(ByVal value As Integer)
                _CmdType = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the Inline SQL or Stored Procedure to use
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property Query() As String
            Set(ByVal value As String)
                _Qry = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the parameter values for the queries performed
        ''' </summary>
        ''' <value></value>
        ''' <remarks>New DataType() {}</remarks>
        Public WriteOnly Property ParamValues() As Object
            Set(ByVal value As Object)
                _ParamValues = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the paramater names for the queries performed
        ''' </summary>
        ''' <value></value>
        ''' <remarks>New DataType() {}</remarks>
        Public WriteOnly Property ParamNames() As Object
            Set(ByVal value As Object)
                _ParamNames = value
            End Set
        End Property

        ''' <summary>
        ''' Does the Stored Procedure return a value?
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property HasReturn() As Boolean
            Set(ByVal value As Boolean)
                _HasReturn = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the return parameter name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ReturnParamName() As String
            Set(ByVal value As String)
                _RetParamName = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the return data type
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ReturnDataType() As Object
            Set(ByVal value As Object)
                _RetDataType = value
            End Set
        End Property

        ''' <summary>
        ''' Should it be paged?
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ShouldPage() As Boolean
            Set(ByVal value As Boolean)
                _ShouldPage = value
            End Set
        End Property

        ''' <summary>
        ''' Sets how many records per page
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property PageSize() As Integer
            Set(ByVal value As Integer)
                _PageSize = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the current page we are on
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property CurrentPage() As Long
            Set(ByVal value As Long)
                _CurrPage = value
            End Set
        End Property

#End Region

        ''' <summary>
        ''' Gets the recordset returned as a 3d Array
        ''' ReturnValues(Column, Row)(0 = Value, 1 = ColumnName)
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property ReturnValues() As Object
            Get
                Return _RetVals
            End Get
            Set(ByVal value As Object)
                _RetVals = value
            End Set
        End Property

#Region "Get"

        ''' <summary>
        ''' Gets the total number of fields pulled
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property FieldCount() As Long
            Get
                Return _FieldCount
            End Get
        End Property

        ''' <summary>
        ''' Gets the total number of records returned
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property TotalRecords() As Long
            Get
                Return _TotRecords
            End Get
        End Property

        ''' <summary>
        ''' Gets the number of rows affected by the query
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property AffectedRows() As Long
            Get
                Return _AffRows
            End Get
        End Property

        ''' <summary>
        ''' Gets the return value from a stored procedure
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ReturnValue() As Object
            Get
                Return _RetVal
            End Get
        End Property

        ''' <summary>
        ''' Gets the returned XML
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ReturnXml() As XDocument
            Get
                Return _RetXml
            End Get
        End Property

        ''' <summary>
        ''' Set the Root Node Name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property RootNode() As String
            Set(ByVal value As String)
                _RootNode = value
            End Set
        End Property

        ''' <summary>
        ''' Set the parent node name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ParentNode() As String
            Set(ByVal value As String)
                _ParentNode = value
            End Set
        End Property

        ''' <summary>
        ''' Gets the total number of pages returned
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property TotalPages() As Long
            Get
                Return _TotPages
            End Get
        End Property

#End Region

#End Region

        Private i As Integer
        Private _ConnString As String, _CmdType As Integer, _Qry As String
        Private _ParamValues As Object, _ParamNames As Object, _RetVals As Object
        Private _HasReturn As Boolean, _RetParamName As String, _RetDataType As Object
        Private _ShouldPage As Boolean, _PageSize As Integer, _FieldCount As Long
        Private _TotRecords As Long
        Private _AffRows As Long, _RetVal As Object
        Private _RetXml As XDocument, _CurrPage As Integer, _TotPages As Long, _RootNode As String, _ParentNode As String

#End Region

        ''' <summary>
        ''' Execute a datareader object against the properties passed
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub ExecuteDataReader()
            Try
                Using objConn As New SqlConnection(_ConnString)
                    Using objCmd As New SqlCommand(_Qry, objConn)
                        Using objDA As New SqlDataAdapter()
                            Using objDS As New DataSet()
                                If IsArray(_ParamValues) And IsArray(_ParamNames) Then
                                    If UBound(_ParamValues) = UBound(_ParamNames) Then
                                        'Prepare our parameters
                                        For Me.i = 0 To UBound(_ParamValues)
                                            objCmd.Parameters.AddWithValue(_ParamNames(i), _ParamValues(i))
                                        Next
                                        Erase _ParamValues : Erase _ParamNames
                                        If _ShouldPage Then
                                            objCmd.Parameters.AddWithValue("@CurrentPage", _CurrPage)
                                            objCmd.Parameters.AddWithValue("@PageSize", _PageSize)
                                            _TotPages = Math.Ceiling((GetTotalRecords() / _PageSize))
                                        End If
                                        objCmd.CommandType = _CmdType
                                        objCmd.Connection.Open()
                                        objDA.SelectCommand = objCmd
                                        objDA.Fill(objDS, "Tbl")
                                        Dim DT As DataTable = objDS.Tables("Tbl")
                                        _FieldCount = DT.Columns.Count
                                        _TotRecords = DT.Rows.Count
                                        Dim c As Integer = 0, r As Integer = 0
                                        Dim tmpRetVals(FieldCount - 1, TotalRecords - 1) As Array
                                        For c = 0 To FieldCount - 1
                                            For r = 0 To TotalRecords - 1
                                                tmpRetVals(c, r) = New String() {DT.Columns.Item(c).ColumnName, DT.Rows(r)(c).ToString()}
                                            Next
                                        Next
                                        DT = Nothing
                                        objCmd.Parameters.Clear()
                                        _RetVals = tmpRetVals
                                        Erase tmpRetVals
                                    End If
                                End If
                            End Using
                        End Using
                    End Using
                End Using
            Catch SqlEx As SqlException
                Dim objErr As New TurboKits.ErrorManager
                objErr.Location = "Database - ExecuteDataReader"
                objErr.Notes = "Query: " & _Qry
                objErr.Stack = SqlEx.StackTrace()
                objErr.Except = SqlEx
                objErr.ParseError()
                objErr = Nothing
            Catch ex As Exception
                Dim objErr As New TurboKits.ErrorManager
                objErr.Location = "Database - ExecuteDataReader"
                objErr.Notes = "Query: " & _Qry
                objErr.Stack = ex.StackTrace()
                objErr.Except = ex
                objErr.ParseError()
                objErr = Nothing
            End Try
        End Sub

#Region "Extras"

        ''' <summary>
        ''' Get the total number of records
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function GetTotalRecords() As Long
            Dim objDB As New Database
            With objDB
                .ConnectionString = Common.ConnString
                .Query = _Qry
                .ParamNames = _ParamNames
                .ParamValues = _ParamValues
                .ExecuteDataReader()
                If .TotalRecords > 0 Then
                    Return .TotalRecords
                Else
                    Return 0
                End If
            End With
            objDB = Nothing
        End Function

#End Region

#Region "Threading"

        Public Sub ExecuteDR()
            Dim objT As New Thread(New ParameterizedThreadStart(AddressOf ExecuteDataReader))
            With objT
                .Name = "DBReader"
                .Priority = ThreadPriority.Normal
                .SetApartmentState(ApartmentState.MTA)
                .Start()
            End With
        End Sub

#End Region

    End Class

End Namespace

Open in new window

0
Comment
Question by:kevp75
  • 7
  • 4
11 Comments
 
LVL 13

Accepted Solution

by:
AngryBinary earned 500 total points
Comment Utility
You are attempting to use threading in an ASP.NET page, so what is going on is once your DB thread branches off, the ASP.NET page continues running on the main thread and writes output to the response stream. In other words, your page loads, runs, and sends before your second thread has gotten any results. It will continue to run and complete the code you've written for the thread, but the user won't be able to see any results.

You need to devise an asynchronous means of checking on the thread. One way to do it would be to store the status and/or result of a threaded operation in a database table, write a page that checks on this status, and refresh that page until it's complete and the results are there. Another way would be to store the results in the user's Session, and similarly poll the Session by refreshing a page until the results are there.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
german what?  It would be sort of pointless to do that wouldn't it?  Isn't the whole point of threading to make the page faster?

sorry new to the whole threading thing...

What about delegates?   Is that the same as threading?
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
Nah, I guess delegates are not the same thing.

I tried using it, but there was no threading results.
0
 
LVL 13

Assisted Solution

by:AngryBinary
AngryBinary earned 500 total points
Comment Utility
The whole point of threading is to perform two or more tasks in parallel, particularly when those tasks take a non-trivial amount of time to complete and don't necessarily require the same resources (for instance, one thread that waits for a database query while another one re-draws the UI).

If you spawn a new thread during a page request, the server will continue to serve the page while the new thread runs. So, yes, the page completes faster because you are able to write to the Response stream while the query is still being run on the database server. However, if you need to wait for the process to complete before serving the page to the browser (i.e., the page gets some info from the database that you need to display on the page), threading is only going to complicate your project and not provide any actual benefit.

Delegates are a programming feature similar to function pointers. They're typically used with event handling, which can be used to send notification from one thread to another, like in a multi-threaded application where a user clicks a button that lives in the UI thread on a desktop application, and that fires an event which is wired to a delegate in another thread that causes some code to run in the background. That isn't directly related to the subject of threading, but can be considered in a multi-threaded design. Usage of events and delegates doesn't provide a benefit in terms of performance on it's own, but can cause the perception of better performance because the UI remains responsive while stuff happens in the background. This doesn't apply to web applications.

So, you want the page to load faster. First, you need to define what that means - your page presumably fetches some data from a database and displays that in a control on the page. If you want to reduce the amount of time it takes to get that data and bind it to the page, threading can only be used to speed things up in very specific scenarios - i.e., you can thread multiple database queries simultaneously against multiple database servers at DIFFERENT locations - but that doesn't look like the case here. Here, you probably want to focus performance tuning efforts on making the query run efficiently through well structured SQL and proper use of database indexing.

If, though, the case is you want the user to get SOME sort of page output to look at quickly, and can wait for the data to come later. a better solution would be to load the page without a call to the database and to fetch the data using AJAX. That would actually give you an experience similar to the multi-threaded desktop application.

I just think you may have started off in the wrong direction.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
well...the thing is that I make multiple calls using this class per page, so I figured that threading the calls would do the trick I was after.

Another situation I would this would be to fire off multiple update/insert/delete storedprocedures at the same time  For which, I have another routine in that class strictly for nonreturning data.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Author Comment

by:kevp75
Comment Utility
so basically...the question really is, how can I fire off this twice at the same time...
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
p.s.  officially I am trying to ustilize threading in a class assembly, that is used in an asp.net web site ;)
0
 
LVL 13

Assisted Solution

by:AngryBinary
AngryBinary earned 500 total points
Comment Utility
It does make sense to do this for calls that don't return data. You just won't be able to provide any feedback on the page regarding whether or not the call was successful or how many rows were affected (although, of course, any error handling routines you include will still run) unless you poll for that information.

For queries that do return data, running them in separate threads will not make them return any faster if you are running them against database(s) on the same hardware unless they are using separate connections to the database and the database has multi-core CPU. The reason being, if multiple threads are using the same resource, they still have to execute their units of work synchronously. In fact, there is additional overhead incurred  by thread management.

As you've written it, you should be able to create multiple threads at once without a problem. Just expect TotalRecords to be empty (or incorrect) at the point that you are attempting to return a value for it. If you need to get that value, you will have to come up with a way to retrieve it after all the threads have completed.
0
 
LVL 25

Author Comment

by:kevp75
Comment Utility
Agreed.  (you've got the points and the grade ;-))  But can you explain what I lay out below?  I can;t  LOL

I did some testing, and hopefully this will come in handy for someone else as well.  And this would be a great discussion...

My test scenario consists of SQL 2008, .NET 3.5, IIS 6 on the same machine.  (P4 HT, 2GB RAM, 10kRPM 150G Drive)

I will post the code I used below...but here are the basics of it, and my findings.

I execute a stored procedure against a test table in my db that simply does a single field insert.  I setup a test page with System.Diagnostics.Stopwatch to time the execution of each scenario.

Scenario 1 Uses threading
Scenario 2 Uses delegates
Scenario 3 Uses nothing but the code :)

I use 2 loops in each procedure as you will see below to produce 1,000 records from each. (and to create multiple threads in Scenario 1)

What I found was Scenario 2 performed better than Scenario 1, and Scenario 2 performed slightly better than Scenario 3.

Results in the screen shot, code to follow.

 Screenshot
Part of my db class (ExecuteNDQuery does the actual work, and simply runs stored procedures):

        Public Sub ExeNDQueryThreaded(ByVal intThr As Long)
            Dim objT As New Threading.Thread(AddressOf ExecuteNDQuery)
            With objT
                .Priority = Threading.ThreadPriority.Normal
                .SetApartmentState(Threading.ApartmentState.MTA)
                .Name = "DBThread_" & intThr
                If Not (.ThreadState = Threading.ThreadState.Running) Then
                    .Start()
                End If
            End With
            objT = Nothing
        End Sub

        Private Delegate Sub MyDel(ByVal obj As Object)
        Public Sub ExeNDQueryDelegate()
            Dim objDel As New MyDel(AddressOf ExecuteNDQuery)
            With objDel
                .Invoke(Nothing)
            End With
            objDel = Nothing
        End Sub

Open in new window

test.aspx.vb


Partial Class test
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim tmpXml = <table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid #000;">
                         <tr>
                             <td width="33%"><h2>Threaded</h2></td>
                             <td width="33%"><h2>Delegated</h2></td>
                             <td width="33%"><h2>Non-Threaded</h2></td>
                         </tr>
                         <tr>
                             <td><%= Test1() %></td>
                             <td><%= Test2() %></td>
                             <td><%= Test3() %></td>
                         </tr>
                     </table>
        Response.Write(HttpUtility.HtmlDecode(tmpXml.ToString()))
    End Sub

    Private Function Test1() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x As Long = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 1." & x & ": " & i}
                    .ExeNDQueryThreaded(x)
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function

    Private Function Test2() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 2." & x & ": " & i}
                    .ExeNDQueryDelegate()
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function


    Private Function Test3() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 3." & x & ": " & i}
                    .ExecuteNDQuery()
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function


End Class

Open in new window

0
 
LVL 13

Assisted Solution

by:AngryBinary
AngryBinary earned 500 total points
Comment Utility
The difference between your Delegate and non-Delegate/non-Threaded results are trivial, and if you were to run it a dozen more times, you'd probably see tiny fluctuations one way or the other either way. The reason being, whether or not you use a delegate, you are essentially doing the exact same thing. Consider the following pseudo-code examples...

Without a delegate:

    someClass.someMethod();

With a delegate:

    someClass.delegateHandler = someClass.someMethod;
    someClass.delegateHandler();

If anything, the delegate method results in a few more lines of code, but functionally is identical. Delegates are a solution for handling events in a generic way - it makes your code modular and makes classes reusable in different scenarios. It does not make your code faster.

The results for threading are what I'd expect. Creating threads takes time, so there is an overhead involved in multi-threading processes. Here are some conditions where multi-threading will actually benefit performance:

1. You have multiple tasks to perform
2. The tasks don't require the same resources (for instance, you have multi-core CPU, or one task waits on network traffic while the other one utilizes the file system)
3. The benefit of running both tasks simultaneously outweighs the overhead involved in creating and destroying the threads needed to run them in parallel (i.e., the tasks are not trivial)

0
 
LVL 25

Author Closing Comment

by:kevp75
Comment Utility
You have been a saint, and I appreciate the descriptive responses.  It gave me some serious insight to what I am trying to achieve here.   Actually, it made me realize that I am going about what I am trying to do the wrong way, however it made me realize what the right way would be, so I am now on the right track.

Thanks again!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
VB.NET Server side Javascript 8 51
Custom auto number 6 51
Check if number is currency 15 29
VB.Net - CSV to Oracle table 4 27
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now