Databases
StackOverflow2013
Postsdbo
POUsing LINQ .Select() to cast into new type is TOO slow?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing LINQ .Select() to cast into new type is TOO slow?
    primarykey
    data
    text
    <p>Current project, broke head over this problem:</p> <p>Client Repository:</p> <pre><code>public class ClientRepository { // Members private masterDataContext _db; // Constructor public ClientRepository() { _db = new masterDataContext(); } public IEnumerable&lt;ClientName&gt; GetCorporateClientNames() { return _db.corporate_client_tbs.Select(o =&gt; new ClientName { id = o.id, name = o.company_name }).AsEnumerable(); } public IEnumerable&lt;ClientName&gt; GetRetailClientNames() { return _db.retail_client_tbs.Select(o =&gt; new ClientName { id = o.id, name = o.name }).AsEnumerable(); } // Define return type public class ClientName { public int id { get; set; } public string name { get; set; } } } </code></pre> <p>Now in the Controller I have the following:</p> <pre><code>public ActionResult Index() { var _visits = _db.GetAllServiceVisits(); return View(_visits); } </code></pre> <p>Which takes approximately 4 seconds to load the view with the 200 odd rows currently present.</p> <p>I want to add a property "client" to the visit model which contains the name of the client. The name of the client will come from one of two different tables which is fetched from one of two arrays of type "ClientName".</p> <p>This is the first approach, which used LINQ :</p> <pre><code>public ActionResult Index() { private ClientRepository _cr = new ClientRepository(); var _retailclients = _cr.GetRetailClientNames().ToArray(); var _corporateclients = _cr.GetCorporateClientNames().ToArray(); var _visits = _db.GetAllServiceVisits(); var _temp = _visits.Select(o =&gt; new ServiceVisitViewModel { service_visit = o, client = (o.client_type ? _corporateclients.Where(p =&gt; p.id == o.client_id).First().name : _retailclients.Where(p =&gt; p.id == o.client_id).First().name) }).ToArray(); return View(_temp); } </code></pre> <p>This is the second approach, using plain 'ol C# :</p> <pre><code>public ActionResult Index() { private ClientRepository _cr = new ClientRepository(); var _retailclients = _cr.GetRetailClientNames().ToArray(); var _corporateclients = _cr.GetCorporateClientNames().ToArray(); var _visits = _db.GetAllServiceVisits(); List&lt;ServiceVisitViewModel&gt; _temp = new List&lt;ServiceVisitViewModel&gt;(); foreach (service_visit_tb v in _visits) { _temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p =&gt; p.id == v.client_id).First().name : _retailclients.Where(p =&gt; p.id == v.client_id).First().name) }); } return View(_temp); } </code></pre> <p>The second approach is about 8 - 10 times faster based on my tests.</p> <p>The only difference I can see is the .Select statement.</p> <p>Can someone please tell me if I have done something wrong in the first approach or in the alternative, why the first approach is so !@#$ing slow?!</p> <p><b>Edit:</b> The _db.GetAllServiceVisits() definition is as follows:</p> <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits() { var _visits = _db.service_visit_tbs; return _visits.AsEnumerable(); } </code></pre> <p><b>End Edit</b></p> <p><b>Second Edit:</b> I have removed this line from every entry in the log:</p> <pre><code>-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1 </code></pre> <p>The Context Log is as follows:</p> <pre><code>// This query is to fetch all the clients of Type One (corresponding to _cr.GetRetailClientNames() ) SELECT [t0].[id], [t0].[name] FROM [genii].[retail_client_tb] AS [t0] // This query is to fetch all the clients of Type Two (corresponding to _cr.GetCorporateClientNames() ) SELECT [t0].[id], [t0].[company_name] AS [name] FROM [genii].[corporate_client_tb] AS [t0] // This is the main query (loading roughly 250 records) which fetchs all Visits SELECT [t0].[id], [t0].[client_type], [t0].[client_id], [t0].[machine_type], [t0].[machineID], [t0].[visit_type], [t0].[scheduledon], [t0].[arrivedon], [t0].[completedon], [t0].[reported_problem], [t0].[diagnosed_problem], [t0].[action_taken], [t0].[visit_status], [t0].[engineer_id], [t0].[reference_id], [t0].[addedby], [t0].[addedon], [t0].[modifiedby], [t0].[modifiedon] FROM [genii].[service_visit_tb] AS [t0] // These next queries are not being manually called by me, I assume they are being // called when the Razor view is compiled since I am calling the name value of a linked table as such: // @item.service_visit.engineer_tb.name SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8] SELECT [t0].[id], [t0].[status] FROM [genii].[visit_status_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [1] SELECT [t0].[id], [t0].[name] FROM [genii].[engineer_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3] SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [11] SELECT [t0].[id], [t0].[name] FROM [genii].[engineer_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2] SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [7] SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2] SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [6] SELECT [t0].[id], [t0].[type] FROM [genii].[visit_type_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [3] SELECT [t0].[id], [t0].[name] FROM [genii].[engineer_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [5] SELECT [t0].[id], [t0].[name] FROM [genii].[engineer_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [4] SELECT [t0].[id], [t0].[status] FROM [genii].[visit_status_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [8] SELECT [t0].[id], [t0].[status] FROM [genii].[visit_status_tb] AS [t0] WHERE [t0].[id] = @p0 -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2] </code></pre> <p>Addendum to the question: Is there a better way to pull this data? I always presumed (never checked) that the foreign key based data access that LINQ context gave me was as good as it gets, but seeing these additional queries, I'm not so sure anymore!</p> <p>Will post speeds for the second part of execution later today (its a long weekend here in Mumbai but we are working right through)</p> <p><b>End Edit</b></p> <p><b>Third Edit</b> (I am considering response from webserver to client, since all computation / fetching / binding / etc etc should be accounted for.) <br></p> <p><b>Method One : 6.85 seconds</b> (Call from 3 tables and then do casting into View-Model using C#)</p> <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits() { var _visits = _db.service_visit_tbs; _db.Log = new DebuggerWriter(); return _visits.AsEnumerable(); } public ActionResult Index() { var _retailclients = _cr.GetRetailClientNames().ToArray(); var _corporateclients = _cr.GetCorporateClientNames().ToArray(); var _visits = _db.GetAllServiceVisits(); List&lt;ServiceVisitViewModel&gt; _temp = new List&lt;ServiceVisitViewModel&gt;(); foreach (service_visit_tb v in _visits) { _temp.Add(new ServiceVisitViewModel { service_visit = v, client = (v.client_type ? _corporateclients.Where(p =&gt; p.id == v.client_id).First().name : _retailclients.Where(p =&gt; p.id == v.client_id).First().name) }); //} return View(_temp); } </code></pre> <p><b>Method Two : 8.59 seconds</b> (Call from 3 tables and then do casting into View-Model using LINQ)</p> <pre><code>public IEnumerable&lt;service_visit_tb&gt; GetAllServiceVisits() { var _visits = _db.service_visit_tbs; _db.Log = new DebuggerWriter(); return _visits.AsEnumerable(); } public ActionResult Index() { var _retailclients = _cr.GetRetailClientNames().ToArray(); var _corporateclients = _cr.GetCorporateClientNames().ToArray(); var _visits = _db.GetAllServiceVisits(); var _temp = _visits.Select(o =&gt; new ServiceVisitViewModel { service_visit = o, client = (o.client_type ? _corporateclients.Where(p =&gt; p.id == o.client_id).First().name : _retailclients.Where(p =&gt; p.id == o.client_id).First().name) }); return View(_temp); } </code></pre> <p><b>Method Three : 5.76 seconds</b> (Everything in a single LINQ query - executed on Database)</p> <pre><code>public IEnumerable&lt;ServiceVisitViewModel&gt; GetAllServiceVisitsNew() { var _visits = _db.service_visit_tbs.Select(o =&gt; new ServiceVisitViewModel { service_visit = o, client = (o.client_type ? _db.corporate_client_tbs.Where(c=&gt; c.id == o.client_id).First().company_name : _db.retail_client_tbs.Where(c=&gt; c.id == o.client_id).First().name) }); _db.Log = new DebuggerWriter(); return _visits; } public ActionResult Index() { var _visits = _db.GetAllServiceVisitsNew(); return View(_visits()); } </code></pre> <p>Guess that decides it. Thanks to everyone for the help. I am marking Jon as correct answer since his approach of doing everything on database side is what brought home the bacon. Many thanks to anyone and everyone who took the trouble to reply.</p> <p><b> End Edit </b></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVarun Vohra
    1. USVarun Vohra
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COLooks odd, `.Select` itself should not add anything big. Such big difference indicates that the database queries differ. Can you run SQL Profiler and check whether the two alternatives generates the same SQL workload?
      singulars
      1. POUsing LINQ .Select() to cast into new type is TOO slow?
      1. USAlbin Sunnanbo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload