large result sets

14 Sep 2006 5:18 PM

Noble Shore

I need to return all rows from a table with 50K+ records. The computer
that runs this program does not have sufficient resources to run such a
large query, although it runs fine with 5K or 10K results. I know
about "SELECT TOP <N> * from <TBL>" but is there a way to start from a
row index other than 0? I am looking to extract the records in parts,
do the calculations, and then write the result for each part to a fine
before proceeding to the next part.

14 Sep 2006 5:23 PM

Jens

Sure, are you using SQL Server 2005 ? Then you will have the new
functionality of ROW_NUMBER(ing). Otherwise you will have to implement
something like server paging. Look at google for server paging in SQL
Server, you will find tons of hits for that.

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

14 Sep 2006 5:26 PM

Aaron Bertrand [SQL Server MVP]

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

Show quote

"Noble Shore" <noblesh***@gmail.com> wrote in message
news:1158254281.489301.28870@e3g2000cwe.googlegroups.com...
>I need to return all rows from a table with 50K+ records. The computer
> that runs this program does not have sufficient resources to run such a
> large query, although it runs fine with 5K or 10K results. I know
> about "SELECT TOP <N> * from <TBL>" but is there a way to start from a
> row index other than 0? I am looking to extract the records in parts,
> do the calculations, and then write the result for each part to a fine
> before proceeding to the next part.
>

14 Sep 2006 5:33 PM

Arnie Rowland

First, 50K+ is a NOT a large resultset -even if each row was 8K!

Time for more memory on the server...

SELECT TOP < X >
FROM   MyTable
WHERE PKColumn NOT IN (SELECT TOP < N > PKColumn
                       FROM MyTable
                       ORDER BY {SomeColumn}
                      )
ORDER BY {SomeColumn}

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Noble Shore" <noblesh***@gmail.com> wrote in message news:1158254281.489301.28870@e3g2000cwe.googlegroups.com...
>I need to return all rows from a table with 50K+ records. The computer
> that runs this program does not have sufficient resources to run such a
> large query, although it runs fine with 5K or 10K results. I know
> about "SELECT TOP <N> * from <TBL>" but is there a way to start from a
> row index other than 0? I am looking to extract the records in parts,
> do the calculations, and then write the result for each part to a fine
> before proceeding to the next part.
>

14 Sep 2006 5:37 PM

Aaron Bertrand [SQL Server MVP]

>> Time for more memory on the server...

Granted, the OP did say "the computer that runs this program"... it may be
the case that the program is not running on the database server, or a server
at all. It could be some poor sales schmuck's laptop from 1994.

14 Sep 2006 5:56 PM

Arnie Rowland

Good point.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:ucBjURC2GHA.4476@TK2MSFTNGP02.phx.gbl...
>>> Time for more memory on the server...
>
> Granted, the OP did say "the computer that runs this program"... it may be
> the case that the program is not running on the database server, or a
> server at all. It could be some poor sales schmuck's laptop from 1994.
>