#25 poor performance for count(column)

[Anonymous]

Extremely poor performance for:

select count(field) from Type where 1 = 1

We use JoSQL over collections with 500,000 to 2,000,000 elements and are very pleased with the performance. Surprised at sudden drop of performance when using count.

[Gary Bentley]

Hi,

This is deliberate. The functions are not aware of grouping/aggregation operations such as "count". Instead it is better to do:

SELECT :_allobjs.size
FROM Type
WHERE 1 = 1

Performance can also be improved by using the "EXECUTE ON" clauses which ensure that operations are only executed once.

Below is an example of a query from the polliwog project that uses those clauses:

SELECT name name,
@pages pages,
visitCount visitCount,
@downloads downloads,
@start start,
@end end
FROM org.polliwog.data.SearchEngineVisit
GROUP BY name
GROUP BY ORDER @pages DESC
GROUP BY LIMIT 1,10
LIMIT 1,1
EXECUTE ON GROUP_BY_RESULTS sum (pagesCount) pages,
sum (downloadSize) downloads,
min (fromDate) start,
max (toDate) end
EXECUTE ON ALL sum(pagesCount) totalPages,
sum(downloadSize) totalDownloads,
min(fromDate) searchStart,
max(toDate) searchEnd,
sum (:_allobjs, visitCount) totalVisits,
grp (:_allobjs,
name).size engines