.net Windows form bound with data, too slow

I have a windows application in which a form is bound with the data.

The form loads slowly because of large data. I am also showing paging in form to navigate through the records.

Ho开发者_Python百科w can I increase the performance?

---

Bottom line- your app needs to 'page the data' effectively.

This means, you will need to "lazy load" the data. The UI should only load and display data that it needs to show; therefore load additional data only when needed.

Since you didnt provide much of an information regarding your app and the data that you load.

So, lets assume that your app fetches 10,000,000,01 records.

• Load your form
• If, for instance your grid shows 25 records per page, so use TOP 100 to fetch the top 100 records, and fill in your first page and next four pages.
• Upon each Next or consecutive 'Nexts' you can hit database to fetch next records. Note that, you will require some mechanism(ROW_NUMBER?) to keep track of the records being fetched, row numbers, etc.

This article discusses exactly what you are after and I am referring towards.

---

It's hard to say for certain without knowing more about your application, but the immediate thing that comes to mind is that if your dataset is large, you should be doing pagination on the database side (by constraining the query using row counts) rather than on the application side.

Databinding is a convenience feature of .NET, but it comes with a severe performance overhead. In general it's only acceptable for working with small datasets of less than a few thousand rows bound to a couple of dozen controls at most. If the datasets grow very large, they take their toll very quickly and no amount of tweaking will make the application speedy. The key is always to constrain the amount of memory being juggled by the data binding system at any given time so that it doesn't overload itself with the meta-processing.

---

Here are some recommendations:

• Find out why you need to bring a large set of data. That much data displayed on the screen will not lead to a good user experience. If this is a search result or something, limit your search results, say 100, and let the user know that there is more but they need a more fine grained search criteria.
• Check to make sure that your database query is well optimized and indexed and you are not bringing more data than you need to.
• Assuming you are using a DataGridView, see if taking advantage of VirtualMode helps. Below description is from msdn and there is also a link to an example in there.

  Virtual mode is designed for use with very large stores of data. When the VirtualMode property is true, you create a DataGridView with a set number of rows and columns and then handle the CellValueNeeded event to populate the cells.

If you are using some other control, you can see if that control provides a similar feature. ListView also has VirtualMode.

• Fire up the SQL profiler to see what your applications is needing from the database. You may see some unnecessary calls, opportunities to trim your data needs and lazy load. Also debug and profile your application to see where you spend most of your time.

---

If you are using SQL server, implement paging using the Commaon table Expressions and ROW_NUBER(). This will allow you to get less data from the Sql server and definitely better performance.