Slow DataReader

I’m working on an app that has to iterate through hundreds of thousands of records in a SQL database. Since VB.NET isn’t my strongest skill, and is required by the organization paying me to write it, I decided to look for suggestions on how exactly to do it. I initially thought I’d need to use a Do While Loop. I Googled for an example and found an article by Microsoft that seemed to have a good way to iterate through the records. It works great, as long as speed isn’t a priority. If you need speed, try a DO WHILE Loop. In my testing I found that it’s about 10x faster than the For Next loop. The sample code below may have some formatting issues due to text wrapping. Also I left out the declarations. Here’s the sample code I used to test the For Next loop: objSQLCmd = New SqlCommand("SELECT [Territory], [Company],” & _ “[Policyno], [Module], [Covatot], [CovctotS], [CovdtotS], [CovetotS],” & _ “[Yearbuilt], [Consttype], [Stories], [retrofit], [Covaded], [Term],” & _ “[TermS], [poltype1] FROM [DataBaseName]", objSQLConn) objSQLConn.Open() objSQLRdr = objSQLCmd.ExecuteReader While objSQLRdr.Read() Dim i As New Integer For i = 0 To objSQLRdr.FieldCount - 1 varTPErr = System.DateTime.Now.ToString("HH:mm:ss.ffff", Nothing) & _ "Testme" & " " varKeyCompany = objSQLRdr("Company") varKeyPolicyno = objSQLRdr("Policyno") varKeyModule = objSQLRdr("Module") 'begin Write objSQLCmdW = New SqlCommand("UPDATE “ & _ “[DataBaseName] SET [TerrErr] = @varTPErr” & _ “WHERE [Company] = @Company AND [Policyno] = @Policyno” & _ “AND [Module] = @Module", objSQLConnW) objSQLCmdW.Parameters.Add("@varTPErr", varTPErr) objSQLCmdW.Parameters.Add("@Company", varKeyCompany) objSQLCmdW.Parameters.Add("@Policyno", varKeyPolicyno) objSQLCmdW.Parameters.Add("@Module", varKeyModule) objSQLCmdW.Connection.Open() objSQLCmdW.ExecuteNonQuery() objSQLCmdW.Connection.Close() 'end Write Next End While objSQLRdr.Close() objSQLConn.Close() Here’s the sample code I used to test the Do While Loop: objSQLCmd = New SqlCommand("SELECT [Territory], [Company], [Policyno], [Module], [Covatot], [CovctotS], [CovdtotS], [CovetotS], [Yearbuilt], [Consttype], [Stories], [retrofit], [Covaded], [Term], [TermS], [poltype1] FROM [DataBaseName]", objSQLConn) objSQLConn.Open() objSQLRdr = objSQLCmd.ExecuteReader If objSQLRdr.HasRows Then Do While objSQLRdr.Read() varTrace = varTrace + 1 varTPErr = System.DateTime.Now.ToString("HH:mm:ss.ffff", Nothing) & " Testme" & " " & varTrace varKeyCompany = objSQLRdr("Company") varKeyPolicyno = objSQLRdr("Policyno") varKeyModule = objSQLRdr("Module") 'begin Write objSQLCmdW = New SqlCommand("UPDATE [DataBaseName] SET [TerrErr] = @varTPErr WHERE [Company] = @Company AND [Policyno] = @Policyno AND [Module] = @Module", objSQLConnW) objSQLCmdW.Parameters.Add("@varTPErr", varTPErr) objSQLCmdW.Parameters.Add("@Company", varKeyCompany) objSQLCmdW.Parameters.Add("@Policyno", varKeyPolicyno) objSQLCmdW.Parameters.Add("@Module", varKeyModule) objSQLCmdW.Connection.Open() objSQLCmdW.ExecuteNonQuery() objSQLCmdW.Connection.Close() 'end Write Loop Else Console.WriteLine("No rows returned.") End If objSQLRdr.Close() objSQLConn.Close()