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.

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


"Rob R. Ainscough" <(E-Mail Removed)> wrote in message
news:%23OSuDEA%(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:#OSuDEA#(E-Mail Removed)...
> 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.
>
>

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.

"Amit" <(E-Mail Removed)> wrote in message
news:OspNkjA%(E-Mail Removed)...
> 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
>
>
> "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
> news:%23OSuDEA%(E-Mail Removed)...
>> 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.
>>
>>
>
>

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



"Rob R. Ainscough" <(E-Mail Removed)> wrote in message
news:#exxn#A#(E-Mail Removed)...
> 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.
>
> "Amit" <(E-Mail Removed)> wrote in message
> news:OspNkjA%(E-Mail Removed)...
> > 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
> >
> >
> > "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
> > news:%23OSuDEA%(E-Mail Removed)...
> >> 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.
> >>
> >>
> >
> >
>
>

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" <(E-Mail Removed)> wrote in message
news:uk%23t$TD%(E-Mail Removed)...
> 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
>
>
>
> "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
> news:#exxn#A#(E-Mail Removed)...
>> 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.
>>
>> "Amit" <(E-Mail Removed)> wrote in message
>> news:OspNkjA%(E-Mail Removed)...
>> > 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
>> >
>> >
>> > "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
>> > news:%23OSuDEA%(E-Mail Removed)...
>> >> 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.
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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


"William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
news:eE$2ptD%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:uk%23t$TD%(E-Mail Removed)...
>> 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
>>
>>
>>
>> "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
>> news:#exxn#A#(E-Mail Removed)...
>>> 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.
>>>
>>> "Amit" <(E-Mail Removed)> wrote in message
>>> news:OspNkjA%(E-Mail Removed)...
>>> > 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
>>> >
>>> >
>>> > "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
>>> > news:%23OSuDEA%(E-Mail Removed)...
>>> >> 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.
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

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

--
Message posted via http://www.dotnetmonster.com

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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

"Sahil Malik" <(E-Mail Removed)> wrote in message
news:eWfzqRG%(E-Mail Removed)...
> Bill .. if I have a query like Select * from A, and another like Select
> count(*) from A .. won't the count(*) be MUCH faster?

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" <(E-Mail Removed)> wrote in message
news:eWfzqRG%(E-Mail Removed)...
> 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
>
>
> "William (Bill) Vaughn" <(E-Mail Removed)> wrote in message
> news:eE$2ptD%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:uk%23t$TD%(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>> "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
>>> news:#exxn#A#(E-Mail Removed)...
>>>> 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.
>>>>
>>>> "Amit" <(E-Mail Removed)> wrote in message
>>>> news:OspNkjA%(E-Mail Removed)...
>>>> > 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
>>>> >
>>>> >
>>>> > "Rob R. Ainscough" <(E-Mail Removed)> wrote in message
>>>> > news:%23OSuDEA%(E-Mail Removed)...
>>>> >> 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.
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>