Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Not waiting for a Stored Procedure to Finish

    I have a Stored Procedure which Ideally should run when a Customer Logs in, the Procedure will check the available stock and create a Temp Table for the Information, which allows many other Queries in the site to run a lot faster, (due to no joins). The Query has taken as much as 30seconds (Lots of Records and 1/2 dozen Joins) to run upon log in and causing a Timeout for the web application.

    I want the procedure to run as it is, but for the login method to not be dependent on the Process tried this in .NET cmd.BeginExecuteNonQuery() (cmd=SQLCOmmand)
    which doesn't do what I want it just allows me to run heaps of QUeries at the same time.

    Can anyone help me with getting this procedure to run and not hold up the Web application? Not sure whether I need to do this in .NET, or whether I can get .NET to run a Batch File or something, but someone must have had a similiar problem, please help.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you could just run it in a separate worker thread. since you are using .NET, you could use ThreadPool.QueueUserWorkItem() for this.

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Thank you

    Thanks it was just what I was looking for, for anyone else that has this problem:

    PHP Code:
    '---
    Somewhere in the Code call the item
        Dim ti As New TaskInfo(Conn, UserData.CustomerID)

                    '
    Invoke ASYNC Webservice
                    ThreadPool
    .QueueUserWorkItem(New WaitCallback(AddressOf UpdateTempDetails_ForCustomerWebQuote), ti)

    '----

     Public Class TaskInfo
            Public Conn As SqlConnection
            Public CustomerID As Integer
            Public Sub New(ByVal Connect As SqlConnection, ByVal ID As Integer)
                Conn = Connect
                CustomerID = ID
            End Sub
        End Class

        Public Sub UpdateTempDetails_ForCustomerWebQuote(ByVal info As Object)
            '
    This should only be used for updates
            
    'see webservices for other options
            Dim ti As TaskInfo = CType(info, TaskInfo)
            Dim cmd As New SqlCommand("UpdatePartTypeDetailsTable", ti.Conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@CustomerID", ti.CustomerID)
       
                Dim count As Integer = 0
                Dim result As IAsyncResult = cmd.BeginExecuteNonQuery()
                While Not result.IsCompleted
                    ' 
    Wait for 1/10 secondso the counter
                    
    ' does not consume all available resources 
                    ' 
    on the main thread.
                    
    Threading.Thread.Sleep(2000)
                    
    count += 1
                End 
    While
                
    cmd.EndExecuteNonQuery(result)

          
        
    End Sub 

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    glad it worked for you.

    minor point, but your "wait for 1/10 second" comment is incorrect as Thread.Sleep() takes an int in milliseconds, so you are sleeping for 2 sec each time through the loop.

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Yeah fair call made some changes use to say 100,
    While your paying attention, is there anything I should be concerned about memory usage of ThreadPool.QueueUserWorkItem, have had a look round couldn't find anything?

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I use the threadpool quite a lot and have not had problems with it. It's more convenient than managing your own threads for stuff like this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •