Select chunk at a time ???

Select chunk at a time ???

Post by John » Thu, 13 Dec 2001 22:08:54



Hi all,

If I have a table with around 12500 records in it, is it possible to select
x rows at a time. I know of Select TOP 500, but would like to be able to
execute a "Select row 501 to 1000 from  file" or "Select row 6501 to 7000
from  file". Is this possible to do using SQL?

Regards
John.

 
 
 

Select chunk at a time ???

Post by Dinesh T » Thu, 13 Dec 2001 22:36:25


John,

Copy the results to a temporary table and select from that.

Example :

select *, sortfield=identity(int,1,1)
into #temptable
from yourtable
where <some filter>
order by column

--then do a select

select *
from #temptable
where sortfield between 500 and 1000

the above solution can be taxing since you may need to create a big
temporary table.But it wont matter if you do some sort of filter.
Otherwise you can do something like :

--example to return rows 30 -50
select top 20 *
from yourtable
where id not in
            (select top 20 id
              from yourtable
              order by id
            )
order by id

Dinesh.


Quote:> Hi all,

> If I have a table with around 12500 records in it, is it possible to
select
> x rows at a time. I know of Select TOP 500, but would like to be able to
> execute a "Select row 501 to 1000 from  file" or "Select row 6501 to 7000
> from  file". Is this possible to do using SQL?

> Regards
> John.


 
 
 

Select chunk at a time ???

Post by Steve Kas » Thu, 13 Dec 2001 23:38:37


John,

  Here is a solution to your problem that doesn't require
a temporary table or identity().  It does require that the
columns you want to order by can be extended to a
candidate key (a group of columns whose values
are unique).


as
  set nocount on
  DECLARE C CURSOR DYNAMIC SCROLL READ_ONLY FOR
  SELECT orderID, productID
  FROM NORTHWIND..[ORDER DETAILS]
  WHERE ORDERID%7 > 1
  ORDER BY ORDERID


  OPEN C


  SELECT * FROM NORTHWIND..[ORDER DETAILS]


  ORDER BY orderID, productID
  SET ROWCOUNT 0
  CLOSE C
  DEALLOCATE C
go

exec sk_FetchMiddle 1000, 14

Steve Kass
Drew University


> John,

> Copy the results to a temporary table and select from that.

> Example :

> select *, sortfield=identity(int,1,1)
> into #temptable
> from yourtable
> where <some filter>
> order by column

> --then do a select

> select *
> from #temptable
> where sortfield between 500 and 1000

> the above solution can be taxing since you may need to create a big
> temporary table.But it wont matter if you do some sort of filter.
> Otherwise you can do something like :

> --example to return rows 30 -50
> select top 20 *
> from yourtable
> where id not in
>             (select top 20 id
>               from yourtable
>               order by id
>             )
> order by id

> Dinesh.



> > Hi all,

> > If I have a table with around 12500 records in it, is it possible to
> select
> > x rows at a time. I know of Select TOP 500, but would like to be able to
> > execute a "Select row 501 to 1000 from  file" or "Select row 6501 to 7000
> > from  file". Is this possible to do using SQL?

> > Regards
> > John.

 
 
 

Select chunk at a time ???

Post by Umachandar Jayachandra » Sat, 15 Dec 2001 15:24:36


Quote:>>  WHERE ORDERID%7 > 1

    There is one main problem with this. It negates the use of index & you
will always scan the table/index. So performance is not something that you
can expect from this. And gaps in "OrderID" will also be a problem. The
temporary table approach usually works fine.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Select chunk at a time ???

Post by Steve Kas » Sat, 15 Dec 2001 20:01:45


Umachandar,

  A temp table is a terrible idea when selecting a chunk
deep in a huge table, especially when the temp table
is created locally from a remote server.  But the
cursor-based query is better in most any scenario

The %7 is there only to make the table "interesting"
and doesn't change the performance much.
Yes, the clustered index is still used.  And even for
this table of modest size, the query using a cursor is
more efficient than the identity/temp table queries
below: same number of scans, fewer reads.  If the
base table is remote, there are more benefits.

drop table #fetch
select identity(int,1,1) as position, orderid, productid into #fetch
  FROM NORTHWIND..[ORDER DETAILS]
  WHERE ORDERID%7 > 1
  ORDER BY ORDERID

select * from #fetch
where position between 1000 and 1013

and the same query with top 1013 for the temp table.

Leaving out the orderid%7 makes the performance
gain of my query greater.

Steve


> >>  WHERE ORDERID%7 > 1

>     There is one main problem with this. It negates the use of index & you
> will always scan the table/index. So performance is not something that you
> can expect from this. And gaps in "OrderID" will also be a problem. The
> temporary table approach usually works fine.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

Select chunk at a time ???

Post by Umachandar Jayachandra » Mon, 17 Dec 2001 03:41:57


Quote:>> A temp table is a terrible idea when selecting a chunk deep in a huge

table,

    I don't think so. It depends. For the cursor also, you have some tempdb
usage & overhead.

Quote:>> especially when the temp table is created locally from a remote server.

    I have no idea what you are referring to here.

Quote:>> The %7 is there only to make the table "interesting" and doesn't change
the
>> performance much. Yes, the clustered index is still used.

    What do you mean the index is used? SQL Server is just scanning the
entire table using the index. What I meant was search conditions like this
will avoid seeks on the index. In any case, it doesn't matter for this
problem since this is just a bogus criteria.

    For this problem, I will just generate the SELECT statement using TOP
from the client-side or use dynamic SQL & not worry about cursors/temporary
tables. Something like below:

select top 14 o.*
  from Northwind.."Order Details" as o,
       (
        select top 1 * from (
            select top 1000 o1.Orderid, o1.ProductID
              from Northwind.."Order Details" AS o1

             order by o1.OrderID, o1.ProductID
        ) as o2
        order by o2.OrderID desc, o2.ProductID desc
      ) as ot
 where o.OrderID >= ot.OrderID And o.ProductID > ot.ProductID
 order by o.OrderID, o.ProductID

    Bottom-line, I will not unnecessarily use cursors or temporary tables
when dynamic SQL or other SQL constructs provide better performance. For the
above query, you basically pass the key values from the current page
starting with (0,0). The advantage with this approach is many-fold. For one,
you are always querying on the key values. Again all this will work if you
are ordering the result by the key values.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Select chunk at a time ???

Post by Steve Kas » Mon, 17 Dec 2001 04:17:06


Umachandar,

  Here is a query based on your post alongside my sp.  They have
the same query plan and the same io statistics, A clustered index scan
to get the start value, and a clustered index seek to get the rows to
return.  Perhaps I missed something rewriting your query, but I don't
see how the plan can be better, and what the advantage is of having
to do it dynamically.  (Thanks for catching the  nonsense
things I was saying about temp tables - I was thinking of the approach
that produces a temp table with identity values to search on)

set statistics io on
dbcc freeproccache
go



from (
  select top 1000 OrderID, ProductID
  from Northwind..[Order Details]
  order by OrderID, ProductID) OD
  order by OrderID desc, ProductID desc

select top 14 * from Northwind.."Order Details"


order by OrderID, ProductID

go

as
  set nocount on
  DECLARE C CURSOR DYNAMIC SCROLL READ_ONLY FOR
  SELECT OrderID, ProductID FROM Northwind..[Order Details]
  ORDER BY OrderID, ProductID


  OPEN C


  SELECT * FROM Northwind..[Order Details]


  ORDER BY OrderID, ProductID
  SET ROWCOUNT 0
  CLOSE C
  DEALLOCATE C
go

dbcc freeproccache
go
exec sk_fetchmiddle 1000,14

drop procedure sk_fetchmiddle

Steve


> >> A temp table is a terrible idea when selecting a chunk deep in a huge
> table,

>     I don't think so. It depends. For the cursor also, you have some tempdb
> usage & overhead.

> >> especially when the temp table is created locally from a remote server.

>     I have no idea what you are referring to here.

> >> The %7 is there only to make the table "interesting" and doesn't change
> the
> >> performance much. Yes, the clustered index is still used.

>     What do you mean the index is used? SQL Server is just scanning the
> entire table using the index. What I meant was search conditions like this
> will avoid seeks on the index. In any case, it doesn't matter for this
> problem since this is just a bogus criteria.

>     For this problem, I will just generate the SELECT statement using TOP
> from the client-side or use dynamic SQL & not worry about cursors/temporary
> tables. Something like below:

> select top 14 o.*
>   from Northwind.."Order Details" as o,
>        (
>         select top 1 * from (
>             select top 1000 o1.Orderid, o1.ProductID
>               from Northwind.."Order Details" AS o1


>              order by o1.OrderID, o1.ProductID
>         ) as o2
>         order by o2.OrderID desc, o2.ProductID desc
>       ) as ot
>  where o.OrderID >= ot.OrderID And o.ProductID > ot.ProductID
>  order by o.OrderID, o.ProductID

>     Bottom-line, I will not unnecessarily use cursors or temporary tables
> when dynamic SQL or other SQL constructs provide better performance. For the
> above query, you basically pass the key values from the current page
> starting with (0,0). The advantage with this approach is many-fold. For one,
> you are always querying on the key values. Again all this will work if you
> are ordering the result by the key values.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

Select chunk at a time ???

Post by Umachandar Jayachandra » Mon, 17 Dec 2001 05:05:19


    The difference is compactness & performance. Why use a cursor / SP when
I can get it done in one SELECT statement using a set-based approach? The
performance will be more obvious when you try this on a large table with the
cursor approach. I will attribute this mainly to creating the keys required
for the cursor OPEN statement.
    I modified the code that you posted to how I usually test. Apart from
io, I always rely on the actual execution time. I also added
DROPCLEANBUFFERS. I also dumped the results into a temporary table. You can
also put the rows into variables. The idea is to avoid returning the result
set to the client which introduces other things like network overhead,
client display code etc. And why do I have to split the SELECT into two when
I can do the join. This can change IO sometimes.
    So try:

