CODEWITHSUNDEEP
c#oracle-databasebulk
TiFang
TiFang

Reputation: 13

Bulk Collect From Oracle and fill to a DataTable in C#

In my project I need to get over 10,000,000 records from Oracle database, for improving efficiency. I write a SP and use BULK COLLECT, BULK COLLECT is fast for selecting, isn't it? The procedure is below:

create or replace type type_bulk as TABLE OF number(10);
/
create or replace PROCEDURE getbulk(returns OUT type_bulk) 
is
BEGIN
SELECT origseq BULK COLLECT
   INTO returns
   FROM Indicationinfo;
end;
/

Then I can execute SP like below in PL/SQL:

declare
    origbulk type_bulk;
begin
    getbulk(returns => origbulk);
    dbms_output.put_line(origbulk.count);
end;

It works fine.

origbulk is a table(or array). So the question is:

1.How to exec SP like above in C#; 2.How to fetch origbulk to C# app;

I try it like below(I use OleDb driver, and Provider is"OraOLEDB.Oracle.1")

 string commText = 
"DECLARE ? type_bulk;\n" + 
"BEGIN\n" + 
"getbulk(returns => ?);\n" + 
"END;"; //here is right?
 myAccessCommand = new OleDbCommand(commText, myAccessConn);
 myAccessCommand.CommandType=CommandType.Text;
 OleDbParameter pout = new OleDbParameter("origseqbulk", ???what type, no OleDbType.Array, 10);

Any advice? Thanks for your help.

Upvotes: 1

Views: 2854

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

Re: advice - reading 10 million rows of data into memory is not a way to be "more efficient". This will be very slow, both because the database will take a while to return 10m rows, and because it will take another long time (and lots of memory) to cram these values into a control, if that's what you're doing. To give you an idea of what I mean, I just read 100 thousand rows from one of our bigger tables, and found that it took about 26 seconds. Nothing complicated, just

SELECT A_COLUMN FROM MY_TABLE WHERE ROWNUM <= 100000

OK, that's one percent of your 10 million rows. Now, I'll never claim that our database is highly performant, but if it's representative of your situation and if the access time scales linearly it would take something on the order of 40 minutes or so to read in your 10 million rows. I suppose that if this is a big batch process maybe that would be OK. If, on the other hand, you've got a user sitting there waiting to see some data displayed they'll probably give up before the data ever shows up on their screen.

Share and enjoy.

Upvotes: 1

Related Questions