CODEWITHSUNDEEP
linq-to-sqlasp.net-mvc-2foreach
user560498
user560498

Reputation: 547

reduce number of round trips - linq-to-sql

Is it possible to reduce the number of round trips to the database during the execution of an asp.net mvc 2 action for the following specific case and in general? I'm using linq-to-sql. The following code results in something like 60 selects, which take 60 round trips to the database. How can I reduce the number of round trips ?

If more of my code is needed, I will post it.

My view model :

public class ArticlesContainerViewModel
{
    public ArticlesContainerViewModel()
    {
    }

    public Article CategoryArticle { get; set; }

    public IList<ArticlesNode> CategoryNodes { get; set; }
}

public class ArticlesNode
{
    public Article NodeArticle { get; set; }

    public IQueryable<Article> NodeItems { get; set; }
}

The view:

<ul class="tabs">
        <% foreach (var category in Model)
           { %>
        <li><a href="#" class="s">
            <%= Html.Encode(category.CategoryArticle.AbbreviatedTitle) %></a></li>
        <% } %>
    </ul>
    <!-- tab "panes" -->
    <div class="panes">
        <% foreach (var category in Model)
           {
        %>
        <div>
            <table style="width: 100%;" cellpadding="0" cellspacing="0">
                <% int counter = 0; foreach (var node in category.CategoryNodes)
                   {
                %>
                <%if (counter % 2 == 0)
                  { %>
                <tr>
                    <%} %>
                    <td class="content-container">
                        <div class="index-node-title">
                            <%= Html.Encode(node.NodeArticle.ArticleTitle)%>&nbsp;<span class="small1 darkGrey2">(<%= Html.Encode(node.NodeItems.Count().ToString())%>)</span>
                        </div>
                        <div class="index-node-content">
                            <ul class="index-node-content-list">
                                <% foreach (var item in node.NodeItems.Take(2))

Upvotes: 0

Views: 480

Answers (2)

Karel
Karel

Reputation: 2212

Take a look at PLINQO. It will optimize your queries. www.plinqo.com

Upvotes: 0

Tomas Jansson
Tomas Jansson

Reputation: 23472

The code is a little bit messy if you ask me, and I guess that the type of Model is of type IList<ArticlesContainerViewModel> or something similar. Your problem is that you are using IQueryable this "high" up in the application. The structure I would go for is:

1. First select all categories and put that in your `ViewModel` using a single query. 
2. From all your categories select all your articles or what else from the db in a single query, put the result in a dictionary where category is the key.

That way you should be able to get only two queries instead of m*(n+1) as you are having now.

Upvotes: 1

Related Questions