Is it a problem if i query again and again to SQL Server 2005 and 2000?

Question

Window app i am constructing is for very low end machines (Celeron with max 128 RAM). From the following two approaches which one is the best (I don't want that application becomes memory hog for low end machines):-

Approach One:-

Query the database Select GUID from Table1 where DateTime <= @givendate which is returning me more than 300 thousands records (but only one field i.e. GUID - 300 thousands GUIDs). Now running a loop to achieve next process of this software based on GUID.

Second Approach:-

Query the database Select Top 1 GUID from Table1 where DateTime <= @givendate with top 1 again and again until all 300 thousands records done. It will return me only one GUID at a time, and I can do my next step of operation.

What do you suggest which approach will use the less Memory Resources?? (Speed / performance is not the issue here).

PS: Database is also on local machine (MSDE or 2005 express version)

Answer 1

If you only have 128 MB of ram I think number 2 would be your best approach......that said can't you do this SET based with a stored procedure perhaps, this way all the processing would happen on the server

Answer 2

If memory use is a concern, I would consider caching the data to disk locally. You can then read the data from the files using a FileStream object.

Your number 2 solution will be really slow, and put a lot of burden on the db server.

Answer 3

I would have a paged enabled Stored Procedure.

I would do it in chunks of 1k rows and test from there up until I get the best performance.

usp_GetGUIDS @from = 1, @to = 1000

Answer 4

I would go with a hybrid approach. I would select maybe 50 records at a time instead of just one. This way, you aren't loading the entire number of records, but you are also drastically reducing the number of calls to the database.

Answer 5

Go with approach 1 and use SQLDataReader to iterate through the data without eating up memory.

Answer 6

This may be a totally inaproprite approach for you, but if you're that worried about performance and your machine is low spec, I'd try the following:

1. Move your SQL server to another machine, as this eats up a lot of resources.
2. Alternativly, if you don't have that many records, store as XML or SQLite, and get rid of the SQL server altogether?