multi thread vb app to pull data from webservice

[miaminemo]

Guys -

This application uses a list of userIDs in my database to pull XML
back from a web service with user information. Each time it hits the
web service it takes about 3 seconds to pull back the xml and open
it.

Would it be possible to multi-thread this application so that i would
send thousands of requests to the web service at a time? (as many as
my processor/memory could handle)

Im basically looking for a faster way other than running it multiple
times myself with different start points

Will

Code so far: (works, but slow)

Imports System.Xml
Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form
Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.Button1 = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(112, 64)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(80, 48)
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 266)
Me.Controls.Add(Me.Button1)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Public Function getXMLCallUp(ByVal userID As String) As
XmlDocument
Try
Dim m_xmlr As XmlTextReader
Dim xmlDoc As New XmlDocument
Dim aspUserName, userDomain, userInfo As String
Dim nodeFullName, nodeCompany, nodePhoneNumber, nodeDept
As XmlNode
Dim strFullName, strCompany, strPhoneNumber, strDept As
String
Dim nodeMgrID, nodeMgrFullName, nodeMgrPhoneNumber,
nodeMgrUserID As XmlNode
Dim strMgrID, strMgrFullName, strMgrPhoneNumber,
strMgrUserID As String
'aspUserName = Principal.WindowsIdentity.GetCurrent.Name
'userDomain = Left(authUserName, InStr(authUserName, "\")
- 1)
'userid = Mid(authUserName, InStr(authUserName, "\") + 1)
m_xmlr = New XmlTextReader("http://zservices.nwie.net/
callup?xml=1&userid=" & userID)
xmlDoc.Load(m_xmlr)
''Get Requester FullName
'nodeFullName = xmlDoc.SelectSingleNode("/CallUP/Customer/
FullName")
'strFullName = nodeFullName.InnerText.Trim
'm_xmlr.Close()
'userInfo = userID & "@#@" & strFullName
''Return xmlDoc
'Dim ds As New DataSet
'Dim sReader As New StringReader(xmlDoc.InnerXml)
'ds.ReadXml(sReader)
'Return ds
Return xmlDoc
Catch ex As Exception
Return Nothing
End Try
End Function
Shared Function GetResults(ByVal SQLQuery As String, ByVal
connectString As String) As DataTable
'generic function to pull back results of sqlquery
Dim conn As New SqlConnection(connectString)
Dim dt As New DataTable
Dim da As New SqlDataAdapter(SQLQuery, conn)
Try
da.Fill(dt)
da.Dispose()
conn.Close()
conn.Dispose()
Catch
dt = GetResultsRetry(SQLQuery, connectString, True)
End Try

Return dt
End Function
Shared Function GetResultsRetry(ByVal SQLQuery As String, ByVal
connectString As String, ByVal retry As Boolean) As DataTable
'generic function to pull back results of sqlquery
Dim conn As New SqlConnection(connectString)
Dim dt As New DataTable
Dim da As New SqlDataAdapter(SQLQuery, conn)
If retry = True Then
If MsgBox("SQL error, retry or cancel?",
MsgBoxStyle.RetryCancel) = MsgBoxResult.Retry Then
Try
da.Fill(dt)
da.Dispose()
conn.Close()
conn.Dispose()
Catch
dt = GetResultsRetry(SQLQuery, connectString,
True)
End Try
Else
dt = Nothing
End If
Else
da.Fill(dt)
da.Dispose()
conn.Close()
conn.Dispose()
End If
Return dt
End Function
Shared Function executeCommand(ByVal sqlQuery As String, ByVal
connectString As String) As Integer
'returns number of rows affected
'generic function to execute and sql commands

Dim conn As New SqlConnection(connectString)
Dim myCommand As New SqlCommand(sqlQuery, conn)
Dim dr As Integer

Try
myCommand.Connection.Open()
dr = myCommand.ExecuteNonQuery()
conn.Close()
myCommand.Dispose()
Catch ex As Exception
dr = executeCommandRetry(sqlQuery, connectString, True)
End Try
Return dr
End Function
Shared Function executeCommandRetry(ByVal sqlQuery As String,
ByVal connectString As String, ByVal retry As Integer) As Integer
'returns number of rows affected
'generic function to execute and sql commands

Dim conn As New SqlConnection(connectString)
Dim myCommand As New SqlCommand(sqlQuery, conn)
Dim dr As Integer
If retry = True Then
If MsgBox("SQL error, retry or cancel?",
MsgBoxStyle.RetryCancel) = MsgBoxResult.Retry Then
Try
myCommand.Connection.Open()
dr = myCommand.ExecuteNonQuery()
conn.Close()
myCommand.Dispose()
Catch ex As Exception
dr = executeCommandRetry(sqlQuery, connectString,
True)
End Try
Else
dr = Nothing
End If
Else

End If

Return dr
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim NSCConnectString As String, sqlQuery As String, sqlStmt As
String, RC As Integer
Dim userID As String
Dim dr As DataRow
Dim tempXML As XmlDocument
Dim xmlUserID As String, xmlFullName As String, xmlPhoneNum As
String, xmlBusLoc As String, xmlState As String, xmlMailCode As String
NSCConnectString = "Data Source=ohcoldbp0007;Initial
Catalog=SMS_NSC;Integrated Security=SSPI;"
sqlQuery = "Select distinct user_name0 from v_r_system where
user_name0 is not null and user_name0 != '' and user_name0 >=
'burdead'"
Dim CallupDT As DataTable = GetResults(sqlQuery,
NSCConnectString)
If CallupDT Is Nothing Then
MsgBox("SQL returned error: " & sqlQuery,
MsgBoxStyle.Information)
Exit Sub
End If
For Each dr In CallupDT.Rows
userID = dr("user_name0")
tempXML = getXMLCallUp(userID)
'tempXML = getXMLCallUp("abdhulh")
'tempXML.ge()
If Not tempXML.SelectSingleNode("CallUP/Customer") Is
Nothing Then
'get the nodes associated with the XML record
xmlFullName = Replace(tempXML.SelectSingleNode("/
CallUP/Customer/FullName").InnerXml, "'", "")
xmlPhoneNum = Replace(tempXML.SelectSingleNode("/
CallUP/Customer/PhoneNumber").InnerXml, "'", "")
xmlBusLoc = Replace(tempXML.SelectSingleNode("/CallUP/
Customer/BusinessLoc").InnerXml, "'", "")
xmlState = Replace(tempXML.SelectSingleNode("/CallUP/
Customer/BusinessState").InnerXml, "'", "")
xmlMailCode = Replace(tempXML.SelectSingleNode("/
CallUP/Customer/MailCode").InnerXml, "'", "")

'the business location is

'insert the row into the database
sqlStmt = "insert into CallUp (userid, fullName,
Phone, businessLoc, state, mailCode) values ('" & userID & "','" &
xmlFullName & "','" & xmlPhoneNum & _
"','" & xmlBusLoc & "','" & xmlState & "','" &
xmlMailCode & "')"
RC = executeCommand(sqlStmt, NSCConnectString)
End If

Next



End Sub
End Class

 

[Guest]

Would it be possible to multi-thread this application so that i would
send thousands of requests to the web service at a time? (as many as
my processor/memory could handle)

Yes - but you'll probably swamp the web service.

Im basically looking for a faster way other than running it multiple
times myself with different start points

Can you cache the data on your end? Any other integration options?
database? web services can be quite slow ... and may not be the best choice
if you want performance and high throughput.

 

[Cor Ligthert[MVP]]

Miaminemo,

This has only sense if the webservice has a build in delay per user build
in. If that is not the case, then you are for sure only slowing the process
down with a lot of chance on errors.

Cor

 

[miaminemo]

Flooding the web service is not a concern to me. The site with the
service services thousands of hits every hour. Also, the holder of the
data source refuses to provide me with a feed of data - stating that
the data is available one record at a time. If I happen to flood it,
maybe they will be more inclined to provide me with a feed of the
data.

I dont understand how cacheing the data on my end would improve
performance. The SQL read/write are not the slow down, the data
capture from the web service is.

Any insight on how I could spawn of numerous threads to accomplish
this more (time) efficiently?

Miaminemo

 

[Guest]

Also, the holder of the
data source refuses to provide me with a feed of data - stating that
the data is available one record at a time. If I happen to flood it,
maybe they will be more inclined to provide me with a feed of the
data.

You may have to swamp the web service in that case!

I dont understand how cacheing the data on my end would improve
performance. The SQL read/write are not the slow down, the data
capture from the web service is.

Can you pre-download information throughout the day and do user lookups
locally?

Any insight on how I could spawn of numerous threads to accomplish
this more (time) efficiently?

You have a couple ways:

1. Use async web service calls - i.e. the
BeginXXXFunction/EndXXXFunction calls in the web proxy
2. Use the thread pool
3. Manually create threads

If you want to send out 1000 threads, you'll need to spawn manual
threads. Using the thread pool you'll only be able to have 25 concurrent
running threads - the rest will be queued. Async calls are good, but
with this many threads you don't have very much control over the status
of each thread.

So perhaps create an array of thread - which in turn calls the web
service functions you need?

 

[Guest]

This has only sense if the webservice has a build in delay per user
build in. If that is not the case, then you are for sure only slowing
the process down with a lot of chance on errors.

Web services do have an inherrent delay Serialize, Transmit, Receive,
Deserialize are all very slow... so I think multiple threads will make an
impact. However, 1000 threads as the OP mentioned is overkill :-D

 

[Patrice]

You could use the Async version of the calls so that your client application
can do something else (if it can) while waiting for the response.