Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is tha
t
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either
.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.