0

I'm trying to build a product catalog application in ASP.NET and C# that will allow a user to select product attributes from a series of drop-down menus, with a list of relevant products appearing in a gridview.

On page load, the options for each of the drop-downs are queried from the database, as well as the entire product catalog for the gridview. Currently this catalog stands at over 6000 items, but we're looking at perhaps five or six times that when the application goes live.

The query that pulls this catalog runs in less than a second when executed in SQL Server Management Studio, but takes upwards of ten seconds to render on the web page. We've refined the query as much as we know how: pulling only the columns that will show in our gridview (as opposed to saying select * from ...) and adding the with (nolock) command to the query to pull data without waiting for updates, but it's still too slow.

I've looked into SqlCacheDependency, but all the directions I can find assume I'm using a SqlDataSource object. I can't do this because every time the user makes a selection from the menu, a new query is constructed and sent to the database to refine the list of displayed products.

I'm out of my depth here, so I'm hoping someone can offer some insight. Please let me know if you need further information, and I'll update as I can.

EDIT: FYI, paging is not an option here. The people I'm building this for are standing firm on that point. The best I can do is wrap the gridview in a div with overflow: auto set in the CSS.

The tables I'm dealing with aren't going to update more than once every few months, if that; is there any way to cache this information client-side and work with it that way?

bmurrell30
  • 565
  • 8
  • 23
  • 1
    You should only ever load as many rows as you can display - look into **paging** - load the **top 50** or so rows - **NOT** all 6000 !! – marc_s Feb 23 '15 at 16:00
  • Let's say that no filter criteria is entered. Are you trying to display all 6,000 products at one? Why? The user doesn't need to view 6,000 products at once. Instead, allow the user to page through the data, and only retrieve the data necessary from the DB to display one page's worth of products. – mason Feb 23 '15 at 16:00
  • 1
    @bmurrell30: Paging is not an option? Why? – Dave Mason Feb 23 '15 at 16:05
  • 1
    Paint 6.000 rows can't never be fast. You should think another way to do it (scroll on-demand, filtering... etc). And paging should be a option because.. i have never met a user that can work with 6000 rows over a web page... – Jaime García Pérez Feb 23 '15 at 16:06
  • The problem is not over Sql Server,query, table, etc. You problem it's that you want to: bring 6.000 rows throught the net (sql-Server to your IIS) then paint it to a web-Page. Too much amount of data to handle and for rendering. That isn't a good practice. If you want to view 6.000 rows without paging or scroll-demand change your application to Winforms (even will be hard to work with 6.000 rows). – Jaime García Pérez Feb 23 '15 at 16:18
  • By the way @bmurrell30, what is taking 10 seconds exactly ? Querying the data, rendering it, or both ? You can use Asp.Net tracing to check which method is taking the most time. – thomasb Feb 23 '15 at 16:22
  • It was the rendering that's taking so long. I have edited my post. – bmurrell30 Feb 23 '15 at 16:47

4 Answers4

1

