Too many joins want to reduce - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Too many joins want to reduce

Author

Topic

vivek009
Starting Member

1 Post

Posted - 2012-02-22 : 12:38:00

Hello Experts,

Can anyone help me to reduce the number of joins in the query without impacting the output and there by improving the performance.

(SELECT DISTINCT Mmap1.AdvertiserID, Mmap1.AdvertiserName, CampaignID, CampaignName, PA1.ActionID,
A1.ActionDescription, StartDate, EndDate
FROM (SELECT CD.AdvertiserID, CD.AdvertiserName, CD.CampaignID, CD.CampaignName, MediaPlanID, StartDate, EndDate,
max(CD.CreateDate) CreateDate
FROM Campaign_Definition CD
JOIN ST_Campaign C
ON (CD.CampaignID = C.CampaignID)
WHERE DestinationUrl = 'All'
GROUP BY CD.AdvertiserID, CD.AdvertiserName, CD.CampaignID, CD.CampaignName, MediaPlanID, DestinationUrl,
StartDate, EndDate
) Mmap1
JOIN ST_Plmt_Mp_Ad PMA1
ON (Mmap1.MediaPlanID = PMA1.MediaPlanID)

JOIN (SELECT PlacementID, AdID, ActionID, DayDate, MAX(LoadTimeStamp) LoadTimeStamp
FROM ST_PlacementAdActionDay
GROUP BY PlacementID, AdID, ActionID, DayDate
) PA1
ON (PMA1.PlacementID = PA1.PlacementID
AND PMA1.AdID = PA1.AdID
AND DayDate between Mmap1.StartDate and (CASE WHEN Mmap1.EndDate IS NULL THEN GETDATE() ELSE Mmap1.EndDate END)
)

JOIN ST_Action A1
ON (PA1.ActionID = A1.ActionID
AND PMA1.AdvertiserID = A1.AdvertiserID)
WHERE Mmap1.CreateDate > '1/1/2011'
OR PA1.LoadTimeStamp > '1/1/2011' )

UNION

(SELECT DISTINCT Mmap2.AdvertiserID, Mmap2.AdvertiserName, CampaignID, CampaignName, PA2.ActionID,
A2.ActionDescription, StartDate, EndDate
FROM (SELECT CD.AdvertiserID, CD.AdvertiserName, CD.CampaignID, CD.CampaignName, MediaPlanID, StartDate, EndDate,
DestinationUrl, max(CD.CreateDate) CreateDate
FROM Campaign_Definition CD
JOIN ST_Campaign C
ON (CD.CampaignID = C.CampaignID)
WHERE DestinationUrl <> 'All'
GROUP BY CD.AdvertiserID, CD.AdvertiserName, CD.CampaignID, CD.CampaignName, MediaPlanID, StartDate, EndDate,
DestinationUrl
) Mmap2
JOIN ST_Plmt_Mp_Ad PMA2
ON (Mmap2.MediaPlanID = PMA2.MediaPlanID
AND Mmap2.DestinationUrl = PMA2.DestinationURL)

JOIN (SELECT PlacementID, AdID, ActionID, DayDate, MAX(LoadTimeStamp) LoadTimeStamp
FROM ST_PlacementAdActionDay
GROUP BY PlacementID, AdID, ActionID, DayDate
) PA2
ON (PMA2.PlacementID = PA2.PlacementID
AND PMA2.AdID = PA2.AdID
AND DayDate between Mmap2.StartDate and (CASE WHEN Mmap2.EndDate IS NULL THEN GETDATE() ELSE Mmap2.EndDate END)
)

JOIN ST_Action A2
ON (PA2.ActionID = A2.ActionID
AND PMA2.AdvertiserID = A2.AdvertiserID)
WHERE Mmap2.CreateDate > '1/1/2011'
OR PA2.LoadTimeStamp > '1/1/2011' )

Thanks in advance..

Mahdi Eftekhari Moghaddam
Starting Member

3 Posts

Posted - 2012-02-22 : 16:37:32

It seems you can use CTE which will reduce your code and improve your performance. Can you please post structure of your tables and what exactly you want to retrieve. Some sample data will be very helpful. I will have a look and come up with optimised query.

Regards
Mahdi Eftekhari

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources