C# How to get all data in Oracle database

Question

I have 21 tables in Oracle 11g, and I need to display them using JOIN clauses. But the query is too slow and throws an System.OutOfMemoryException

My current code:

   try
        {
            //string ConString;
            using (OracleConnection con = new OracleConnection(ConString))
            {
                //string query = "SELECT PAX.CREATION_DATE,PAX.PNR,PAX.PAX_NMBR,PAX.UPDATE_LEVEL_MOD,PAX.PAX_NAME,PAX.CHANGE_OR_CANCEL_IND,PAX_SEATS.LEG_NMBR,PAX_SEATS.INSTANCE_NMBR,pax_seats.ssr_cd,pax_seats.carrier_cd,pax_seats.seat_nmbr,pax_seats.previous_status_codes FROM PAX INNER JOIN PAX_SEATS ON PAX.PNR = PAX_SEATS.PNR";
                string query1 = "SELECT PNR FROM HISTORY_LEGS";
                string query2 = "SELECT PAX.CREATION_DATE,PAX.PNR,PAX.PAX_NMBR,PAX.UPDATE_LEVEL_MOD,PAX.PAX_NAME,PAX.CHANGE_OR_CANCEL_IND,PAX_SEATS.LEG_NMBR,PAX_SEATS.INSTANCE_NMBR,pax_seats.ssr_cd,pax_seats.carrier_cd,pax_seats.seat_nmbr,pax_seats.previous_status_codes,PAX_SSRS.SSR_NMBR,PAX_TKT.TKTNMBR,PAX_TKT.ISSUING_AIRLINE, PAX_TKT.ISSUING_OFFC_NMBR, PAX_TKT.ISSUING_COUNTER_CD, PAX_TKT.ISSUING_AGNT_NMBR, PAX_TKT.TKT_IND, PAX_TKT.TKT_STATUS, PAX_TKT.TARIFICATION_IND,PAX_TKT.S_IND,PAX_TKT.ISSUANCE_DATE FROM PAX INNER JOIN PAX_SEATS ON PAX.PNR = PAX_SEATS.PNR RIGHT JOIN PAX_SSRS ON PAX.PNR = PAX_SSRS.PNR RIGHT JOIN PAX_TKT ON PAX.PNR = PAX_TKT.PNR";
                //string query4 = "SELECT * FROM PAX";
                //string query3 = "SELECT PAX.PAX_NMBR,PAX.PAX_NAME,RES_LEGS.ARNK_IND,RES_LEGS.CARRIER_CD1,RES_LEGS.CARRIER_CD2,RES_LEGS.FLT_NMBR,RES_LEGS.CLASS_CD,RES_LEGS.DAY_OF_WEEK,RES_LEGS.FLT_DATE,RES_LEGS.LEG_ORIGIN_CD,RES_LEGS.LEG_DES_CD,RES_LEGS.CURRENT_STATUS_CD,RES_LEGS.NUMBER_IN_PARTY,RES_LEGS.DP_TIME,RES_LEGS.AR_TIME,RES_LEGS.DATE_CHANGE_IND,RES_LEGS.FLT_IRREGULARITY_IND,RES_LEGS.LEG_TXT,RES_LEGS.PREVIOUS_STATUS_CODES,RES_LEGS.MESSAGE FROM RES_LEGS INNER JOIN PAX ON RES_LEGS.PNR = PAX.PNR";
                OracleCommand cmd = new OracleCommand(query2, con);
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                //dataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.EnableResizing;
                //dataGridView1.RowHeadersVisible = false;
                if (ds.Tables.Count > 0)
                {
                    dataGridView1.DataSource = ds.Tables[0].DefaultView;
                }

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

Questions:

How to get all data?
How to prevent System.OutofMemory?
How to speed up query execution performance?

do you have better idea? i want explore all data at once. Should i use paging. Example: 10,000 rows at one 1 page — Sambuu, Jun 21 '17 at 02:00
Export them to what? A file? You don't need to load all data into memory at once to do that. — mason, Jun 21 '17 at 02:55

score 0 · Answer 1 · answered Jun 21 '17 at 01:52

0

You can look at indexes on your key fields to speed up the query a bit perhaps, but if you are getting a lot of rows back you'll be hitting some memory caps somewhere.

If you can, I would suggest implementing some paging: Paging with Oracle.

This will let you bring back smaller chunks of your data making it quicker and less likely to hit any memory limits.

answered Jun 21 '17 at 01:52

LordBaconPants

1,215
1
15
19

Hello, i tried your suggestion "Paging with Oracle" and still gives me this error: System.OutofMemory – Sambuu Jul 04 '17 at 06:33

C# How to get all data in Oracle database

1 Answers1