SQL gurus, how can I make my query better?

[HungryHippos]

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:

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

 

[HungryHippos]

Thanks, i'll give it a crack. Is it possible to mess things up by creating too many indexes? I guess it would be. I'll make a note of the ones I add so I can remove them if necessary

 

[HungryHippos]

I added the indexes, but I think two of them were already on, the only one which wasn't was the GROUPID index on the USERS table. I also took some screen shots of the execution plan which I've put below.

http://img255.imageshack.us/img255/8376/pic1f.png
http://img152.imageshack.us/img152/676/pic2z.png

It's quite long! looks like all the workload is taken up on two of the sections though which is interesting.

Taking various lengths of time to do the same query here, one time it took 2.5 minutes, another 4.5, and the most recent took 10 minutes...!

 

[HungryHippos]

So are you happy that indexes are on all the join and primary key columns? If the query is run often then you can include the columns you are summing up on the index. This means there would be no key lookup shaving 50% of your time (at the cost of more storage space).

Also varying run times can hint at incorrect statistics. Also check the indexes have been defragged. If up to the second accuracy is not important you can allow dirty reads - should make it more consistent and not block other users.

We have a 2nd copy of the database for other reasons, i'm going to play around with that instead of adding more indexes to the live database.

The query would only be run occasionally by us as administrators. It wouldn't be run under normal circumstances.

The databases themselves won't change that frequently, we run tasks that go over night that update them but during the day when we would normally run queries like this, the data remains pretty consistent.