Good morning all,
I'm wondering if someone can help me with my incredibly slow select
statement.
My program executes a SINGLE select statement and then generates a report
based on the output. So far, it's worked great..until now.
Yesterday, I wanted to add a new report that lists all the files that have
more than one user accessing them and the users that accessed them. (this
is a file auditing product)
Here is the select that I used:
select Distinct ar.username userName, ar.filename fileName,
multiused.accesscount accessCount from auditrecords ar
inner join
(
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
on Upper(ar.filename) = multiused.filename
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?
Gene
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
