DataReader - how to get record count without closing?

[Rob R. Ainscough]

I'm trying once again to accomplish something pretty simple, but it appears
the DataReader can only return accurate results in the .RecordsAffected
property if it is closed? In fact, on a SELECT based SQL query the
RecordsAffected always returns -1? Since the datareader is not very useful
to me closed, is there any other way to obtain a record count PRIOR to
cycling thru a datareader?

I'm using a datareader because it is very fast and I don't need to change
results and only need to move thru the returned information forward once --
as I understand it this is a matched situation for using a datareader.
However, I need to provide a progressbar where it's max value is based on
the returned rows in the datareader.

Am I missing something? (probably, as usual)

Thanks, Rob.

 

[Amit]

You said it - since the DataReader can only move forward, it doesn't know
how many rows there are until its reached the end and it is closed.

If you're using stored procedures, what you can do is return the total
number of rows as the first result set and then the actual records as the
second result set. If you don't want to modify the sql code then just get
the DataReader twice - first time to calculate the rows and second time to
look at the data. I admit though that this is not quite an efficient
solution.
-amit

 

[Sahil Malik]

You have to do a count(*) before executing the exact results. DataReader
will not give you the recordcount before hand.

One of the reasons it is so efficient is because it is simple and does one
thing and it does that one thing real good i.e. read stuff as a firehose
cursor - which means it cannot know much about what it hasn't even started
reading - much less the total count.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Rob R. Ainscough]

Thanks -- unfortunately the DataReader is very limited -- I was hoping it
would be close to Static recordset provided by DAO 3.6 (which is extremely
fast and provides a recordcount), unfortunately no such luck. I ended up
going with a DataAdapter & DataSet approach. Slower, but not providing my
user base with a some progress status would be even worse -- they have a
tendancy to reboot at the slightest hint of an "unresponsive" application.
So I guess I'll take the performance hit.

I was contemplating using a animated gif or some such to make it look as if
things are happening -- unfortunately my users wouldn't go for that, they
wanna see accurate progress. Oh well.

Speaking of which, is there any way to get a Progress update from a query
(i.e. SELECT) from SQL Server 2000? I have a few queries that take some
time to execute and it would do wonders for my sanity and my users if they
could get some indication of progress from SQL Server 2000 -- any such
method/property exists to monitor the progress of a query?

Thanks, Rob.

 

[Sahil Malik]

Rob .. a COUNT(*) is not as bad as retreiveing the entire result set .. it
is by far not that bad .. count(*)'s work fairly fast. That IMHO is your
best bet here.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[William \(Bill\) Vaughn]

Ah, yes, but it causes the entire query to run twice. For simple queries
this approach might (just might) return the right number, but for complex
queries it's a lot of work to repeat just to get an approximate count.
That's because the count returned is based on rows found during time X. When
you run the rowset-returning query at X+Y (sometime later), the number might
be different--different enough to break software that expects an exact
number. I don't approve of this approach for these reasons. For progress
meters I suggest the "Access" approach. They provide a progress bar and jump
down the bar 1/4 (or so) of the remaining distance every few seconds. This
way they never really get to the end. The Windows Installer approach simply
shows an incrementing progress bar that starts over--it gives the user some
indication that the system has not locked up.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik]

Bill .. if I have a query like Select * from A, and another like Select
count(*) from A .. won't the count(*) be MUCH faster?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Ritesh Jain via DotNetMonster.com]

Hi Sahil,

If it's just a matter to Retrieve the no.of Records in a particular Table w/o checking any condition...............

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('abc') AND indid < 2

will be much more faster than Select count(*) form abc ???

Regards,
Ritesh

 

[Miha Markic [MVP C#]]

Yes, but the problem here is that you'll have to run both queiries if you
want to get data and row count before getting that data.

 

[William \(Bill\) Vaughn]

Not really--especially when we're talking about "real" queries where the SQL
logic is not just pulling all the rows from a single table. When you have to
run through a 4-40-step process to locate the rows to return, it can be very
costly to repeat the process just to get a rowcount--and it might not work
the same way again.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Sahil Malik]

Well for complex queries or joins .. I agree there won't be much of a
difference, but I believe for a simple table it'll do a fast index scan (or
something like that).

Anyway .. bottom line .. I agree, it is two queries and two queries can
never be as fast as one.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik