Extrememly long call for just a count...

[Duncan]

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

[Duncan]

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?

[Duncan]

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

[Thomas]

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

[Duncan]

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;
}

[Duncan]

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...

[Duncan]

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?

[Duncan]

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