Soldato
Hi guys,
I'm trying to run a query to determine how much data a group of users has in our Zantaz EAS system, the problem is the query is taking a bloody long time to process, so I think it could use some optimisation. The databases are very large.
How can this be sped up?
This is what I currently have:
I've added my query designer view so you can better see how this is working, essentially I'm joining tables based on common fields.
http://img94.imageshack.us/img94/7842/easquery.jpg
So this works with 4 databases:
GROUPS
USERS
REFER
PROFILELOCATION
GROUPS contains all the groups with GROUPID/GROUPNAME.
USERS contains the list of users, each user has a GROUPID which can be linked to the GROUPS database to figure out which group a user belongs to. This database also lists the USERNAME and EASSTATUS values. If EASSTATUS = 0 then the user is enabled, 1 is disabled.
REFER lists messages that belong to users
PROFILELOCATION lists all the messages with compressed and uncompressed sizes
Cheers
I'm trying to run a query to determine how much data a group of users has in our Zantaz EAS system, the problem is the query is taking a bloody long time to process, so I think it could use some optimisation. The databases are very large.
How can this be sped up?
This is what I currently have:
Code:
SELECT USERS.USERNAME, USERS.USERID, USERS.EASSTATUS, SUM(PROFILELOCATION.COMPRESSEDSIZE) As COMPRESSED_SIZE,
SUM(PROFILELOCATION.UNCOMPRESSEDSIZE) As UNCOMPRESSED_SIZE
FROM GROUPS INNER JOIN
USERS ON GROUPS.GROUPID = USERS.GROUPID INNER JOIN
REFER ON USERS.USERID = REFER.USERID INNER JOIN
PROFILELOCATION ON REFER.MSGID = PROFILELOCATION.MSGID
WHERE GROUPS.GROUPID = 191 and REFER.FOLDERID > 0
GROUP BY USERS.USERNAME, USERS.USERID, USERS.EASSTATUS
ORDER BY USERS.USERNAME
I've added my query designer view so you can better see how this is working, essentially I'm joining tables based on common fields.
http://img94.imageshack.us/img94/7842/easquery.jpg
So this works with 4 databases:
GROUPS
USERS
REFER
PROFILELOCATION
GROUPS contains all the groups with GROUPID/GROUPNAME.
USERS contains the list of users, each user has a GROUPID which can be linked to the GROUPS database to figure out which group a user belongs to. This database also lists the USERNAME and EASSTATUS values. If EASSTATUS = 0 then the user is enabled, 1 is disabled.
REFER lists messages that belong to users
PROFILELOCATION lists all the messages with compressed and uncompressed sizes
Cheers