Application Slows Down During Loop

nomadicprs

New member
Joined
Nov 20, 2009
Messages
1
Programming Experience
3-5
I have a small console application that runs through a list of stock symbols on a sql server. It fills a datatable with the symbols and for each symbol it imports a csv file into a table called symbolselctedprices. It starts out really really fast and then slows to a crawl. I noticed when running in debuger I get a a wierd msg that says something about contextswitchdeadlock. Anyway I'm kind of new to the vbnet thing so could someone take a look at the below code and tell me if something just jumps out as you as my problem.

Imports System.IO
Imports System.Data.SqlClient
Imports System.Net
Module Module1


Sub Main()
'On Error Resume Next
Dim url As String
Dim SymbolSelect As String

Dim DCon As New SqlConnection()

DCon.ConnectionString = myconnectionstring
DCon.Open()
'DCon2.Open()
Dim dt2 As New DataTable("Symbols")
Dim dt As New DataTable()
Dim sql As String
sql = "Select [Symbol] From Symbols"
Dim objCommand As New SqlCommand(sql, DCon)
Dim da As New SqlDataAdapter(objCommand)
da.Fill(dt2)
Dim dr As DataRow
Dim line As String = Nothing
Dim i As Integer = 0
Dim data() As String
Dim row As DataRow
For Each dr In dt2.Rows
data = Nothing
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
SymbolSelect = dr("Symbol")
Console.WriteLine(SymbolSelect)

dt.Clear()
dt.Dispose()
objCommand.Dispose()






line = Nothing
i = 0
Using sr As StreamReader = File.OpenText("C:\Stocks\" & SymbolSelect & ".csv")
line = sr.ReadLine()
line = sr.ReadLine()
Do While line IsNot Nothing
data = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
row = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop

End Using

Using copy As New SqlBulkCopy(DCon)
copy.ColumnMappings.Add(0, 0)
copy.ColumnMappings.Add(1, 1)
copy.ColumnMappings.Add(2, 2)
copy.ColumnMappings.Add(3, 3)
copy.ColumnMappings.Add(4, 4)
copy.ColumnMappings.Add(5, 5)
copy.ColumnMappings.Add(6, 6)
copy.DestinationTableName = "TempPrices"
copy.WriteToServer(dt)
End Using

sql = "Begin Transaction Insert Into stockquotes.dbo." & SymbolSelect & "Prices (DateC, OpenP, High, Low, [Close], Volume, AdjClose) Select DateC, OpenP, High, LOW, [Close], Volume, AdjClose FROM Master.dbo.TempPrices Commit;"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
Console.WriteLine(SymbolSelect & " Updated")

Next
 

Latest posts

Back
Top