How to load 1 milion records from database fast?

Question

Now we have a firebird database with 1.000.000 that must be processed after ALL are loaded in RAM memory. To get all of those we must extract data using (select * first 1000 ...) for 8 hours. What is the solution for this?

Answer 1

Does each of your "select * first 1000" (as you described it) do a full table scan? Look at those queries, and make sure they are using an index.

Answer 2

How long does it take to construct the DTO object that you are creating with each data read?

{ int a = read.GetInt32(0); int b = read.GetInt32(1); mylist.Add(new DTO(a,b)); }

You are creating a million of these objects. If it takes 29 milliseconds to create one DTO object, then that is going to take over 8 hours to complete.