Hello!
> On a database of 2.5 million records, it ran over 11 hours before I killed
> it. On smaller datasets it works fine.
> Any Advice?
See explain query plan for indices usage. And query
Select filename, accessCount from
(
SELECT eventTime, Upper(AuditRecords.fileName) fileName,
Count(Distinct Upper(AuditRecords.userName)) accessCount
FROM
AuditRecords
GROUP BY
Upper(AuditRecords.fileName)
) as used where used.accessCount > 1
) as multiused
is not optimal... You can use "having" condition as "having used.accessCount >
1" and simplificate subquery
Select filename, accessCount from
(
SELECT eventTime, Upper(AuditRecords.fileName) fileName,
Count(Distinct Upper(AuditRecords.userName)) accessCount
as
SELECT Upper(AuditRecords.fileName) fileName,
Count(Distinct Upper(AuditRecords.userName)) accessCount
Best regards, Alexey.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
