We use LLBLGEN Pro 4.2 Final and .Net 4.5.2

We issue a count on a IQuerable<JourneyEntity> or a getDbCount on JourneyCollection

The following query is executed

SELECT COUNT(*) AS NumberOfRows FROM (SELECT * FROM journey) TmpResult

which takes 319.5 Seconds! to execute.

When i use GetScalar on selfservicing code

EntityFields fields = new EntityFields(1);
fields[0] = EntityFieldFactory.Create(JourneyFieldIndex.Id);
fields[0].AggregateFunctionToApply = AggregateFunction.Count;
JourneyDAO dao = new JourneyDAO();
return (int)dao.GetScalar(fields, null, null,null, null);

the desired query is executed

SELECT COUNT(`journey`.`Id`) AS `Id` FROM `journey` LIMIT 1

which takes 20.4 Seconds to execute

is there a way to use the faster way on a IQueryable.Count();

I can reproduce the performance difference, but maybe because you are selecting all fields, unlike the EntityFields example. Try to have the produce the following: SELECT COUNT(*) AS NumberOfRows FROM (SELECT ID FROM journey) TmpResult

My test example:

var qf = new QueryFactory();

var q = qf.Create().Select(CustomerFields.CustomerId);

using (var adapter = new DataAccessAdapter())
{
    var count = adapter.FetchScalar<int>(qf.Create().Select(q.CountRow()));
}

that is indeed a better query, and be aware that measuring query performance is a tricky: the first query run is often slow as it has to load data from disk into memory, the second query likely benefits greatly from that.

I also think that there won't be much difference between the execution plans. did you look at those?

@Otis Reversed the execution of Queries so first executed the desired one

The timing was the same so not the issue of loading data to memory.

Also looked at the execution plans for both queries and they are defenitly not the same because of the temp table.

The most important questionis is there a method to alter the query building for a IQueryable.Count()

Or maybe as Walaa stated only select a single field when counting

rlucassen wrote:

@Otis Reversed the execution of Queries so first executed the desired one

The timing was the same so not the issue of loading data to memory.

Also looked at the execution plans for both queries and they are defenitly not the same because of the temp table.

Ah figured as much. Then that's the issue: the db will first insert all rows of the main query in the temp table, then do a count on it.

The most important questionis is there a method to alter the query building for a IQueryable.Count()

Or maybe as Walaa stated only select a single field when counting

With Linq that's not possible. You can rewrite the query in queryspec to get only the select count(*) from <table>. With Linq the count is always wrapping a query.

What indeed can help is selecting a PK field (or the PK in full) as it has an index. This is very important: an indexed field selected with a count will likely make the optimizer look at the index instead of creating a temp table. This also works with e.g. select count(field) from table instead of select count(*) from table. where 'field' is an indexed field.

Ok Thanx

I don't want to make a rewrite for every query which has a count. If one forgets to add a rewrite when adding a new table the calls to database can get slower as he is using the 'old' way of counting without anyone noticing.

I have Refactored my current Count method to use a Generic Type so it works for all entities. and use the first field of primary key as reference for counting

    static long Count<T>() where T: CommonEntityBase, new()
    {
        EntityFields fields = new EntityFields(1);

        fields[0] = new T().Fields.PrimaryKeyFields[0];
        fields[0].AggregateFunctionToApply = AggregateFunction.Count;

        CommonDaoBase dao = new CommonDaoBase();
        var count = dao.GetScalar(fields, null, null, null, null);

        return (long)count;
    }

I'd use ResultsetFields, not EntityFields, as it might be you use inheritance. And I'd check for whether there are PK fields. It's still possible to use entities without PK's (in which case I'd use the first field in the entity and add a CountRow aggregate on it).