I am facing a problem with a gridview within my application. Couldn't find anything similar here so I start a new post.
The problem is that I have a storage overview displayed in a dataset. To choose between the different categories I implemented a drop down field which works as a select parameter for the access datasource, like this:
Now everytime I try to load a category with more than 8-10 items it takes ages (20-25 seconds) to load the page. Does anyone have a solution for this kinds of problems, or a different way to load/display/change the categories?
Maybe you can make AllowingPaging = True if the filtered data is still large...
I was not able to allow paging for the Datalist.
I tried to reduce the code to find the reason for the long time loading and it seems that the application loads the whole database connection, whenever, the drop down box is changed.
Any suggestions to solve this problem (is it faster to code the database connection within the VB Code?) - a nice workaround would work for me either ;)
Do you lookup any more data? (per line, depending on the data found)
Is that view a complicated one that takes a long time to load? If so, you might want to consider a different strategy for getting the data.
I have very little experience with Access. It could be that opening the file just takes that long. No idea what to do against that. You could try a forum about Access on that.
Superguppie.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
It depends on what is slow. Have you tried running the query from code (or in a query analyzer) to check the time it takes?
I remember a while ago someone had a stored procedure (or was it a function?) that returned a big table. He then did a select to get one record from that. He hadn't realized that the stored procedure would always return the full table. So even getting one record
was always slow.
In that case the solution was to add a parameter to the stored procedure and have it select the right data to return.
For a view, SQL Server should be smart enough to apply any selection as early on as possible. But for Access, I don't know how smart it is. Last time I used Access was over 10 years ago. I then stopped using it because of its lack of smartness. Haven't bothered
looking at it since, but I am guessing it has improved.
Main thing now is to find out which of the operations you do is slow. My earlier suggestion to try the query from code, and then see how long each step takes with the debugger is a good option. Once the slow part has been identified, the focus can be on improving
it.
Superguppie.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
I tried to reduce the code to find the reason for the long time loading and it seems that the application loads the whole database connection, whenever, the drop down box is changed.
you mean, all data.
Henne
I tried to reduce the code to find the reason for the long time loading and it seems that the application loads the whole database connection, whenever, the drop down box is changed.
yes, you can
upgrade to Sql Express. and,
use custom paging available with the Row_Number() function.
and that will fetch only a fixed amount of rows for each page (nothing more)
Henne
Member
41 Points
52 Posts
Datalist loading time
Jun 21, 2010 07:42 AM|LINK
Hi,
I am facing a problem with a gridview within my application. Couldn't find anything similar here so I start a new post.
The problem is that I have a storage overview displayed in a dataset. To choose between the different categories I implemented a drop down field which works as a select parameter for the access datasource, like this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Database.mdb" SelectCommand="SELECT [Code], [PictureLink], [Description], [reusable], [QuantityOnStock], [Price] FROM [View_Kits] Where ([CategoryName] = ?)"> <SelectParameters> <asp:ControlParameter ControlID="DDLCategory" Name="CategoryName" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:AccessDataSource>Now everytime I try to load a category with more than 8-10 items it takes ages (20-25 seconds) to load the page. Does anyone have a solution for this kinds of problems, or a different way to load/display/change the categories?
Thanks and regards,
Henrik
datal list loading time
Venkatesan I...
Member
642 Points
107 Posts
Re: Datalist loading time
Jun 21, 2010 11:01 AM|LINK
hi,
hope below link will help you out.
http://www.designplace.org/tutorials.php?page=1&c_id=26
http://www.highoncoding.com/Articles/169_DropDownList_Inside_GridView__Method_1_.aspx
http://www.xmlfox.com/datagrid_combobox.htm
Please let me know if i am wrong with any of above link.
regards
Venkat
regards,
venkat
http://venkatesan-iyer.blogspot.com
digish
Member
634 Points
721 Posts
Re: Datalist loading time
Jun 21, 2010 11:52 AM|LINK
Try ajax, reduce the viewstate values. and please do not pull unwanted columns.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Datalist loading time
Jun 22, 2010 01:40 AM|LINK
Maybe you can make AllowingPaging = True if the filtered data is still large...
Henne
Member
41 Points
52 Posts
Re: Datalist loading time
Jun 22, 2010 08:01 AM|LINK
I was not able to allow paging for the Datalist.
I tried to reduce the code to find the reason for the long time loading and it seems that the application loads the whole database connection, whenever, the drop down box is changed.
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/Database.mdb" SelectCommand="SELECT [Code], [PictureLink], [Description], [reusable], [QuantityOnStock], [Price] FROM [View_Kits] Where ([CategoryName] = ?)" > <SelectParameters> <asp:ControlParameter ControlID="DDLCategory" Name="CategoryName" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:AccessDataSource>Any suggestions to solve this problem (is it faster to code the database connection within the VB Code?) - a nice workaround would work for me either ;)
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Datalist loading time
Jun 28, 2010 01:24 AM|LINK
Yes, DataList has no pager, but you can use a 3-rd party to deal with the problem, please refer to this article:
http://en.webdiyer.com/
superguppie
All-Star
48225 Points
8679 Posts
Re: Datalist loading time
Jun 28, 2010 03:42 PM|LINK
Is that view a complicated one that takes a long time to load? If so, you might want to consider a different strategy for getting the data.
I have very little experience with Access. It could be that opening the file just takes that long. No idea what to do against that. You could try a forum about Access on that.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
Henne
Member
41 Points
52 Posts
Re: Datalist loading time
Jun 29, 2010 07:59 AM|LINK
What kind of strategy are you thinking about?
I do not think that opening the access database takes such a long time, cause it opens quickly, when you start a query within the vb-code.
superguppie
All-Star
48225 Points
8679 Posts
Re: Datalist loading time
Jun 29, 2010 02:59 PM|LINK
I remember a while ago someone had a stored procedure (or was it a function?) that returned a big table. He then did a select to get one record from that. He hadn't realized that the stored procedure would always return the full table. So even getting one record was always slow.
In that case the solution was to add a parameter to the stored procedure and have it select the right data to return.
For a view, SQL Server should be smart enough to apply any selection as early on as possible. But for Access, I don't know how smart it is. Last time I used Access was over 10 years ago. I then stopped using it because of its lack of smartness. Haven't bothered looking at it since, but I am guessing it has improved.
Main thing now is to find out which of the operations you do is slow. My earlier suggestion to try the query from code, and then see how long each step takes with the debugger is a good option. Once the slow part has been identified, the focus can be on improving it.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
PeteNet
All-Star
81342 Points
11398 Posts
Re: Datalist loading time
Jun 29, 2010 03:29 PM|LINK
you mean, all data.
yes, you can
and that will fetch only a fixed amount of rows for each page (nothing more)
read through: http://www.asp.net/data-access/tutorials/efficiently-paging-through-large-amounts-of-data-vb
another example with the ObjectDataSource and DataList:
Efficient Data Paging with the ASP.NET 2.0 DataList Control and ObjectDataSource
Peter