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.


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 (*)?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It depends on which the slow bit is, and what you are doing in your application. You are programming a windows forms application, where does your data come from? Is it from a database or does it go through a web service? For windows forms applications I would recommend using asynchronous methods for any operations that are slow so that the form "appears" to work faster. The real question is ... which is the slow bit? Is it the database read, the arraylist sorting, or the binding from the arraylist to the ListView?

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.

you should not use arraylist when you try to hold data coming from a database.
the problem on arraylists is the extrem overhead cause of boxing and unboxing each 'object' to type object.
if you are using net2, you should use a generic list which is typesafe.

the other bottleneck is using a listview for a huge amount of data, its not designed to display alot of data.

the fastest way in displaying data which comes from a database is :
- retrieve data from your database and fill it in a dataset or datatable
- display the data in the datagrid, its much faster in displaying a lot of data.

greets, vo1d

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
maxy88Author Commented:

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.

under which framework do you develop max?
cause they got different datagrids.
maxy88Author Commented:

.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....:

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, 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.

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?
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.MouseEventArgs e)
     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);

here is a very nice resource for working with the datagrid, you should check it out:
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, 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)
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?

maxy88Author Commented:

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?

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.

But to answer your other question question, yes the winforms datagrid is sortable without implementing special code. To take full advantage of it's performance advantage, you'll need to move to a dataset/custom object collection anyway, so I still wonder if its worth trying the dataset (or custom object collection) first, and then moving to the datagrid if it doesn't improve the performance enough.

You might also want to try doing things like deriving your own class from the listview, and in the constructor setting:
      this.SetStyle(ControlStyles.DoubleBuffer, true);
      this.SetStyle(ControlStyles.UserPaint, true);

And one to try if you aren't already doing. Before looping and filling your ListView in the form's code, do this.SuspendLayout(), and once you have finished looping, do this.ResumeLayout(). If your are individually adding items to the ListView and they are being painted one by one, this should speed things up.
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.collectionbase and implement all methods for adding and indexing your objects.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.