how does SqlDataReader work (internally)?

4 Feb 2007 12:21 AM

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?

4 Feb 2007 2:26 AM

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.
-----------------------------------------------
Show quote

"Sergei Shelukhin" <realg***@gmail.com> wrote in message
news:1170548511.974272.263520@a34g2000cwb.googlegroups.com...
> 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?
>