Horribly Inefficient SQL Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Horribly Inefficient SQL Statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-15 : 08:49:47

Scott writes "Hello,

Wondering if I can get some help with a SQL statement that uses WAY too many sub-selects to accomplish it's job.

SELECT DISTINCT deliverable_id,
ISNULL ((SELECT rank_id FROM Deliv_Prioritization o WHERE o.deliverable_id = d.deliverable_id AND o.target_id = d.target_id AND cust_group = 'AEP....001'), - 1),
ISNULL ((SELECT rank_id FROM Deliv_Prioritization o WHERE o.deliverable_id = d.deliverable_id AND o.target_id = d.target_id AND cust_group = 'WPS....001'), - 1)
FROM Deliv_Prioritization d WHERE (deliverable_id IN (SELECT track FROM prioritization WHERE parent=27574))

The ISNULL lines are built within a loop (I've pasted two of the 75 or so that are actually there). Basically it returns something like this:

deliverable_id, -1, 4
deliverable_id, 2, 5
deliverable_id, 1, -1

This way i can get all the rankings for a particular deliverable_id (people rank these "deliverables" according to how useful they think they are) in one row of the recordset.

I'd like to streamline this and make it more efficient. It takes a good 20-30 seconds to run this query. Any help? :)

Thanks,

Scott
sschluer@pacbell.net"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-15 : 09:55:06

How about something like this?


SELECT deliverable_id, 
  MAX(CASE WHEN cust_group = 'AEP....001' THEN rank_id ELSE -1 END),
  MAX(CASE WHEN cust_group = 'WPS....001' THEN rank_id ELSE -1 END)
  -- etc.
FROM Deliv_Prioritization
WHERE deliverable_id IN (
  SELECT track
  FROM prioritization
  WHERE parent = 27574)
GROUP BY deliverable_id

DGMelkin
Starting Member

24 Posts

Posted - 2002-02-15 : 10:04:53

You could even take it one step further and get rid of the IN statement. :

 
SELECT deliverable_id,
	MAX(CASE WHEN p.cust_group = 'AEP....001' THEN d.rank_id ELSE -1 END), 
	MAX(CASE WHEN d.cust_group = 'WPS....001' THEN d.rank_id ELSE -1 END)
FROM Deliv_Prioritazation d
	JOIN prioritization ON deliverable_id = track
WHERE parent=27574
GROUP BY deliverable_id

-D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources