One of the pages in my dynamic data Linq2Entities web application is based on a table with about 100.000 records.
Entering this page causes slow performance as it is loading all the records. The pagesize is 20 so that would give 5000 pages.
How do you insert custom paging with a dynamic data project?
The gridview is using an entitydatasource, but so far all I can find about custom paging is based on an objectdatasource.
My project is completely done, but the records will only become more and more and thus drain down performance more and more.
My entitydatasource is using a queryExtender to do custom filtering, but I don't think a queryExtender can be used on an objectDatasource, right?
Entering this page causes slow performance as it is loading all the records.
Hi Yannink, does this page have any filters that relate to the same talbe or another table wil similar high number of records? To check this use the display attribute on each FK filed to turn off the filter to check you performance.
[Display(AutoGenerateFilter=false)]
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
I only have custom filters and they all have limited options.
The table does have 6 FK's, but none of those tables have a lot of records.
When I delete all the records in the large table except for like a thousand then the page is loaded a LOT faster. I didn't change anything at all in the FK tables.
I tried what you said though, but that doesn't seem to help.
Still try turnign off all your filters as this issues is always related to Filters on the List page and FK Field templates on the Edit or Insert pages.
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
H Yannick just try turning offf ALL filters for the List page and see if the slownes goes away. trust me thos has happend many times, also do you have access toe SQL Profiler, that can pinpoint the source of the issue?
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
I do have 5 extra tab pages in my formview which shows 5 many2many tables in small gridviews.
For example the main table shows all products and the 5 tabs will show the selected product its relation with suppliers ... with orders ... etc.
My main table:
ARTICLE
- ArticleID
- ArticleName
And consider one of these extra tables:
ARTICLE_SUPPLIER
- ID
- ArticleID
- SupplierID
Now the gridview which shows these ARTICLE_SUPPLIER relations has to show the SupplierName, so a join is needed to the SUPPLIER table to fetch the supplier name, but at the moment it loads all the SUPPLIER Fields, not just the name. On top of that it also
loads all the fields of the ARTICLE table, again.
I was thinking this has something to do with this: Article_Optional_DataSource.Include = Article_Optional_Table.ForeignKeyColumnsNames;
I would certanly chekc that out, try turnign all the tabs off and see if performance comes back, if it does the add them back one at a time and see which one gives the issue.
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
if you need Many to Many why are you not using Entity Framework where Many to Many is built in?
I am using entity framework, why would you think I'm not?
Btw;
I can be sure that when the page is loaded not all 100.000 records are loaded, but just the top 15 ( in case the pagesize of the gridview is 15 ), correct?
Yannick86
Member
565 Points
366 Posts
Large gridview load
Dec 19, 2011 12:34 PM|LINK
Dear,
One of the pages in my dynamic data Linq2Entities web application is based on a table with about 100.000 records.
Entering this page causes slow performance as it is loading all the records. The pagesize is 20 so that would give 5000 pages.
How do you insert custom paging with a dynamic data project?
The gridview is using an entitydatasource, but so far all I can find about custom paging is based on an objectdatasource.
My project is completely done, but the records will only become more and more and thus drain down performance more and more.
My entitydatasource is using a queryExtender to do custom filtering, but I don't think a queryExtender can be used on an objectDatasource, right?
I'll need a solution for this and fast.
Anybody know what I can do?
Thanks in advance,
- Yannick
sjnaughton
All-Star
27320 Points
5459 Posts
MVP
Re: Large gridview load
Dec 19, 2011 12:40 PM|LINK
Hi Yannink, does this page have any filters that relate to the same talbe or another table wil similar high number of records? To check this use the display attribute on each FK filed to turn off the filter to check you performance.
[Display(AutoGenerateFilter=false)]
Always seeking an elegant solution.
Yannick86
Member
565 Points
366 Posts
Re: Large gridview load
Dec 19, 2011 01:00 PM|LINK
I only have custom filters and they all have limited options.
The table does have 6 FK's, but none of those tables have a lot of records.
When I delete all the records in the large table except for like a thousand then the page is loaded a LOT faster. I didn't change anything at all in the FK tables.
I tried what you said though, but that doesn't seem to help.
sjnaughton
All-Star
27320 Points
5459 Posts
MVP
Re: Large gridview load
Dec 19, 2011 01:25 PM|LINK
Still try turnign off all your filters as this issues is always related to Filters on the List page and FK Field templates on the Edit or Insert pages.
Always seeking an elegant solution.
Yannick86
Member
565 Points
366 Posts
Re: Large gridview load
Dec 19, 2011 01:30 PM|LINK
I have some many2many relations to this table though, but none of the filters use these associations when the page is loaded.
Does this matter?
sjnaughton
All-Star
27320 Points
5459 Posts
MVP
Re: Large gridview load
Dec 19, 2011 01:45 PM|LINK
H Yannick just try turning offf ALL filters for the List page and see if the slownes goes away. trust me thos has happend many times, also do you have access toe SQL Profiler, that can pinpoint the source of the issue?
Always seeking an elegant solution.
Yannick86
Member
565 Points
366 Posts
Re: Large gridview load
Dec 20, 2011 09:52 AM|LINK
Stephen,
I do have 5 extra tab pages in my formview which shows 5 many2many tables in small gridviews.
For example the main table shows all products and the 5 tabs will show the selected product its relation with suppliers ... with orders ... etc.
My main table:
ARTICLE
- ArticleID
- ArticleName
And consider one of these extra tables:
ARTICLE_SUPPLIER
- ID
- ArticleID
- SupplierID
Now the gridview which shows these ARTICLE_SUPPLIER relations has to show the SupplierName, so a join is needed to the SUPPLIER table to fetch the supplier name, but at the moment it loads all the SUPPLIER Fields, not just the name. On top of that it also loads all the fields of the ARTICLE table, again.
I was thinking this has something to do with this: Article_Optional_DataSource.Include = Article_Optional_Table.ForeignKeyColumnsNames;
Could that be? And what do I do about this?
sjnaughton
All-Star
27320 Points
5459 Posts
MVP
Re: Large gridview load
Dec 20, 2011 10:06 AM|LINK
I would certanly chekc that out, try turnign all the tabs off and see if performance comes back, if it does the add them back one at a time and see which one gives the issue.
Always seeking an elegant solution.
sjnaughton
All-Star
27320 Points
5459 Posts
MVP
Re: Large gridview load
Dec 20, 2011 10:07 AM|LINK
Always seeking an elegant solution.
Yannick86
Member
565 Points
366 Posts
Re: Large gridview load
Dec 20, 2011 10:44 AM|LINK
I am using entity framework, why would you think I'm not?
Btw;
I can be sure that when the page is loaded not all 100.000 records are loaded, but just the top 15 ( in case the pagesize of the gridview is 15 ), correct?