store procedure performance - 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 2000 Forums

Transact-SQL (2000)

store procedure performance

Author

Topic

michaelj
Starting Member

2 Posts

Posted - 2005-04-04 : 12:06:42

I have a stored procedure that retuens several recordsets of data for reporting

each of selects looks similar to this

SELECT
count(I.AdId) as Qty,
PlacementName as Placement

FROM
tbl_AdImpressionLog I
JOIN tbl_PageViewLog L ON I.PageViewId = L.Id
JOIN tbl_Ads A ON I.AdId = A.AdId
JOIN tbl_AdCustomers C ON A.AdCustomer = C.AdCustomerId
JOIN tbl_AdPlacements P ON I.PlacementBit = P.PlacementBit

WHERE
C.AdCustomerId = @iAdvertiserId
AND L.ViewDate >= @datFirstDate
AND L.ViewDate < @datLastDate

group by PlacementName
order by Qty DESC

There are indexes on al related columns
tbl_AdImpressionLog has > 20 million rows
the dates are in tbl_PageViewLog it has ~ 1 million rows

Would it help significantly to move the dates into the tbl_AdImpressionLog table?

At what point should I consider breaking this data down into monthly databases or tables?

mj

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 12:49:37

>>There are indexes on al related columns

That info needs to be expanded, type of index clustered or non, and which columns are in the index & their order in the index.

eg: If you had a clustered index on tbl_PageViewLog, with ViewDate as the 1st column in the index, it would very likely be used by the optimizer since you're querying date ranges.

What is the current result of SHOWPLAN on the query ? Index seeks, index scans or tablescans ?

michaelj
Starting Member

2 Posts

Posted - 2005-04-04 : 13:55:18

Indexes for tbl_PageViewLog are

Primary Key clustered column ID
non clustered index on catInd
non clustered index on ViewDate
non clustered index on AdImpressions

should I consider changing the clustered index to the viewdate?

The execution plan shows 56% on the join between the tbl_PageViewLog and tbl_AdImpressionLog and 31% on a table scan of tbl_AdImpressionLog

I am not real familiar with this stuff but that table scan seems to seem like a bad idea with 20 million rows.

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 16:13:18

Indeed, a tablescan on 20 million records is never good.
Is tbl_AdImpressionLog.AdID indexed ? If you are querying by @iAdvertiserId, this resolves into a set of AdID's, and an index on AdID in tbl_AdImpressionLog should be used by the optimizer (assuming it is selective enough).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources