Home » SQL & PL/SQL » SQL & PL/SQL » Stored procs..
Stored procs.. [message #256815] Mon, 06 August 2007 15:06 Go to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
I need to be able to insert millions of records into a database really fast.. I was told that if I use a stored proc and pass it arrays and do my insert into the table from that stored proc this would be the fastest way to do it.. I guess my question is this really faster? What would be the best way to insert millions of records fast into a table? I will be importing my data from a flat file which has a complex format of headers and trailers and many detail lines.. I am want to write my reader for this in C# and then call stored procs..
Re: Stored procs.. [message #256819 is a reply to message #256815] Mon, 06 August 2007 15:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A flat file? SQL*Loader would be the fastest, as far as I can tell. Or, you might consider use of external table if your database version supports them.
Re: Stored procs.. [message #256866 is a reply to message #256815] Mon, 06 August 2007 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External table is surely the fastest way to do it, above all if you have special conditions.

Regards
Michel
Re: Stored procs.. [message #257054 is a reply to message #256866] Tue, 07 August 2007 08:30 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
Well the way I understand external tables to work is all the data in the file must be put into the same table and of the same type? Here is the problem with that for me.. Lets say I have two tables cat and dog each describe stuff about the dog or cat.. In the file I will get in I can get in cat and dogs
Example of a file:
CatnameCathomeCateyecolor
DognameDogBirthday

So all cat information would be on line 1 and all dog information would be on line 2.. But both will have the data formatted different and they might not have the same fields...
So I cannot map a text file to one logical database table.. Also when I get in the file I will have optional data lines.. Example would be if dog and cat both have a home then I would look for a line that would give me home information as the 3rd line.. So I was trying to find a fast way to import this data into the database..
Re: Stored procs.. [message #257073 is a reply to message #257054] Tue, 07 August 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a real example and we'll see what we can do.
Your explainations are too general.

Regards
Michel
Re: Stored procs.. [message #257104 is a reply to message #256815] Tue, 07 August 2007 10:32 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Based on what you've said, it sounds like SQL Loader could work. Based on a field in the flat file, you can choose which table to insert the row.
Re: Stored procs.. [message #257194 is a reply to message #257104] Tue, 07 August 2007 13:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Off-topic, but nevertheless: old people in my fatherland say: "Never mix apples and pears". Meaning that two different things put together usually bring problems.

So, why do you mix dogs and cats? All you can get is a problem (when dogs start chasing cats through lines of the same file).
Re: Stored procs.. [message #257241 is a reply to message #257194] Tue, 07 August 2007 19:03 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
Ok let me see if i can give you a file example..
EX)
H51 9087thisisahearderforfile
F608976$800thisisaheaderforaaccount
F308976$400thisisaheaderforsubaaccount
F318976$400thisisaheaderforsubaaccount
F618976$800totalforaaccount
H52 9087trailerrecord

We don't normally use the H## records, but sometimes i need too.. The H records also would say that this is the end of this bit of data and you could have different data on the line after the trailer.. As you can tell from my example The file changes on the first 3 letters... However this is not always true...
EX) 2
H51 9087thisisahearderforfile
F708976$800thisisaheaderforaaccount
line1ofaccountinfo
line2ofaccountinfo
line3ofaccountinfo
line4ofaccountinfo
line5ofaccountinfo
line6ofaccountinfo
F718976$800totalforaaccount
H52 9087trailerrecord

In this example line4-6 are not always in the file or not always needed.. But they can never put in line5 without 4.. they will always be in the same order line wise 1 2 3 4 5.. we know how to switch now because of the header information and then we look for the lines if the line count ends we stop and move to the next record..
so it has be able to run these two type of records and also be able to run them if they are in the same file.. We know when they change now by the headers the H##...
So i think that might give you a better idea of how i need to import.. right now we use lookup table and read it line by line.. i was wondering if there was a better way.. We also have to check for double records.. the mainframes screw up and send the same record two times(but i can do that from the tables)..
We don't mix the records like this the mutual funds markets do.. They are running older mainframes and they love to pad records together like this into one file.. So if you want to buy stocks, bonds, etc... all in one Fund they wrap like this or if you are the same client and you want to buy a lot of funds.. plus our system is really old and the system did not always work like this..
In my simple example Cat and dogs are both animals.. It would be like sending in a list of all animals people own in one file..
I am not a expert in oracle... I used to do electronics.. So i guess i could not see how i could use the loader or external tables(without it being a real mess).. I am looking for speed..
thanks for all the help..
Re: Stored procs.. [message #257287 is a reply to message #257241] Wed, 08 August 2007 00:40 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a practical example that we can work with (including create table statement). If you want convert it with your cat and dog analogy.

Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Regards
Michel
Previous Topic: DEFAULT SETTINGS OF SQL
Next Topic: Sequence problem while using multiple union in INSERT - SELECT query.
Goto Forum:
  


Current Time: Sat Dec 10 03:21:56 CST 2016

Total time taken to generate the page: 0.27066 seconds