Bdtjtk

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:USERSEARCH-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.

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.