Optimizing SQL data reader

Question

I am using SQl data reader to get value from SQL database.

Language VB.NET

After getting data into reader, i run a while loop

   While reader.Read

      If reader.HasRows() Then

/* Proessing of data */

      End If

  End While

I observe that the while loop takes a lot of time to process as there are many rows. Is there any better way to implement.

Please comment on: Should i get the SQlDataReader data into arraylists, and then process data using the arraylists?

Algo:

 While reader.read

    /* Put data into Arraylists */

    End While

    for(arraylist row count)
    /*Process data*/
    end for

Answer 1

Could you place the logic of the loop into SET based logic within the TSQL?

So, rather than iterate through each row in code, have the RDBMS execute it in TSQL. This is what RDBMS's are designed for and are good at.

Please tell us what you are doing in the loop and what TSQL is used to generate the dataset.

Answer 2

It's pointless to check if the data reader has rows inside the loop. If there are no rows, the Read method returns false so it will not enter the loop, and if there are rows the HasRows method returns true even when you have read all rows. Removing it will save you some tiny bit of time, but hardly noticable:

While reader.Read

  /* Proessing of data */

End While

Reading all the data into a list before processing will not give you any better performance. On the contrary you will be using a lot of memory that might be better used for something else.

Answer 3

Don't assume that reading the data from the database is what's taking a lot of time.
You can measure the time elapsed in a block of VB code like:

startTime = Now
....
stopTime = Now
elapsedTime = stopTime.Subtract(startTime)

Measure first, and then optimize the thing that's really taking time.

(P.S. if you're using Team Studio, you could try the built-in profiler.)