Optomizing a sql Count Query.

Question

I am still fairly new to SQL so I wanted to know if I am doing this the most optimized way.

 SELECT DISTINCT ACCOUNTID, ACCOUNT_NAME
  (SELECT        COUNT(*)
  FROM            TICKET
  WHERE        (ACCOUNTID = OPPORTUNITY.ACCOUNTID)) AS [Number Of Tickets],
  (SELECT        COUNT(*)
  FROM            TICKET
  WHERE        (ACCOUNTID = OPPORTUNITY.ACCOUNTID) AND (STATUSCODE = 1 OR
         STATUSCODE = 2 OR
         STATUSCODE = 3)) AS [Active Tickets]
 from OPPORTUNITY
    where AccountID > @LowerBound and AccountID < @UpperBound

What I am trying to do is get a list of all accounts and have it show how many tickets the account has and how many are active (have a status code that is 1, 2, or 3). Is the select inside the select the correct way to do this or is there a way it can be done using something like group by.

My biggest concern is speed, it takes 3-5 seconds to just pull around 20 records and the query could potentially have 1000's of results.

I am not the DBA so any changes to table schema are not imposable but will require some pleading with upper management.

This is being run against SQL Server 2000.

EDIT-- as all of the answers where asking about it I checked on it. Both Opportunity and ticket index on accountid ascending.

Answer 1

I think the below should be logically equivalent and more efficient. Obviously test both aspects your end!

 SELECT O.ACCOUNTID, O.ACCOUNT_NAME,
   COUNT(*) AS [Number Of Tickets],
   ISNULL(SUM(CASE WHEN STATUSCODE IN (1,2,3) THEN 1 ELSE 0 END),0) 
                                                                AS [Active Tickets]
 FROM OPPORTUNITY O
 LEFT OUTER JOIN TICKET T ON T.ACCOUNTID = O.ACCOUNTID
    WHERE O.ACCOUNTID > @LowerBound and O.ACCOUNTID < @UpperBound
    GROUP BY O.ACCOUNTID, O.ACCOUNT_NAME

IF you can view the execution plans then you should check indexes exist on ACCOUNTID in both tables and are being used.

Answer 2

The SQL engine (even in 2000) is smart enough to optimize that sql. Based on your performance numbers with such few results, I'm guessing the source data had a bunch of records and does not have the indexes the sql needs.

Make sure there is an index on Opportunity.AccountID and an index on Ticket.AccountID.