set statistics io on
dbcc freeproccache with no_infomsgs
dbcc dropcleanbuffers with no_infomsgs
go


select top 14 o.*
  into #t
  from Northwind.."Order Details" as o,
       (
        select top 1 * from (
            select top 1000 o1.Orderid, o1.ProductID
              from Northwind.."Order Details" AS o1
             order by o1.OrderID, o1.ProductID
        ) as o2
        order by o2.OrderID desc, o2.ProductID desc
      ) as ot
 where o.OrderID >= ot.OrderID And o.ProductID > ot.ProductID
 order by o.OrderID, o.ProductID
drop table #t

go

as
  set nocount on
  DECLARE C CURSOR DYNAMIC SCROLL READ_ONLY FOR
  SELECT OrderID, ProductID FROM Northwind..[Order Details]
  ORDER BY OrderID, ProductID


  OPEN C


  SELECT * INTO #t FROM Northwind..[Order Details]


  ORDER BY OrderID, ProductID
  SET ROWCOUNT 0
  CLOSE C
  DEALLOCATE C
go
raiserror( '-- SP Call:', 0 , 1 ) with nowait
dbcc freeproccache with no_infomsgs
dbcc dropcleanbuffers with no_infomsgs
go


exec #sk_fetchmiddle 1000,14

    Lastly, if the number of rows to be returned is fixed in each
page/chunk, I can even slap a inline table-valued function in SQL2000 &
parameterize the SELECT for query purposes instaed of using a SP.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Select chunk at a time ???

Post by Steve Kas » Mon, 17 Dec 2001 05:09:32


Umachandar,

  You caught a good point I missed, the i/o doesn't tell the whole
story, which could be bad here for a large table.  I'll be rethinking
some cursors. Oops.

Thanks for all the good points.

Steve


>     The difference is compactness & performance. Why use a cursor / SP when
> I can get it done in one SELECT statement using a set-based approach? The
> performance will be more obvious when you try this on a large table with the
> cursor approach. I will attribute this mainly to creating the keys required
> for the cursor OPEN statement.
>     I modified the code that you posted to how I usually test. Apart from
> io, I always rely on the actual execution time. I also added
> DROPCLEANBUFFERS. I also dumped the results into a temporary table. You can
> also put the rows into variables. The idea is to avoid returning the result
> set to the client which introduces other things like network overhead,
> client display code etc. And why do I have to split the SELECT into two when
> I can do the join. This can change IO sometimes.
>     So try:

> set statistics io on
> dbcc freeproccache with no_infomsgs
> dbcc dropcleanbuffers with no_infomsgs
> go


> select top 14 o.*
>   into #t
>   from Northwind.."Order Details" as o,
>        (
>         select top 1 * from (
>             select top 1000 o1.Orderid, o1.ProductID
>               from Northwind.."Order Details" AS o1
>              order by o1.OrderID, o1.ProductID
>         ) as o2
>         order by o2.OrderID desc, o2.ProductID desc
>       ) as ot
>  where o.OrderID >= ot.OrderID And o.ProductID > ot.ProductID
>  order by o.OrderID, o.ProductID
> drop table #t

> go

> as
>   set nocount on
>   DECLARE C CURSOR DYNAMIC SCROLL READ_ONLY FOR
>   SELECT OrderID, ProductID FROM Northwind..[Order Details]
>   ORDER BY OrderID, ProductID


>   OPEN C


>   SELECT * INTO #t FROM Northwind..[Order Details]


>   ORDER BY OrderID, ProductID
>   SET ROWCOUNT 0
>   CLOSE C
>   DEALLOCATE C
> go
> raiserror( '-- SP Call:', 0 , 1 ) with nowait
> dbcc freeproccache with no_infomsgs
> dbcc dropcleanbuffers with no_infomsgs
> go


> exec #sk_fetchmiddle 1000,14

>     Lastly, if the number of rows to be returned is fixed in each
> page/chunk, I can even slap a inline table-valued function in SQL2000 &
> parameterize the SELECT for query purposes instaed of using a SP.

> --
> Umachandar Jayachandran
> SQL Resources at http://www.umachandar.com/resources.htm
> ( Please reply only to newsgroup. )

 
 
 

1. Determining time of chunk creation

Is there a way (using SMI) to determine the date that a particular chunk
was added?  I've worked on a script to give me a complete breakdown of
each rawspace (from physical disk to dbspace chunks residing) and would
like to add "Date created" also.  Any ideas?

Thanks in advance

John Carlson
Informix DBA
WHSmith USA

2. suscribe

3. move rows from chunk to another chunk

4. FREE technicals tools for Sybase

5. Select chunk of records from table

6. PGSQL SUCKS because it has no GUI admin utilities

7. Select into...and select at the same time

8. Two fold problem please help

9. Select things a "chunk" at a time?

10. Trying to select CURRENT DATE-TIME minus 8 hours in SQL

11. selecting n rows at a time

12. Selecting Date Time fields ?

13. Select previous record based on date time