Solved

make datereader faster

Posted on 2014-01-21
4
234 Views
Last Modified: 2014-03-24
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
Comment
Question by:Anthony Matovu
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 334 total points
Comment Utility
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
Comment Utility
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
 
LVL 1

Author Comment

by:Anthony Matovu
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 334 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now