Most of your solution will come in a few forms (none of which have to do with a Gridview):

  1. Good indexes. Create good indexes for the tables that pull this data; good indexes are defined as:

    • Indexes that store as little information as actually needed to display the product. The smaller the amount of data stored, the greater amount of data can be stored per 8K page in SQL Server.
    • Covering indexes: Your SQL Query should match exactly what you need (not SELECT *) and your index should be built to cover that query (hence why it's called a 'covering index')

  2. Good table structure: this goes along with the index. The fewer joins needed to pull the information, the faster you can pull it.

  3. Paging. You shouldn't ever pull all 6000+ objects at once -- what user can view 6000 objects at once? Even if a theoretical superhuman could process that much data; that's never going to be your median usecase. Pull 50 or so at a time (if you really even need that many) or structure your site such that you're always pulling what's relevant to the user, instead of everything (keep in mind this is not a trivial problem to solve)

The beautiful part of paging is that your clients don't even need to know you've implemented paging. One such technique is called "Infinite Scrolling". With it, you can go ahead and fetch the next N rows while the customer is scrolling to them.

George Stocker
  • 57,289
  • 29
  • 176
  • 237
  • This 'infinite scrolling' thing sounds promising -- you had me at 'paging that doesn't look like paging.' I'll look into this and get back to you. – bmurrell30 Feb 23 '15 at 16:25
  • The URL you provided for 'Infinite Scrolling' returns a 404 error. I think this is the page you meant to point to: http://www.sitepoint.com/implementing-infinite-scroll-jquery/?__kzp=0&__kzt=1424708810878 – bmurrell30 Feb 23 '15 at 16:29
  • Sorry, I'm on a train; SO keeps dropping off for me. Yes, that's the link (I picked the top one from the Google results. There are likely a lot more and better alternatives; but without specifics on your end, I can't do much more than I have). – George Stocker Feb 23 '15 at 16:37
  • Infinite scrolling turned out to be the way to go; I found a way to do it without completely rebuilding my project, so I'm pursuing that. It's opened up a whole new bag o' cats, though, so I'm going to start a new thread. Marking this as answered because you got me started. – bmurrell30 Feb 24 '15 at 15:22
  • Here's the link to that [new thread](http://stackoverflow.com/questions/28700034/infinite-scrolling-with-asp-net-gridview-not-detecting-div-scrolled-to-bottom-af) – bmurrell30 Feb 24 '15 at 15:42
0

If, as you're saying paging really is not an option (although I really doubt it ; please explain why you think it is, and I'm pretty sure someone will find a solution), there's really no way to speed up this kind of operation.

As you noticed, it's not the query that's taking long, it's the data transfer. Copying the data from one memory space (sql) to another (your application) is not that fast, and displaying this data is orders of magnitude slower.

Edit: why are your clients "firm on that point" ? Why do they think it's not possible otherwise ? Why do they think it's the best solution ?

thomasb
  • 5,816
  • 10
  • 57
  • 92
  • Fair enough. Please see my edits for why paging isn't an option. – bmurrell30 Feb 23 '15 at 16:15
  • 1
    As I edited, can you elaborate on why they think it's not an option ? Maybe they think "I want to work this way" and it's possible, even with paging (or infinite scrolling or something else), with some sort of work-around. – thomasb Feb 23 '15 at 16:18
  • They just want the entire catalog presented on page load; I've been warned by several sources that paging the results would be strongly rejected (exact words : "they'll flip out"). I agree that it would be the best option, but I just can't go that way and I'm looking for alternatives. – bmurrell30 Feb 23 '15 at 16:23
  • I understand that you want to please your clients, but understanding *why* they impose an "it-will-fail" approach on you may give you insights on possible solutions. By digging deeper into the customer's requirements, I sometimes find out that what they really need is not at all what they asked in the first place, or that it can solved in a simpler way. Can't you just talk to them ? – thomasb Feb 23 '15 at 16:28
0

There are many options to show a big largeset of data on a grid but third parties software. Try to use jquery/javascript grids with ajax calls. It will help you to render on client a large amount of rows. Even you can use the cache to not query many times the database. Those are a good grids that will help your to show thousands of rows on a web browser:

  1. http://www.trirand.com/blog/
  2. https://github.com/mleibman/SlickGrid
  3. http://demos.telerik.com/aspnet-ajax/grid/examples/overview/defaultcs.aspx
  4. http://w2ui.com/web/blog/7/JavaScript-Grid-with-One-Million-Records

I Hope it helps.

Jaime García Pérez
  • 953
  • 1
  • 10
  • 16
0

You can load all the rows into a Datatable on the client using a Background thread when the application (Web page) starts. Then only use the Datatable to populate your Grids etc....So you do not have to hit SQL again until you need to read / write different data. (All the other answers cover the other options)

Grantly
  • 2,546
  • 2
  • 21
  • 31