• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

make datereader faster

I am reading a database from oracle of over 500 million rows. I want return about 300,000 rows and I want later to transfer then to another database. I am reading the data from off a served through a network.

Challenge is that it takes for ever to execute.

Help, I what can I do to make it run faster

Thank you

Anthony


cmd.CommandText = "select served_msisdn_nr as msisdn, min(FIRST_ACTIVE_DT) as connection_date," _
                              & " snapshot_dt as sdate, min(service_class) as tarrif_plan, min(region) as region, min(district) as district, " _
                              & " sum(case when usage_type_nm in ('GPRS','DATA','WiMAX','WiFi','WiMaX') then 1 else 0 end) as used_data, " _
                              & " sum(case when usage_type_nm in ('MTN 1-4-1 SMS Bundles','MTN 1-4-1 Voice Minutes') then 1 else 0 end) as used_141," _
                              & " sum(case when usage_type_nm in ('SMS') then 1 else 0 end) as used_sms, " _
                              & " sum(case when usage_type_nm in ('CRBT') then 1 else 0 end) as used_crbt, " _
                              & " sum(case when usage_type_nm in ('VOICECHART') then 1 else 0 end) as used_voicechart, " _
                              & " sum(case when usage_type_nm in ('MUSIC ON DEMAND') then 1 else 0 end) as used_mod, " _
                              & " sum(case when usage_type_nm in ('MTN PLAY') then 1 else 0 end) as used_mtnplay, " _
                              & " sum(case when usage_type_nm in ('MMS') then 1 else 0 end) as used_mms, " _
                              & " sum(case when usage_type_nm in ('VOICE SMS') then 1 else 0 end) as used_voicesms, " _
                              & " sum(case when usage_type_nm in ('ROAMING') then 1 else 0 end) as is_roaming," _
                              & " sum(case when usage_type_nm in ('PHONE BOOK BACKUP') then 1 else 0 end) as used_pbb " _
                              & " from bib_adhoc.vw_marketing_datamart_daily where FIRST_ACTIVE_DT >= to_date('20130801','yyyymmdd')" _
                              & " and FIRST_ACTIVE_DT < to_date('20130901','yyyymmdd') and tenure in (0,1)" _
                              & " group by served_msisdn_nr, snapshot_dt"
            cmd.CommandType = CommandType.Text
            Dim dr As OracleDataReader = cmd.ExecuteReader()
0
Anthony Matovu
Asked:
Anthony Matovu
  • 2
3 Solutions
 
Mark GeerlingsDatabase AdministratorCommented:
The biggest impact on your query performance is from this object in your "from" clause: bib_adhoc.vw_marketing_datamart_daily.  Is that a table or a view?  You need to determine that first.

Second, is the FIRST_ACTIVE_DT column indexed, and is that the first column in the index?  If not, this kind of query from a large table can take a *LONG* time!
0
 
slightwv (䄆 Netminder) Commented:
Can you post more about your code and what it is doing after the ExecuteReader?

You might look at using Oracle's XML functions to create a large XML document as a CLOB and return that to your .Net code.  Then use LINQ to parse and process it.

It will still be slow.  I mean bringing back a 300,000 row result set and expecting it to remain resident on the IIS server is likely a bit much to ask.

Can you paginate the data?  I use virtual page sizes with .Net datagrids (I haven't rewritten for griviews yet) and only bring back one page at a time from the database server.
0
 
Anthony MatovuBusiness Analyst, MTN UgandaAuthor Commented:
bib_adhoc.vw_marketing_datamart_daily is a view.  Is that a table or a view?  You need to determine that first.

FIRST_ACTIVE_DT is not indexed.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Views have advantages and disdvantages compared to selecting data directly from base tables.  The advantages:
  1. they can make writing queries easier because complex data relationships can be managed for you;
  2. obsure or cryptic column names can be expended to more-meaningful names;
  3. security can be made easier.  
The disadvantage: there can be (but isn't always) a significant performance penalty, especially if the view selects more columns and/or from more base tables than you actually need data from for your particular query.

Then, if you select data based on a column that is not indexed, you force the data base to do a "full-table scan", that is: retreive from disk and read every record in the table to see if it meets the criteria for your query, or not.  If there is an indexed column available, Oracle can quickly read the appropriate part of the index to determine which rows (actually which blocks) of the table actually need to be retrieved, and Oracle can then often avoid reading the entire table.  If your query however forces Oracle to read an entire large table (and then if that is via a view that may also force other data to be fetched for each row retrieved form the large table) you can wait a long time to get the results.

For fastest results from Oracle:
1. select from base tables instead of views
2. provide values in your "where" clause that match existing indexes on the base table(s).
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now