Extrememly long call for just a count...

8 posts, 1 answers
  1. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 21 Oct 2011 Link to this post

    I have a table called Triggers that has a ton of data in it for each of my users. I call on it frequently to posts counts etc to the UI, so it is very important that i make these calls in a optimized fashion.

    Here is the call:
    ...
    Users user = dbContext.Users.Single(u => u.Credentials.Email == email);
    count = user.UserGroups.Triggers.Where(t => t.TriggerTypeID == 1 && t.TriggerDateTime >= startdate && t.TriggerDateTime <= enddate).GroupBy(t => t.IP).Count();
    ...

    I look up the user and from that entity i can find all of their triggers in the Triggers table. The call for the Count() takes up to 20+ seconds... Surely i am doing something wrong here? It must be pulling down EVERY trigger for the user!!!

    How can i fix this please?

    Thanks
    Duncan
  2. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 21 Oct 2011 Link to this post

    Confirmed. user.UserGroups.Triggers shows over 18000 records... After the Where clause is executed the count ends up being 0. Surely there is a better way to count ONLY what i need?
  3. DevCraft banner
  4. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 24 Oct 2011 Link to this post

    So i took the time to build out a SQL based query that does exactly the same thing:

    public int countVisitors(string email, DateTime startdate, DateTime enddate)
    {
        int count = 0;
     
        try
        {
            using (BluefireDBContext dbContext = new BluefireDBContext())
            {
                Users user = dbContext.Users.Single(u => u.Credentials.Email == email);
     
                StringBuilder sqlQueryString = new StringBuilder();
     
                sqlQueryString.Append("SELECT COUNT(DISTINCT(IP)) FROM Triggers ");
                sqlQueryString.AppendFormat("WHERE TriggerDateTime >= '{0}' ", startdate.ToString());
                sqlQueryString.AppendFormat("AND TriggerDateTime <= '{0}' ", enddate.ToString());
                sqlQueryString.AppendFormat("AND UserGroupID = {0} ", user.UserGroups.UserGroupID);
                sqlQueryString.Append("AND TriggerTypeID = 1");
     
                using (OACommand command = dbContext.Connection.CreateCommand())
                {
                    command.CommandText = sqlQueryString.ToString();
                    count = (int)command.ExecuteScalar();
                }
            }
        }
        catch (Exception ex)
        {
            //TODO: Add ERROR LOGGING and handling
        }
     
        return count;
    }

    And i got the call down to about 1 second...

    Could someone help to explain to me what it is about the LINQ query that is so expensive? Is it something i have done or can do to make it more optimized? Or should i stick with SQL queries to pull this data?

    Duncan
  5. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 24 Oct 2011 Link to this post

    Hello Duncan,

    I believe the difference is to be found in the way the UserGroupId is pushed as filter to the triggers.
    With LINQ, you expressed it as user.UserGroups.Triggers, with SQL you used a 'and UserGroupID = value'. Can you reformulate your LINQ query to that it is using the same approach (assuming that the Trigger class exposes the UserGroupId property)?

    All the best,
    Thomas
    the Telerik team

    NEW and UPDATED OpenAccess ORM Resources. Check them out!

  6. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 24 Oct 2011 Link to this post

    Like this?:

    public int countVisitors(string email, DateTime startdate, DateTime enddate)
    {
        int count = 0;
     
        try
        {
            using (BluefireDBContext dbContext = new BluefireDBContext())
            {
                Users user = dbContext.Users.Single(u => u.Credentials.Email == email);
                count = dbContext.Triggers.Where(t => t.TriggerTypeID == 1 && t.UserGroupID == user.UserGroups.UserGroupID && t.TriggerDateTime >= startdate && t.TriggerDateTime <= enddate).GroupBy(t => t.IP).Count();
            }
        }
        catch (Exception ex)
        {
            //TODO: Add ERROR LOGGING and handling
        }
     
        return count;
    }
  7. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 24 Oct 2011 Link to this post

    That changed things considerably...

    So my original implementation of the LINQ query was incorrect? I shoudl not be pulling my data fromt he original "Users" entity like i did...
  8. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 24 Oct 2011 Link to this post

    So just to clarify, would this be incorrect implementation to get the AccountTypeName of the current user in my DB?

    user.Credentials.AccountTypes.AccountTypeName

    Would this pull ALL records in the AccountTypes table?
  9. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 24 Oct 2011 Link to this post

    Thanks Thomas, i think i am understanding this better now... I guess it all depends on what the relationship between the entities are. user.UserGroups.Triggers will pull all Triggers for the user before the filter is applied, but something like user.Credentials.AccountTypes will only pull the account type for that user and not all account types...

    Eventually i will get LINQ completely... Bare with me... :)

    Thanks again
Back to Top
DevCraft banner