CODEWITHSUNDEEP
sqlsql-servervb.netssms
K.Madden
K.Madden

Reputation: 210

DataReader running very slow

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

Answers (1)

Dan Guzman
Dan Guzman

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

Related Questions