Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
I have large volume of data in my database. I have written stored procedure that have some complex query to fetch the data from Database. To achieve the performance, I don't want want to execute my SP twice.

1. In Asp.Net 1.1, I have used DataGrid that was having VirtualItemCount. So this serverd our purpose however DataGrid was having other ViewState issue and no Ajax Support.

2. In Asp.Net 2.0, VirtualItemCount property removed from GridView. However it gave us ObjectDataSource with Cache Support. It requires two method one for fectch the record and other for to get the just total Item count for paging.
At this point, I dont want to execute sp twice just for total count.

3. In Asp.3.5, new DataPager comes to customize the Paging layout but nothing for Custom paging.
I have tried to use the SQLDataSource but it also requires two select method however I dont like this.

Currently I have solved my problem using customized gridview.
http://www.codeproject.com/script/Articles/ViewDownloads.aspx?aid=19058[^]

Is it right solution for Asp.net 4.0?
If not please give me any sample to solve this problem.

My application have three tier architecture.

Thank you,
Anjum Rizwi
Posted
Updated 26-May-11 0:40am
v2
Comments
Prerak Patel 26-May-11 7:13am    
jQuery is not an option?!!
Anjum.Rizwi 26-May-11 7:20am    
I using other feature of gridview like sorting, selecting the row to display the data in detailsView

1 solution

Take a look at Building Custom Paging with LINQ, ListView, DataPager and ObjectDataSource[^]

I think it's nifty.

[EDITED]

1. Large Dataset Performance Demystifie
[^]
Mind you it is 100k, which is 4 times larger than yours.

2. Linq to sql-part 3 querying our database.aspx[^]
I want to stress this paragraph. "When working against SQL 2005, LINQ to SQL will use the ROW_NUMBER() SQL function to perform all of the data paging logic in the database. This ensures that only the 10 rows of data we want in the current page view are returned from the database when we executed..."

Not enough ?
3. LINQPad example[^]

Adios.
 
Share this answer
 
v2
Comments
Anjum.Rizwi 27-May-11 0:28am    
Thanks for replying.
My question is "To achieve the performance, I don't want want to execute my SP twice."
I would suggest again please go through my question.
Wonde Tadesse 27-May-11 7:52am    
Have you gone through the article? I doubt. If so, you may not need to have SP ? It's pure LINQ paging technique. Just read it once again.
Anjum.Rizwi 27-May-11 8:25am    
Just now again I have gone through the article. Still I am not convinced about performance point of view.

My problem is after two much filtration, I get 5000 rows than I want paging. If I use LINQ to SQL than all Data will be fetch in my case 25000 rows than linq will do filtration. This I want to avoid.

Let me know am I right?
Wonde Tadesse 27-May-11 20:38pm    
No. you're not right. All the data won't be fetch.BTW are you familiar with LINQ to SQL ?
Please see my updated answer carefully.

GOOD LUCK.
Anjum.Rizwi 30-May-11 0:28am    
Extremely Thank you for helping to solve my problem.
I am beginner in LINQ to SQL how ever I have 8+ years of exp in VB,ASP,ASP.Net/Sql Server,JavaScript & JQuery.
As of now I am convinced with your approach I will use that LINQ to SQL than I will see the performance.

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