Querry one record at a time?

[megaxoom]

Hello everyone

I have to design a program that manages a huge database (million and million of records). The database is Microsoft SQL Server or Oracle. I don't think it is appropriate to use a SELECT state to select all the records. My question is that can we use CSharp (and stored procedure if necessary) to point the cursor to a particular record in the database, read that record, and then move to the next record. If there is, please provide some information or examples. Thanks a million for any help.

[exterminator]

If you have to select a 1000 records from a table just think whats better among these two:

1. Hitting the database a 1000 times thru your code (be it a stored proc or a select command)
2. Hitting the database just once, getting the records (into a dataset if using asp.net) and then using it.

I guess you wont disagree with the selection of the 2nd option. Now there could be better ways to get those many records in just one hit to the db. You could use stored procedures. You could use views. Views could prove to be more efficient.

Analyze your requirements well and identify the issues.
All the best...Cheers
Exterminator

[Andy Tacker]

I agree with Exterminator.
Anyhow as he mentioned, SQLDataSet is the best way to solve your problem.

Exterminator, SQLDataSet works the same in Windows forms too :)

[exterminator]

Yes. Right. It completely got off my mind. Since this belongs to ADO.net it would work with .Net in general. Thanks Andy.

Cheers,
Exterminator.

[megaxoom]

Thank for quick replys. But I am still not sure what to use to quickly retrieve records. Here is the description of my program:

Write a Window program that will get a set of records from an INDEXED database. The set of records could be very huge (couple of hundred thousands, maybe up to million). Then examine each record, check the data and do some calculation based on the data of the record, finnally update the calculated data into another table. Please note that the whole record's information will not be displayed to the user, except some small information will be displayed so that the user will know the program is running or know where the program stops if it does happen.

[exterminator]

That is what was suggested to you. Use datasets. You could read about them from any standard ADO.NET tutorial/book. They are in a way offline databases. I mean you get the records from the database (you could bind them to tables and the you could do whatever you wish to do and that too without hitting the database!!!) And finally when you are done you could update the info back to the database in one go and also could insert info into different tables as you stated.

Try it out. Its fun, easy, powerful and quick too !!!

[torrud]

Be careful with a dataset and a large database. Otherwise maybe you will run out of memory. For working with a very huge amount of datarows you should not use a dataset. Try the SQLDataReader and SQLDataWriter class. These classes are designed for a straight forward data fetching and writing. Here is a little example from the MSDN help:
public void ReadMyData(string myConnString) {
string mySelectQuery = "SELECT OrderID, CustomerID FROM Orders";
SqlConnection myConnection = new SqlConnection(myConnString);
SqlCommand myCommand = new SqlCommand(mySelectQuery,myConnection);
myConnection.Open();
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
// Always call Read before accessing data.
while (myReader.Read()) {
Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
}
// always call Close when done reading.
myReader.Close();
// Close the connection when done with it.
myConnection.Close();
}


Inside the while-loop you have access to each datarow. Have a look at it. I think it is more practical for your kind of problem.

[klintan]

I agree with Torrud, I think the other guys are wrong.

If you need to deal with millions of rows, there are several options:

1. You may need to show pages of rows to the user, then you need to implement paging, just like you asked about in this thread: http://www.codeguru.com/forum/showthread.php?t=342185&highlight=paging
2. If you want to process the records one by one, a DataReader is very efficient, like Torrud's example (and from your description this is what you need)
3. If you need to process many records very efficiently, I don't think c#/.Net is the best option, DTS is better or c++ could be used (I think there are some native interfaces to implement batch/bulk selects and updates).

I have inserted some 30-40000 rows into a dataset and it works just fine (putting it back to a db is slow though), but for potentially millions of records I would do it differently.

[megaxoom]

Thank you very much for all your help. Now I know what to do. :)

[sudheeratwork]

Can you post how you are planning on implementing this scenario...

Just Curious :D ...