Click here to Skip to main content
15,888,177 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am using 3 layer in my application
UI (WPF) ->BL->DAL
Business Objects is referred from UI,BL & DAL

DB is oracle and using "Oracle.ManagedDataAccess.Client"

My query is returning around 300000 records at a time.
After adding whole result from data reader to collection, returning that collection to BL, then to UI. it take a little long time.

I need to speed up or need to load as batches
by loading 100/1000 records to the grid So that user can avoid waiting long time to see the result.

Which is the best method to achieve it?

Any helpsss.. really appreciated... Please provide some sample code..or please change in the following sample code...

Number of records will be always above 300000 and its for some reports..

Sample code is given below to know how my coding goes....

Sample UI Code
C#
gridEmployee.ItemsSource =new BLEmployee().GetEmployeeTransactions(startDate,endDate);


Sample Business Layer Code

C#
public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
{
    return new DLEmployee().GetEmployeeTransactions(startDate, endDate);
}


Sample Data Access LayerCode
C#
public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
 {
           ObservableCollection<BOEmployeeTransaction> empTransactions = new                   ObservableCollection<BOEmployeeTransaction>();
            string query = "";
            BOEmployeeTransaction transaction = null;
            OracleConnection con = new OracleConnection();
            con.ConnectionString = oracleConString;
            con.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT Column1,Couln2... FROM TABLE1 WHERE TRUNC(A_DATE) BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('02-02-2015','MM-DD-YYYY')"
            cmd.CommandType = CommandType.Text;
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
               {
                  transaction =new BOEmployeeTransaction();
                  transaction.Name=dr["Name"].ToString();
                  .......
                  .......
                  .......
                  empTransactions.Add(BOEmployeeTransaction);
               }
           }
           con.Clone();
           con.Dispose();
           return transactions;
}
Posted
Updated 23-Jul-15 1:48am
v3

Fetching 300'000 rows to an UI at the same time is probably too much.

First thing I'd consider is: Does the user really need all of the rows? Could the amount of rows be restricted?

If for some reason all of the rows are really needed, I would implement paging. For paging, have a look at On Top-n and Pagination Queries[^]
 
Share this answer
 
Hi U need to get all datas from table using following query ?
C#
cmd.CommandText = "SELECT * FROM TABLE1 WHERE TRUNC(A_DATE) BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('02-02-2015','MM-DD-YYYY')"


1.If no need then specify column names. Don't use "*" for retrieving more datas at a time.
2.Try to get batch wise that u mention above.


Regards
Aravidb
 
Share this answer
 
Comments
Sadique KT 23-Jul-15 7:45am    
its sample query... not real one I am using writing only required columns, and its for some reporting purpose..
Aravindba 27-Jul-15 4:44am    
When u asking question ,u have to specify correct one,one of the slow reason is retrieving all data using "*". At first u use "*" ,and in that table got file stream column,then skip file stream column
Sadique KT 30-Jul-15 7:26am    
Thanks for your reply... But I already considered those things, like data in each field and I Specified the required column names only in my real query.. etc.
My issue is due to huge number of rows,... I need to read data(DB to DAL) and send (DAL to UI) as batches.. I need to know which is the best way and how to achieve it....
Aravindba 30-Jul-15 7:42am    
Why u retrieve all datas at a time ? just select 100 ,100 rows.
check this link
https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx
https://www.google.com/#q=sql+OFFSET

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900