Click here to Skip to main content
15,849,197 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi

I have 1 Billion Data Coming from the DB by executing a Large Query.
I need to show it in DataGrid page by page. While executing query it is taking time.Tried Some useful link .but it is for SQL SERVER only. I am using HP Vertica Database . So how can I do this in an optimized way

please suggest.

Ajit
Posted
Comments
Mehdi Gholam 28-Mar-14 3:39am    
It is a bad idea to show 1 billion rows in a grid, and databases have paging constructs so consult the Vertica documentation.
Raul Iloc 28-Mar-14 10:26am    
Did you try one of my solutions (Solution1 or Soultion2)?
W Balboos, GHB 28-Mar-14 13:21pm    
You (or your client) need to gain some perspective on this.
For 1 Billion (10^9) records:
If you look at 100 records/sec it will take you three months, day and night, to go through the data.

Show this in a data grid (or anywhere else) is not really sensible in a real-world situation.

You have to implement pagination.
You could read (and use my C# code and SQL code from) my next article regarding this subject: Advanced ASPX GridView Pagination and Data Entities[^] where I implement pagination at the database level.
 
Share this answer
 
Other solution is to implement pagination at the logic level by using LINQ like in the next example:

C#
public IQueryable<T> LoadGridData<T>(IQueryable<T> dataSource, int pageSize, int pageIndex, out int count)
{
    var query = dataSource;
    count = query.Count();
    //
    if (pageIndex < 1)
        pageIndex = 1;
    //
    var data = query.Skip((pageIndex - 1) * pageSize).Take(this.pageSize);
    return data;
}


So you could build LINQ for getting your data, then use a method like the one above to run the LINQ and to get the data paginated, see the example below:

C#
 IQueryable<Order> ordersQuery = from o in dataContext.Orders
                                            from oi in dataContext.OrderItems
                                            where o.OrderItems.Count > 1 && oi.OrderID == o.ID && oi.WarehouseArticleID == this.ID
                                            orderby o.ID descending
                                            select o;
//Run the query ==>paginated results
int count;
IQueryable<Order> data = LoadGridData<Order>(ordersQuery , pageSize, pageIndex, out count);
 
Share this answer
 
v3
Comments
Always improve your answer, don't post multiple answers. Please delete this and add all these inside the first solution given by you.

Thanks,
Tadit
Raul Iloc 28-Mar-14 5:44am    
I gave two different solutions, so I don't see what are your point!?
No, adding two answers to a question one member is not advised.
You can have different approaches, but you need to add that inside one answer saying...

Approach-1
-----------

Approach-2
-----------

So, please delte this and Improve your first answer.
Raul Iloc 28-Mar-14 7:29am    
OK this is your opinion, and I gave you mine just in my comment above.
Ajit Kumar Nayak 28-Mar-14 7:00am    
Can u please give me some sample
Hi,
It is bad idea to fetch all data at one time b'cos its too large and create complexity, So you have to user server side paging for that purpose, through which you can fetch only that data which is required, it can be implemented with pagination with server side. also you can use jquery for server side paging.
 
Share this answer
 
No, please don't do this. Don't fetch so much of records at once.

You can implement "Custom Pagination" and fetch that much number of records you want to show on a Page.

Example - Custom Paging in ASP.Net GridView using SQL Server Stored Procedure[^].
 
Share this answer
 

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