how does SqlDataReader work (internally)?

[Sergei Shelukhin]

Hi.
I have a couple of search queries that need to retrieve data from
table Users, returning exactly N rows
However, several rows of additional data from table UserSomething need
to be retrieved for each User record, and there are also matching
conditions, for ex. "Users found must have at least one Something
that current user also has", so I have to join with two UserSomething
tables to find matches (joins work much faster than IN (...) queries
for this case).

This leads to a lot of duplicate rows from joins. Currently I get TOP
20*required number of users rows to be sure that N users land within
this result set into a datatable (getting all matches would be an
overkill), and then proccess it until N users with all their data are
retrieved. 20 seems to work, I can probably decrease it but it needs
checking.

I was wondering if I could speed it up and make it less ugly by using
SqlDataReader, e.g. launch the same query *without* TOP N*20 and then
read() until I have got my users. How does SqlDataReader work? Does it
use some kind of serverside cursor and optimization, so data is
retrieved as needed, or does it get the whole result set first and
then serve me row by row?
Will it increase speed in this case?

 

[RobinS]

SqlDataReader is basically an open faucet. It only reads one record at a
time, not the whole result set, and it does it fast. As long as you don't
need to do any updates, this will be faster.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.