Solved

make datereader faster

Posted on 2014-01-21
4
246 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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 334 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 166 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 334 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

829 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