maxy88
asked on
ListView + ArrayList + Database
Hello EE,
I have a list view control which displays items from an array list. The array list is populated with items from the database. I have implemented the sorting of items in the list view control based on which columns the user clicks on. When the user selects an item from the list view, more data about that item is queried in the database and displayed in a textbox.
Database->ArrayList->ListV
The array list contains the primary key + other fields of the record.
The ListView shows only the fields for each record BUT NOT the primary key.
When the user selects an item in the listview, i get the associated primary key from the arraylist and query the database for more info which is displayed in the textbox.
I use an arraylist as an intermediary storage to hide the primary key used to get more data.
(* I use a list view because selecting an item selects an entire row, and listed items can be sorted by any column in the listview *)
Sorting or populating the list view is slow because first the data needs to be queried, the array list populated and then the items added to the list view. Is there a way to speed this up without losing the functionality in (*)?
Cheers,
Max.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would agree with the datagrid comment, although there is a little work to do to get a whole row to react to a click, it will work and you can use custom paging to reduce the number of rows you display each time. However, that adds complexity.
Dataset vs. custom collection is an interesting one. It really depends on which way you are going with your data model. If you are using a table/row based model, then you are using datasets. If you use a domain logic model with data objects/DTOs/whatever you want to call them there is a large advantage in performance, but a lot more coding to do to get it to work.
Dataset vs. custom collection is an interesting one. It really depends on which way you are going with your data model. If you are using a table/row based model, then you are using datasets. If you use a domain logic model with data objects/DTOs/whatever you want to call them there is a large advantage in performance, but a lot more coding to do to get it to work.
another disadvantage of using an arraylist and a listview to display the data is the huge memoryusage.
you are holding your objects in your arraylist and then initialize for each dataobject a listviewitem and some listviewsubitems, all for displaying the data. but then you have your data stored in both objects, arraylist and listview.thats an extrem overhead for displaying your data.
the datagrid(im not really a fan of it ;)) is only a renderer for your data, which is linked to your datasource.
the data still remains only in your dataset, until you will not implement any controls in the datagrid.
so if you accessing a cell in that grid to retrieve its data, the value comes internally from the datasource instead of the cell itself.
react to a click is not a problem in that grid, and modyfing the data is not a problem too because you only have to make that modifications at one object, your datasource.
the most disadvantage of the datagrid( i only refer to the grid of framework 1.0 and 1.1) is the fact, if you wanna change the design of it. there is some work todo if you wanna customize its look and feel. nothing magic, but some work.
there are a lot of resources which concentrate on this task.
regards, vo1d
you are holding your objects in your arraylist and then initialize for each dataobject a listviewitem and some listviewsubitems, all for displaying the data. but then you have your data stored in both objects, arraylist and listview.thats an extrem overhead for displaying your data.
the datagrid(im not really a fan of it ;)) is only a renderer for your data, which is linked to your datasource.
the data still remains only in your dataset, until you will not implement any controls in the datagrid.
so if you accessing a cell in that grid to retrieve its data, the value comes internally from the datasource instead of the cell itself.
react to a click is not a problem in that grid, and modyfing the data is not a problem too because you only have to make that modifications at one object, your datasource.
the most disadvantage of the datagrid( i only refer to the grid of framework 1.0 and 1.1) is the fact, if you wanna change the design of it. there is some work todo if you wanna customize its look and feel. nothing magic, but some work.
there are a lot of resources which concentrate on this task.
regards, vo1d
ASKER
I agree, there is a lot of overhead and I want to change it, but would a datagrid be able to:
a) hide the primary key of each record from the user, but have it handy somewhere in order to query later on
b) allow user to select an entire row in the list, and when this is done use the primary key of that record to get more data
c) allow user to sort the list based on any of the n columns
If it is possible, are there any suggestions how it can be done? Speed is obviously important so a change must be made.
Max.
under which framework do you develop max?
cause they got different datagrids.
cause they got different datagrids.
ASKER
.NET Framework 1.1
i have to check it out cause its 2years back that i extremely customized the 1.1 datagrid.
a and b should not be a problem.
c i dont know anymore but i will check that out.
can you tell me, about how much datarows we are talking about?
do you have to use framework 1.1?
check this link while i going deeper with point c.
its a speed comparison between different combinations of datareader/datarepeater...
http://www.dotnethero.com/hero/dataset/performance.aspx?nmx=3_8
we are talking about a windows app, correct?
or did you implemented the listview as an applet in a webpage?
a and b should not be a problem.
c i dont know anymore but i will check that out.
can you tell me, about how much datarows we are talking about?
do you have to use framework 1.1?
check this link while i going deeper with point c.
its a speed comparison between different combinations of datareader/datarepeater...
http://www.dotnethero.com/hero/dataset/performance.aspx?nmx=3_8
we are talking about a windows app, correct?
or did you implemented the listview as an applet in a webpage?
Whether we are talking windows forms or web forms, the 1.1 datagrid will be able to hide your primary key column, so that shouldn't be a worry for you. They will both react to row clicks, and will allow you to sort it based on any column. So, it will fulfil your needs.
As I said though; if you are going to use paging to retrieve only, say, 10 records at a time for a web form datagrid, then you need to implement the paging _and_ sorting inside the database statements, and that can get complicated depending on what you are doing.
Andy
As I said though; if you are going to use paging to retrieve only, say, 10 records at a time for a web form datagrid, then you need to implement the paging _and_ sorting inside the database statements, and that can get complicated depending on what you are doing.
Andy
andy, there are differences in the datagrid between the web and the windows implementation.
paging is a good point, but is already implemented in the web one whereas not in the windows forms version. thats why i ask.
and you have to go with different strategies between a web and a windows implementation.
in a windows implementation , the data will come from the dabase to the client.
in a web implementation, the data will come from database to the webserver to the client.
maxy88, here is an example how you can customize your columns so that you dont have to show your primary key.
http://www.akadia.com/services/dotnet_table_style.html
another question i have is, do you wanna show releations for a clicked datarow or do you load some new data in a different object?
paging is a good point, but is already implemented in the web one whereas not in the windows forms version. thats why i ask.
and you have to go with different strategies between a web and a windows implementation.
in a windows implementation , the data will come from the dabase to the client.
in a web implementation, the data will come from database to the webserver to the client.
maxy88, here is an example how you can customize your columns so that you dont have to show your primary key.
http://www.akadia.com/services/dotnet_table_style.html
another question i have is, do you wanna show releations for a clicked datarow or do you load some new data in a different object?
maxy88,
selecting an entire row if the user clicked a cell in the grid can be implemented like this:
private void dataGrid1_MouseUp(object sender, System.Windows.Forms.Mouse
{
System.Drawing.Point pt = new Point(e.X, e.Y);
DataGrid.HitTestInfo hti = dataGrid1.HitTest(pt);
if(hti.Type == DataGrid.HitTestType.Cell)
{
dataGrid1.CurrentCell = new DataGridCell(hti.Row, hti.Column);
dataGrid1.Select(hti.Row);
}
}
here is a very nice resource for working with the datagrid, you should check it out:
http://www.syncfusion.com/FAQ/WindowsForms/Default.aspx#44
selecting an entire row if the user clicked a cell in the grid can be implemented like this:
private void dataGrid1_MouseUp(object sender, System.Windows.Forms.Mouse
{
System.Drawing.Point pt = new Point(e.X, e.Y);
DataGrid.HitTestInfo hti = dataGrid1.HitTest(pt);
if(hti.Type == DataGrid.HitTestType.Cell)
{
dataGrid1.CurrentCell = new DataGridCell(hti.Row, hti.Column);
dataGrid1.Select(hti.Row);
}
}
here is a very nice resource for working with the datagrid, you should check it out:
http://www.syncfusion.com/FAQ/WindowsForms/Default.aspx#44
Absolutely vo1d, I use both 1.1 win and 1.1 web datagrids in all my apps and they are, as you say, completely different controls. The only thing they share is the name. I don't quite understand/agree with your data flow differences for win and web, but I don't think it is a big difference.
Max, before distracting yourself with considering the move to datagrids, first consider the answer to where your bottleneck is. Is your bottleneck coming from:
-the database SELECT statements?
-the parsing of the resultset (is that a datareader) into the arraylist?
-running through the array list and sorting it
-feeding it into the datagrid?
If it is any of the first three points that is slowing you up, then you aren't going to get an advantage from using a different presentation control, unless, in the case of web forms you can page the data (i.e., show the user something like 10 records at a time). In these cases you need to look at using a different object to hold your data.
vo1d has mentioned datasets, and he is right, these are a good RAD way of doing it as it requires very little code. Not only that but you can apply Views to the Table inside a dataset which will sort the data, and bind that to the presentation control directly. It might be that this speeds up your code a lot. You might also find that optimising the presentation part of your code (for instance try turning off screen updating until the list view is filled) helps.
If you are filling your listview using a "do" or "for" loop to do conditional formatting on the rows, then you do need to consider a different control, one which will do this a lot faster. The datagrid is the one, and with some extra effort you can bind TableStyles to the winforms datagrid which will do your formatting for you. In one of my winforms applications, I have datagrids that render 100s of rows with 10s of columns on screen in milliseconds, and that includes doing conditional formatting on the rows and columns. I store the data for these datagrids in datasets, and I can store several hundred in memory simultaneously with no problems. There's going to be a fair bit of code to achieve this though, so only do that once you are sure you have optimised the other parts of the app.
I don't know of fast ways to conditionally format the ListView, but vo1d might?
My main question remains; which is the slow bit of your code? Database SELECT, ArrayList sorts, populating the arraylist, or binding the arraylist to the ListView (if this was a web app you might also have the overhead of transporting the Html to the client, but fortunately it isn't).
Andy
Max, before distracting yourself with considering the move to datagrids, first consider the answer to where your bottleneck is. Is your bottleneck coming from:
-the database SELECT statements?
-the parsing of the resultset (is that a datareader) into the arraylist?
-running through the array list and sorting it
-feeding it into the datagrid?
If it is any of the first three points that is slowing you up, then you aren't going to get an advantage from using a different presentation control, unless, in the case of web forms you can page the data (i.e., show the user something like 10 records at a time). In these cases you need to look at using a different object to hold your data.
vo1d has mentioned datasets, and he is right, these are a good RAD way of doing it as it requires very little code. Not only that but you can apply Views to the Table inside a dataset which will sort the data, and bind that to the presentation control directly. It might be that this speeds up your code a lot. You might also find that optimising the presentation part of your code (for instance try turning off screen updating until the list view is filled) helps.
If you are filling your listview using a "do" or "for" loop to do conditional formatting on the rows, then you do need to consider a different control, one which will do this a lot faster. The datagrid is the one, and with some extra effort you can bind TableStyles to the winforms datagrid which will do your formatting for you. In one of my winforms applications, I have datagrids that render 100s of rows with 10s of columns on screen in milliseconds, and that includes doing conditional formatting on the rows and columns. I store the data for these datagrids in datasets, and I can store several hundred in memory simultaneously with no problems. There's going to be a fair bit of code to achieve this though, so only do that once you are sure you have optimised the other parts of the app.
I don't know of fast ways to conditionally format the ListView, but vo1d might?
My main question remains; which is the slow bit of your code? Database SELECT, ArrayList sorts, populating the arraylist, or binding the arraylist to the ListView (if this was a web app you might also have the overhead of transporting the Html to the client, but fortunately it isn't).
Andy
andy, i totally agree that max has to determine his bottleneck before making changes in the whole design.
the problem in the listview is, that you have to instance alot of objects for only one row.
sorting a huge amount of data is always timecritical, which cant be ontimized much.
the best way for solving that proble is like you said, implement some kind of paging so you can limit the amount of data which has to be sorted and displayed.
we have solved that problem with listview in the same way in our company, optimizing our data and statements and implemented some paging features.
the difference between win and web applications which get data from a database is, that if you run a statement on a database server from a windows app, the result dataset will directly come to the application.
in a web application, your client(webbrowser) will retrieve the datasets from the databaseserver via the webserver(correct me if i'm wrong). so the results have to be transported via the http in plaintext.
the best way would be, if you implent two proxy objects, one for the client and one for the server, which will compress the dataexchange.(only an idea)
vo1d
the problem in the listview is, that you have to instance alot of objects for only one row.
sorting a huge amount of data is always timecritical, which cant be ontimized much.
the best way for solving that proble is like you said, implement some kind of paging so you can limit the amount of data which has to be sorted and displayed.
we have solved that problem with listview in the same way in our company, optimizing our data and statements and implemented some paging features.
the difference between win and web applications which get data from a database is, that if you run a statement on a database server from a windows app, the result dataset will directly come to the application.
in a web application, your client(webbrowser) will retrieve the datasets from the databaseserver via the webserver(correct me if i'm wrong). so the results have to be transported via the http in plaintext.
the best way would be, if you implent two proxy objects, one for the client and one for the server, which will compress the dataexchange.(only an idea)
vo1d
Did you get paging to work on a listview? That's pretty cool. Does the listview not allow you to do similar formatting to the datagrid using formattable columns and TableStyles? I've always gone for the datagrid anyway, just due to the nature of the data I work with.
I kind of guessed at what you meant about the win/web app, though with any real win smart client, you are going to have to implement a service layer anyway to transport the data, so you have that extra bottleneck. My apps have got just what you say, compressed SOAP messaging between the web service and the smart client. They also implement intelligent caching on the client side to reduce client-server-database conversations. For the thin client versions I rely on paging the database results into the datagrid and compressing the html to the client using the web server. I then make use of the ASP.NET server-state mechanisms like session state to cache datasets which improves database performance.
So in your experience are ListViews much, much slower than datagrids in winforms? If so then in what scenario would you use a listview over a datagrid?
A
I kind of guessed at what you meant about the win/web app, though with any real win smart client, you are going to have to implement a service layer anyway to transport the data, so you have that extra bottleneck. My apps have got just what you say, compressed SOAP messaging between the web service and the smart client. They also implement intelligent caching on the client side to reduce client-server-database conversations. For the thin client versions I rely on paging the database results into the datagrid and compressing the html to the client using the web server. I then make use of the ASP.NET server-state mechanisms like session state to cache datasets which improves database performance.
So in your experience are ListViews much, much slower than datagrids in winforms? If so then in what scenario would you use a listview over a datagrid?
A
ASKER
Thanks for the links! I will check them out!
To answer your questions: This is a windows application running on a server computer with a modem to which machines in the field connect and upload their data. Because of the number of machines per dealership the number of datarows (or records) in the datagrid will not be more than 5000 with most occurances having to show about 1000 or so entries. When a row is clicked more data about that machine is shown in a different object on the same form.
I think the bottleneck is in the storing of data into the arraylist and then into the listview item by item which is then displayed on the form. Like vo1d mentioned there is a lot of overhead involved here. This also happens when sorting the listview and array list by the column the user clicks on. Going for a datagrid looks like will speed things up a bit. Is the sorting by column built into the datagrid object itself or does it have to be implemented?
Max.
I don't think the datagrid is going to help massively if the arraylist is the problem. You need to use a different object to store the data in memory. Could you consider using a dataset? As I say, this is sortable by using DataViews on the DataTable inside the Dataset. Given how quick this would be to implement, still using the ListView, could you try that first and then move onto the datagrid implementation afterwards? The datagrid implementation will work nicely with the dataset too.
Andy
Andy
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the advantage of a listview is the possibility of presenting your data in different ways. you can make groups and other things you know from the explorer.
one more thing to the statement of andy, when you are adding alot of items to it. to prevent the listview of paintig each added item, call the the listviews BeginUpdate() method before adding the first item.
after adding all items, call EndUpdate(). the listview starts painting the items than.
the suspendlayout() and resumelayout() are used to suppress multiple layout events while you adjust multiple attributes of the control
the setstyle flags will not bring any speedimprovements.
they are only usefull if you wanna make some/all drawing for yourself of the control.
for this, override the controls wndproc, react on the wm_paint message and do your drawing.
but you will not get the speed as if the control is drawn by the os, which is the default.
like andy said, changing your arraylist sounds good to me as a first try.make your own collection of your object.
derive from system.collections.collect
maybe you could also implement the sorting here too, havent tried it yet. maybe andy has some hint for that;)
another speedimprovement is using the for loop instead of using foreach.
the fastest waywill be, if your are indexing your objects later via a unsafe method and using a pointer to your objects.
one more thing to the statement of andy, when you are adding alot of items to it. to prevent the listview of paintig each added item, call the the listviews BeginUpdate() method before adding the first item.
after adding all items, call EndUpdate(). the listview starts painting the items than.
the suspendlayout() and resumelayout() are used to suppress multiple layout events while you adjust multiple attributes of the control
the setstyle flags will not bring any speedimprovements.
they are only usefull if you wanna make some/all drawing for yourself of the control.
for this, override the controls wndproc, react on the wm_paint message and do your drawing.
but you will not get the speed as if the control is drawn by the os, which is the default.
like andy said, changing your arraylist sounds good to me as a first try.make your own collection of your object.
derive from system.collections.collect
maybe you could also implement the sorting here too, havent tried it yet. maybe andy has some hint for that;)
another speedimprovement is using the for loop instead of using foreach.
the fastest waywill be, if your are indexing your objects later via a unsafe method and using a pointer to your objects.
Sorry Max, and vo1d thanks for clarifying. If you are going to do conditional formatting on the control, then the ControlStyles would help, and the UserPaint settings prevents the OS doing the painting.
If you are going to do your own collection with sorting, its pretty simple if you use IComparer or IComparable on the custom data object.
If you are going to do your own collection with sorting, its pretty simple if you use IComparer or IComparable on the custom data object.
I would also consider implementing a custom data object to represent each row, use a collection to store them, and use IComparer to sort them. You can then directly bind your collection to the ListView. As long as your data object exposes readable properties with the names that your list view expects for each column, this _may_ reduce the overhead of maintaining and manually sorting your ArrayList of objects.
Andy