How to quickly load 1 millionth records from a database?

Question

Now we have a firebird database with 1.000.000, which needs to be processed after ALL are loaded into RAM. To get all this data, we must extract the data using (select * first 1000 ...) within 8 hours. What is the solution for this?

Answer 1

Does each of your "select * first 1000" (as you describe it) do a full table scan? Take a look at these queries and make sure they use the index.

Answer 2

It takes Pentium 4 3Ghz at least 8 hours to load data from a table using 1,000,000 rows in C # using firebird db

Everyone assumed that you were using an SQL query to select records from the database. Something like

select * from your_big_table / 

Because it really takes a few seconds. Well, a little more to display it on the screen, but making the actual selection should be lightning fast.

But this link to C # makes me think that you are doing something else. Perhaps you really have an RBAR loop that creates a million objects. I see how this can take a little longer. But even so, eight hours? Where is the time going?

change

My guess was right, and you are creating 1,000,000 objects in a loop. The right advice would be to find another way to do what you do when you have all your objects in memory. Without knowing more about the details, it is difficult to give specific details. But it seems unlikely that this is a user interface - which user is going to view a million objects?

Thus, a general observation should be sufficient: use mass operations to realize mass activity. SQL databases are excellent at handling sets. Use the power of SQL to process millions of rows in a single set, not as separate rows.

If you do not find this answer useful, you need to provide us with more detailed information about the desire that you are trying to achieve.

Answer 3

How long does it take to build the DTO object you create with each reading of the data?

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

You create a million of these objects. If it takes 29 milliseconds to create one DTO, then it will take more than 8 hours to complete.

Answer 4

What processing do you need to do to load them into memory, and not just process them using SQL statements?

There are two methods by which I use this work depending on what I am trying to do.

• Assuming that there is some kind of artificial key (identifier), work in batches, increasing the last processed value of the identifier.

• BCP output data to a text file, transfer updates, then turn BCP on again, remembering to turn off restrictions and indexes before the IN step.

Answer 5

Take a look at this: http://www.firebirdfaq.org/faq13/