fastest way of storing Data?

eNarc
eNarc used Ask the Experts™
on
Hi I'm looking for a fast way of storing 2 million entrys.

I've tried ADOConnection, though that takes forever.

INIMem, was lil faster though slowed down as more files where being added.


any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Where are you storing them?
Local or network database?

How large are the records?

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What database are you using?

mlmcc

Author

Commented:
its local and I've used ado and mysql and ini though I need something better and faster.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Where are the entries?

mlmcc

Author

Commented:
what are you talking about?

got any ideas on what the best and fastest way of adding data into a database?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are the entries in a database, Excel file, text file, keying them in one at a time?

mlmcc

Author

Commented:
string want to put them in some kind of database.
Commented:
Create your own database, by using binary file, take look in this example
http://delphi.about.com/od/fileio/a/fileof_delphi.htm
Top Expert 2014
Commented:
look at kbmMemtable
http://www.components4programmers.com/products/kbmmemtable/

look at a RAMdisk
Ed CovneyRetired

Commented:
eNarc:

Tell me about your 2M entries.
Are they fixed length? Is the target table keyed? Are field layouts consistent? If anything is being sorted, it may be a lost cause.

One thing you might try is to place one record at a time into a 1-record temp table, then append it to the object table. (Old paradox trick I used occasionally)

I can create a text file with the first 2,000,000 primes separated with CRLFs in about 1 second.  Opening that text file up in notepad is another matter. (that's a 18,795KB txt file)

Ed
Geert GOracle dba
Top Expert 2009

Commented:
fastest ?

depends on:
the database server:
type of database ? > oracle enterprise 11.2 ? > compressed tablespace ?
how many cpu do you have on server ? 64 (= 8 8way)
how much memory do you have on the server ? assigned to the db ?
are the disks mirrored ? with continuous access ?
how many spindles ? > the more the faster for small recordlength inserts ...

network between client and server
broadband ? load balanced ? fiber ?

client:
from how many computers are you sending the inserts ?
just the one ?
or 10 pc's each sending 200000 records ?

Speed and time for a process are in direct relationship with your hardware and what your software does with it,
and has to accomplish.  Einstein would say it's all relative ... :)

Geert GOracle dba
Top Expert 2009
Commented:
i didn't even mention tuning of the insert statement

you didn't mention how long your string is:
you may think not important, a small calculation
100 characters length , 200 characters length
100 byte, 200 byte > * 2 millions
= sending 100000000/1024/1024 = 95Mb for 100 byte length record
> 190 Mb for 200 byte length record

the number of records and the length also is important.


this is one of the worst approaches, that not even the best hardware will solve

this is some pseudo language sample :
 
table rec: 
  A number; -- for storing autoinc number
  colX number; -- X value 
  colY number; -- Y value 
  colZ number; -- Z value
end table rec

insert trigger on rec: 
  var i, n : integer;
  if not assigned(A) 
  begin
    n := 0;
    cursor for i = 0 to recordcount-1 in table do
      if table.A > n then n := table.A
    end cursor
    inserted.A := n;
  end;
end;
  
insert rec (colX, colY, colZ) values (1, 2, 3);

Open in new window


with this sample a insert into a empty table would take .001 ms
for the 2M record it could take 2seconds (depends on hardware)

the speed depends on every piece of code of the software and of the hardware
it is always the bottleneck in the whole process which determines the end speed

putting everything on the same computer only takes out the network part, but not always ... :)

we use devart.com vcl data objects to insert data in the oracle databases
so far, very satisified

http://www.devart.com/odac/performance.html
Top Expert 2014

Commented:
@eNarc

Please return to this question.

Author

Commented:
though I'm going to try that kbm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial