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.
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in May 2003