?
Solved

make datereader faster

Posted on 2014-01-21
4
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1336 total points
ID: 39797926
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 39798674
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
ID: 39799169
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 35

Accepted Solution

by:
Mark Geerlings earned 1336 total points
ID: 39799845
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 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