I have a large table and loading the DataGridView is very slow. The table is going to get bigger.
Is there a way to optimize the loading? Is there a good replacement control? (We will purchase one if need be.) Is there an option I haven't thought to ask?
Is there an option I haven't thought to ask?
Yes! Paging + Search Filtering. If you have so many records in your datagridview that it's sluggish odds are your users find it cumbersome anyway. Add a search feature to limit the number of items they're looking, and only show a page at time.
I will say, I have tried a few GridView controls, and settled with DevExpress. It really is pretty fast.
If you want to speed up your own, you may want to try suspending the layout during data updates.
I have found that using a Repeater can work really well.
I have not done any performance testing between a DataGrid and a Repeater. It doesn't have some of the added functionality of a DataGridView, which will reduce the some of the overhead. Because of the absence of some features I assume it would be faster. Plus you get a little more control over the final HTML.
I also agree with Joel that Paging and Search Filter would be a good way to increase your speed.
Have you considered caching your data?
A well-designed caching strategy is probably the single most important performance-related design consideration. ASP.NET caching features include output caching, partial page caching, and the cache API.
Click the link for specific information.
Paging is only half of the answer.
For example, if you have a recordset that is expensive to produce, but then you only render a page at a time, it may still be too slow. On the other hand, if you have a cheap recordset, then retrieving all the records and throwing away all but one page is not a big deal.
Many people write their queries in such a way that only a page at a time is returned from SQL to .net, and only a page is rendered (like a Repeater that Joe mentioned.) That is typically the most cumbersome and fastest solution.