2

There is a book management system web application based on ASP.NET MVC3. When click the book index page, data query is very slow and user has to wait for several seconds for response. The code of Action Index in BookController below:

    public ViewResult Index(string sortOrder, int? page)
    {
        ViewBag.NameSortParam = string.IsNullOrEmpty(sortOrder) ? "desc" : "";
        ViewBag.CurrentSort = sortOrder;

        BookModel books = from b in db.Books select b; // db = new BookContext();
        switch (sortOrder) {
            case "desc":
                books = books.OrderByDescending(b => b.Name);
                break;
            default:
                books = books.OrderBy(b => b.Name);
                break;
        }
        int pageSize = 15;
        int pageNumber = (page ?? 1);

        return View(books.ToPagedList(pageNumber, pageSize));
    }

In my opinion, the main reason of slow is that server does not response to client until all data are ready. This process takes much time.

I do not know how to solve this problem, is there any method to improve data query performance in this case? Thank you!

update: backend database is SQL Server Compact Edition 4.0

UPDATE I update my logic code below, orderby is used before skip and take statements. Everything goes well. Thank you all for help.

books = (from b in db.Books
                       orderby b.Name descending
                       select b)
                       .Skip((page - 1) * pageSize)
                       .Take(pageSize)
                       .ToList();
Improve this question
6
  • Your logic is very simple so there is not to much to improve here. You can do caching (request/session/application) of the Book enities or add a database index on the Name column in the Book table. Indices also seeds up ORDER BY queries. And it's also strange that it takes long time, how many lines do you have in the Books table? Maybe the slowness is caused some other code. Have you done any profiling? Commented Aug 20, 2012 at 8:03
  • I guess the ToPagedList is getting all the data from the table "Books". If so , you shouldn't use this function to get your page data. Use procedure to get page data is the best way. For table, you can set index for some columns. Commented Aug 20, 2012 at 8:07
  • 1
    First check whether ToPagedList applies pagination at database server or in memory? Commented Aug 20, 2012 at 8:10
  • Can you profile the SQl generated by books.ToPagedList to see what is happening there. Commented Aug 20, 2012 at 8:10
  • @nemesv nemesv, Thank you for your answer. I am a newbie in data query and cache technique, what you have said are difficult for me to implement. By the way, no large data stored in database, ten thousand at most. Commented Aug 20, 2012 at 8:20

3 Answers 3

Reset to default
3

You are loading in all your books before filtering the dataset to just the data you need (by using ToPagedList). Change your query and use the Skip and Take methods instead of ToPagedList.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

hello, zippyV, Could you show me some code snippets, I am a newbie.
return View( books.Skip( pageNumber * pageSize ).Take( pageSize ) );
I'm 99% percent sure that the ToPagedList in an extension method form the PagedList library and the implementation is using Skip and Take correctly.
Yes it does i have checked it too: it uses Skip and Take and another query to get the total number of the items.
Before skip and take, orderby operation is required. Thank you for help.
0

Put index on the Books.Name column (with included columns: include those which are needed in your list), on the C# side query only the filed you are needed:

books.Select(x => new {
                       x.Name,
                       x.Author,
                       x.Description,
                       x.ID }).ToPagedList(/*....*/)./*...*/)

And i would look after the ToPagedList implementation.

Improve this answer

6 Comments

An index on a column is useless if you don't use it in a where clause.
This is not true becouse if the table has no index on the sorted column (with some other column included) then it will scan the full table; whith indexes the database engine will scan the indexes if there is anything useful. This will end up in less I/O and maybe the index sort type is the same whith the sort order in the query but if not it will scan the index reverse order which is much more faster then resorting the data when the query runs.
Since there is no filter applied the full table will be scanned anyway therefore any index is useless.
You are right about the full table scan but if he will select only those columns which are really needed (and exists in one useful index) it will be much faster.
@PeterKiss hi, i do not know how to add index to column, could you show me code snippets, Thanks.
|
0

Use OutputCaching on the method, varying by the two parameters. An overview of this is detailed here.

[OutputCache(Duration=60,VaryByParam="sortOrder;page;")]
public ViewResult Index(string sortOrder, int? page)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.