Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Is it possible to write a VB.NET loop that queries an MS SQL database 1000 rows at a time?

I have an MS SQL database that I need to query.  It has around 10 million rows and I need to be able to create a vb.net loop that will:
1. open the connection to the database,
2. query first/next 1000 rows,
3. close the database

any sample code is greatly appreciated.

Thanks!
0
RichardRiga
Asked:
RichardRiga
  • 4
  • 3
  • 2
  • +3
1 Solution
 
chapmandewCommented:
Yes, just put top(1000) in your queries

select top 10000 * from tablename
0
 
Snarf0001Commented:
If you're using 2005 or above, you can use the row_number function to grab the incremental bits:
with ordered as
(
	select *, ROW_NUMBER() over (order by MyColumn) as Ranking
	from MyTable
)
select *
from ordered
where Ranking between 1 and 1000
--where Ranking between 1001 and 2000
...

Open in new window

0
 
RichardRigaAuthor Commented:
Snarf, it looks like you're ordering the entire table before taking 1000.

here's the thing... i can't use any statement that queries all rows at once.  I have to avoid it due to locking issues.

Chap, I need to be able to do this in a vb loop and grab and process 1000 records at a time.  So... grabbing the first 1000... then grabbing 1001 to 2000, etc.   looping Top 1000 won't work for me, i don't think.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
chapmandewCommented:
You can...you'll just need to keep track of what you've processed. Identity columns would be perfect here
0
 
RichardRigaAuthor Commented:
i'm a tad new to this, can you give me some a sample of psuedocode?
0
 
jogosCommented:
If you don't do it in one action, there is no garantee you'l have covered all the records: data changes, database gets reorganised....
0
 
RichardRigaAuthor Commented:
jogos, i know.  this is a continuous service running so whatever updates/inserts it misses, it will catch eventually
0
 
jogosCommented:
My previous comment was  a comment on the ranking-sollution, when you try to sort it on a non-controled way.  With a unique identification that is possible

Putting chapmendew's things together with important additions
select top (1000) from ...
where id > @lastmaxid  --- here provide the highest already treated
order by id  -- you have a unique value AND indexed on this
0
 
jogosCommented:
<this is a continuous service running so whatever updates/inserts it misses, it will catch eventually>
So your code delete's the records or updates them somehowe that you can filter them out to prevent them for being read multiple times?
The top(1000) pefectly works when you can identify the processed records in a WHERE.
- see the id-example above
- or something like WHERE processedFlag = 0
0
 
Bob LearnedCommented:
You might be able to use one of the overloaded methods for the SqlDataAdapter.Fill method that will allow you to get blocks of data.

1) Run a Select COUNT(*) query to get the row count for the table.

2) Determine block size

3) Determine the For loop index variables

4) Iteratively call the SqlDataAdapter.Fill method to get blocks of data.

http://msdn.microsoft.com/en-us/library/0z5wy74x.aspx
DbDataAdapter.Fill Method (Int32, Int32, DataTable)

"Adds or refreshes rows in a DataTable to match those in the data source starting at the specified record and retrieving up to the specified maximum number of records."
0
 
JackOfPHCommented:

Here

Dim ctr as long = 10000

"SELECT Top 10000 * FROM TableName Where ID Not In (SELECT Top " & ctr & " * FROM TableName orderby ID) orderby ID"  

Note you must change the value of ctr every time you want to increment the retrieve of query.

Example

To retrive the records 20000 to 30000

Just change the value of ctr to 20000.

Regards,

Joseph

Jehova is Great!!!




0
 
JackOfPHCommented:
Check out this post
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24825984.html

From the post above you have three options:
Option 1:
 
select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from employees) x
where RowNum <= 4000
) as y where row_num >= 3000
 
Option 2:
 
select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from employees) x
) as y where row_num between 3000 and 4000
 
Option 3:
 
SELECT TOP 1000 * FROM Employees 
WHERE EmployeeID 
        NOT IN (SELECT TOP 3000 EMployeeID FROM Employees ORDER BY)
ORDER BY FirstName, LastName

Open in new window

0
 
RichardRigaAuthor Commented:
Thank you, everyone, for your time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now