Opinion Please - 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)

Opinion Please

Author

Topic

sharmon419
Starting Member

2 Posts

Posted - 2005-01-26 : 01:42:28

I would like opinions on if there is any better way to optimize this SP.

This table holds under 100 records at any given time, I want to get a semi-random record from my banner ads table to display a banner ad on the site. I originally was just using a query to get the record and if a record was found then I was then using the returned ID to then execute an update statement.

Now I am trying to do it in one trip with an SP.

I'd appreciate your feedback, thanks!

Create Procedure prc_ViewBannerAd

AS

DECLARE
@ID int,
@ERR int

SELECT TOP 1
@ID = [ID]
FROM
[BannerAds]
WHERE
(Disabled = 0)
AND (TotalViews < MaxViews OR MaxViews = 0) AND (TotalClicks < MaxClicks OR MaxClicks = 0)
AND ((NonExpiring = 1) OR ((StartingDate >= GETDATE() OR StartingDate IS NULL) AND (EndingDate <= GETDATE() OR EndingDate IS NULL)))
ORDER BY NEWID()

IF (@ID IS NOT NULL)
BEGIN
SELECT
[ID],
[MouseOverText],
[ImagePath],
[NewWindow]
FROM
[BannerAds]
WHERE
[ID] = @ID
END

BEGIN
UPDATE
[BannerAds]
SET
[TotalViews] = [TotalViews] + 1
WHERE
[ID] = @ID
END

SET @ERR = @@ERROR
RETURN @ERR

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-26 : 03:30:04

I can't understand why you want it to be SEMI-random;

maybe if it'd be only ONE user on the whole web then it'd make some sense;

sharmon419
Starting Member

2 Posts

Posted - 2005-01-26 : 09:49:32

Stoad,

Thank you for your response.

All I really meant by SEMI-random is that I don't mind if the same recordset comes up twice in a row occasionaly which happens with just using NEWID() to sort with. I didn't want anyone to dwell on the fact that I am using NEWID(), I know it's a small performance hit on large tables. What I was concerned with is if there is a better way to write that SP than I already have.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-26 : 10:43:33

for 100 rows, it should be fine.

it *might* be faster, however, to do this:

1) deteremine the # of rows you would like to choose randomly from (call this "N")

2) get a random number from 1-N (call this "@M")

3) assign the @ID variable to the row at that random point like this:

set rowcount @M
select @ID = ID from ... where ...
set rowcount 0

That might be faster than the ORDER BY NewID(), but I'm not 100% sure. You may have to test it. The reason it may be faster is because no sorting is needed.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources