How to speed up this query

Question

SBA-CDeCinko 0 Light Poster

12 Years Ago

The query below performs fairly quickly, however when hit by several visitors at once causes the SQL Server CPU to spike greatly. How would you rewrite this query to reduce the impact?

SELECT     *
FROM         (SELECT     TOP 1 a.adID, a.type, a.position, a.image, a.altText, a.caption, a.link, isNull(v.viewCount, 0) AS viewCount
                       FROM          bannerAds a LEFT OUTER JOIN
                                                  (SELECT     adID, count(viewID) AS viewCount
                                                    FROM          bannerAdsViews
                                                    WHERE      viewDateTime IS NOT NULL AND DateDiff(day, viewDateTime, GetDate()) = 0
                                                    GROUP BY adID) v ON a.adID = v.adID
                       WHERE      a.startDate <= getDate() AND a.endDate >= getDate() AND a.position = 1 AND type = 'standard'
                       ORDER BY viewCount) pos1
UNION ALL
SELECT     *
FROM         (SELECT     TOP 1 a.adID, a.type, a.position, a.image, a.altText, a.caption, a.link, isNull(v.viewCount, 0) AS viewCount
                       FROM          bannerAds a LEFT OUTER JOIN
                                                  (SELECT     adID, count(viewID) AS viewCount
                                                    FROM          bannerAdsViews
                                                    WHERE      viewDateTime IS NOT NULL AND DateDiff(day, viewDateTime, GetDate()) = 0
                                                    GROUP BY adID) v ON a.adID = v.adID
                       WHERE      a.startDate <= getDate() AND a.endDate >= getDate() AND a.position = 2 AND type = 'standard'
                       ORDER BY viewCount) pos2

mssql

2 Contributors
1 Reply
124 Views
2 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by BitBlt

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

BitBlt 452 Practically a Master Poster Featured Poster · Answer 1 · 2012-06-01T19:44:31+00:00

Could be any number of things causing it. Not knowing your table structures or your data volumes, I can only recommend generic things to try, such as:
1. Are your tables indexed? If not, you might try indexing them.
2. Are your tables volatile? As in, lots of random inserts/updates? If so, you might consider updating statistics or reorg-ing frequently.
3. You might consider putting it in a stored procedure, and using a local variable to store the results of GetDate(), then use that in your query.
4. If you decide to go the stored procedure route, you might also consider using a temp table to store the results of your subselect (since it's used in both parts of the union) and then join to that.

Sorry I can't be more specific than that. If I knew more about your table structures and data, I might take a swing at trying to rewrite the query altogether. Since I don't... well, you get the picture.

Good luck!