Solved

make datereader faster

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

737 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