Correct configuration for related entities, etc

[ni...@customapp.co.za]

I've build this huge project (in a huge rush) using SharpLite after figuring out only what I needed to know at the time and got a bit stuck in doing things over and over the same way.
For instance I never figured out queries you got in SharpLite.

Now that I went live, I noticed some performance issues (not SL's fault!) and before start investigating what is the reason and how to address it, I was wondering if there's some suggestions on how to use SharpLite correctly.

For instance I got a "Client" entity and a "ClientMember".   Client have a list of ClientMember and ClientMember have a Client.

At this stage it seems when I use IEnumerable<Client> in a action method, retrieving with the usual _clientRepository.GetAll() it take ages (got several thousand members in tens of clients now)

So I fired up sql profiler and realized tons of queries get executed and assume it's because of the relationships   (In my View, for instance one value I print for each client is Client.Members.Where(x=.x.IsDependant).Count  .. pseudo code, but you get it?)

Could you suggest what I need to figure out to correctly do queries that does not go bananas all over the database?

[Billy McCafferty]

I've run into those exact same issues on previous projects.  You definitely want to figure out how to leverage the query objects; that's the key for you to resolve the performance issues you're running into.  By leveraging a LINQ query, at the very least, you'll be able to put the filtering on the DB side of things instead of the code side of things.  Even if you don't wrap your queries as a "query object," as demonstrated within the S#arp Lite demo code, you still want to use LINQ within your tasks layer when you retrieve the objects; e.g.:

_clientRepository.GetAll().Where(c => c.Blah = "whatever")

Is there a reason that you absolutely need to retrieve every single client?  If you really do, then I'd recommend creating a SQL view which aggregates the key information that you're looking for and then create an "entity" which maps to the view.  So that way, you're not running into the "n+1" issue in addition to loading too much information.

Hope this helps!

Billy McCafferty

--
You received this message because you are subscribed to the Google Groups "S#arp Lite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sharp-lite+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[ni...@customapp.co.za]

Thanks a lot for the advice B.

While (slowly) reading your blog article again (Get Started..btw, the images seems "gone") could you perhaps give me a push in the right direction regarding SQL Views and entities mapping to that?  (No clue, sorry)

Yes and now on "retrieving all clients".  In the new version I uses jQuery DataTables which will pull n number of records with each request.

My biggest issue is with the related data making things really slow (like showing the client.member.count) and a View will be awesome to use for these listing pages

[Billy McCafferty]

For mapping to a view, simply create a SQL view (http://www.w3schools.com/sql/sql_view.asp) and then create an entity for that view, as if it were a SQL table.  You can then query for the object via a repository as if it were just another entity.

For the jQuery data table, LINQ would be a huge timesaver.  Your query would be something like:

_clientRepository.GetAll().Skip(pageSize * pageNumber).Take(pageSize);

That way SQL is only returning pageSize items instead of thousands.

Billy McCafferty

--

[ni...@customapp.co.za]

Thanks, can still write views though.  ;-)  (but yeah, after years of ORM's, one's sql become pretty rusted.)

Ok, will give that a shot.  Sounds straightforward and simple.

Sorry if slow, but are saying, if I do "_clientRepository.GetAll().Skip(pageSize * pageNumber).Take(pageSize);" in the tasks, it will be different than doing it in my controller?

Thanks for all the feedback.  Really useful and honestly, only been just over a year and cannot imagine a life without SharpLite.  :-D

[Billy McCafferty]

"_clientRepository.GetAll().Skip(pageSize * pageNumber).Take(pageSize);" would execute identically in any layer; e.g., controllers or tasks.

And thanks for the positive feedback!  We've still been using S#arp Lite, completely unaltered, on all of our recent projects...some quite sizable.  On the front end, we're using AngularJS (with ServiceStack moving forward).  The combination with these on the front end and S#arp Lite on the backend has been working very well.

Billy McCafferty

--

[ni...@customapp.co.za]

Your suggestion using a view works awesome and don't need to do much even! 

 Dang, the "list client's" view load so fast, look like you using an in-mem collection.  :-D

Thanks a mill. 

Even if my next "test", using a specification query object (like you have IQueryable FindActiveCustomers(this IQueryable customers)  in the demo) does not eliminate nhibernate going nuts getting the member count for each client (actually 2 clients.Members.Where(x=>x.PrincipalMember == null, and vice versa) I still have an awesome solution for all these listing pages.

Thanks a mill.  Wish could show you my site, but it's totally an admin only site, driving a CMS website and Asp.Net Web API (yep, from SharpLite) feeding a mobile app.

Just started, already 10,000 members with hardly any issues (other than these performance issues which are totally my own fault)

[ni...@customapp.co.za]

Ok, last question then I promise to stop spamming your group.

I wanted to try out a query extension so did the following (not listing my "Client" domain object, think the dto and code explain it all)

Created a DTO

    public class ClientsListItemDto
    {
        public int Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public DateTime RegistrationDate { get; set; }
        public int MemberCount { get; set; }
        public int DependantsCount { get; set; }
    }

A query extension:

public static IQueryable<ClientsListItemDto> QueryForClientsList(this IQueryable<Client> clients)

{

    return from client in clients

            select new ClientsListItemDto

                {

                    Id = client.Id,

                    Name = client.Name,

                    Code = client.Code,

                    RegistrationDate = client.RegistrationDate,

                    MemberCount = client.Members.Count(x => x.PrincipalMember == null),

                    DependantsCount = client.Members.Count(x => x.PrincipalMember != null)

                };

}

And then in my controller just : 

public ActionResult Index2()

{

    IQueryable<ClientsListItemDto> clients = _repository2.GetAll().QueryForClientsList();

    return View(clients);

}

All seems to execute fine, looking in SQL profiler NH stop going loco , but the "MemberCount" and "DependantCount" is totally wrong.  "MemberCount" all is 0 (is 0 tot thousands for each client) and DependantCount is like 0 or 4, etc...also will be 0 to several hundred actually.

Having a bit of a wtf moment here.....