0

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
Improve this question
2
  • 1
    It would be interesting to know what exactly you're trying to achieve in the while loop. Commented Aug 10, 2009 at 4:45
  • 1
    How many rows are you dealing with? What kind of information is stored in the rows? What kind of processing are you doing to each row item? Putting the items into an ArrayList (you should also consider using generics instead of ArrayList) then doing the processing on them is guaranteed to be slower. Is there a reason you can't page the data you're returning? Commented Aug 10, 2009 at 4:48

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

0

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.)

Improve this answer

1 Comment

The system clock has really low resolution, so it will only be usable if you measure something at least in the range of seconds. To measure shorter amount of times you can use the System.Diagnostics.Stopwatch class.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.