I went through the answer for Counts from two different tables in one query (14 May 2003). I have a similar requirement with a little addition. I need to retrieve a column (Status) as "Multiple" (static text) when COUNT(LEADID) is more than one. The query looks like this:

SELECT distinct tmp1.LeadID, count(*) 
, CASE count(*) WHEN 1 THEN (
  SELECT Name from Status S, Temp1_Div tmp 
  where S.StatusID = tmp.StatusID 
  and tmp.LeadDetailID=tmp1.LeadDetailID )
ELSE 'Multiple' END As Status
FROM Temp1_Div tmp1 
group by LeadID order by tmp1.LeadID desc

This query works as per the requirement. But because of huge data, it times out. I need your help in optimizing this.


    Requires Free Membership to View

    Login

Optimizing queries is best done by the database administrator for your installation, who will have valuable information about table sizes, indexes, execution paths, and so on. That said, optimizing can also be attempted by re-arranging the SQL, although I personally would not spend too much time trying variations before calling the DBA.

In your query, the join to the Status table is performed during group processing, which is somewhat unusual, although I can certainly understand why it was conceived that way -- no point in joining if there's more than one status, eh?

Here's one alternative:

    select D.LeadID
         , 1            as StatusCount
         , S.Name       as StatusName
      from Temp1_Div D
    inner 
      join Status S
        on D.StatusID = S.StatusID
     where D.LeadID 
        in ( select LeadID
               from Temp1_Div
           group by LeadID
             having count(*) = 1 )
union all
    select LeadID
         , count(*)
         , 'Multiple'
      from Temp1_Div
  group by LeadID
    having count(*) > 1

Note that the join to the Status table is performed for any LeadID only if it has exactly one row. You could imagine that the subquery to get the LeadIDs with just one row will be performed ahead of time, so that the IN condition operates essentially the same as though you had coded all those LeadIDs in a comma-delimited list. You could imagine it that way, and the optimizer might even execute it that way, or it might do a join instead, or perhaps an index lookup. The point is, you don't care how it does it, it'll pick the fastest way.

I make no guarantee that this alternate query will perform better than yours, but it's worth a try.

For More Information


This was first published in May 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top