Reputation: 210
I'm currently working with a database of over 50 million records, where I read a file which a person wants to search the database for etc. I have noticed my data reader part is running particularly slow, where as the query seems almost instant (database is indexed). I was just wondering does anyone know as to why it seems to be running slow?
con.Open() Using sw As New StreamWriter("G:\USER\SEARCH-RESULTS.txt") Try
For Each word As String In result
Using com As New SqlCommand("select t.SmeNbr, t.FilPth, r.MaxDate, t.DteAdd, t.LnePos from (Select SmeNbr, MAX(FilDte) as MaxDate from Test_Table where SmeNbr = @word group by SmeNbr)r inner join Test_Table t on t.SmeNbr = r.SmeNbr and t.FilDte = R.MaxDate", con)
com.Parameters.AddWithValue("@word", word)
Using RDR = com.ExecuteReader
If RDR.HasRows Then
Do While RDR.Read
MyFilePath = RDR.Item("FilPth").ToString()
linePos = RDR.Item("LnePos").ToString()
Using sr As New StreamReader(MyFilePath)
sr.BaseStream.Seek(4096 * (linePos - 1), SeekOrigin.Begin)
FoundWords.Add(sr.ReadLine)
For Each item As String In FoundWords
sw.WriteLine(item)
Next
FoundWords.Clear()
End Using
Loop
Else
Continue For
End If
End Using
End Using
Next
Catch ex As Exception
MessageBox.Show("Couldn't process search")
Finally
con.Close()
End Try
End Using
MsgBox("Complete!")
So it works perfect, as in it gets the records and bits of info I want very quickly through the query and all and even the writing reults to a new file is near instant, I used breakpoints and like I said it seems to take ages between the "Using RDR = com.ExecuteReader" and "If RDR.HasRows Then"
Any help or ideas would be greatly appreciated.
Upvotes: 0
Views: 1236
Reputation: 46425
com.Parameters.AddWithValue("@word", word)
AddWithValue
infers the parameter data type from the provided .NET object value. Since .NET strings are Unicode, this code is will add an nvarchar(n)
parameter with the length of the actual value. I see from your comments that the actual column data type is char(13)
so it would be best to explicitly specify that as the parameter data type:
com.Parameters.Add("@word", SqlDbType.Char, 13).Value = word
The implications with AddWithValue
are that indexes might not be used due to the mismatched data type and there may be many variations of the same query in the SQL Server procedure cache that differ only by length. For these reasons, I suggest one avoid AddWithValue.
Upvotes: 3