Local Temporary Table Bottlenecks?

Greetings,
I'm fairly new to stored procedures and temporary tables and i was going
through this tutorial for a banner ad system.
http://aspnet.4guysfromrolla.com/ar...033104-1.2.aspx
it's a nice write up, how over i'm concearned over some of its logic. In
part two, the author discusses how he generates a temporary table each time
the NRCA_sp_Get_Random_Banner Stored Procedure is called. This would happen
d
each time a banner ad is requested from a page.
My concearn regards scalibility. Let's say that there is a high traffic
website that display banners on its homepage.
1) Would that create a bottle neck?
2) what would happen if two browser requests invoke the
NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
3) Would the temp database create two temporary tables? Or, would one
request fail?
4) would it be better to make an actual table that is simply updated when
new ads are inserted or old ones are modified and query it?"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> 1) Would that create a bottle neck?
Maybe. It depends on many factors, such as memory, the disk system
tempdb is on, and how it's configured. See the following KBA for some
hints:
http://support.microsoft.com/default.aspx/kb/328551?

> 2) what would happen if two browser requests invoke the
> NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
Temp tables (except global temp tables, created with ##) are local to the
connection that created them. So it would work as expected.

> 3) Would the temp database create two temporary tables? Or, would one
> request fail?
Two.

> 4) would it be better to make an actual table that is simply updated when
> new ads are inserted or old ones are modified and query it?
I probably wouldn't use a table at all; I didn't read the article in
much depth, but I would probably do something like:
SELECT TOP 1
Ad_Id
FROM tbl_NRCA_Banner_Ads
WHERE
Page = @.Page
AND StartDate < getdate()
AND EndDate > getdate()
AND Active = 1
AND Weight <= @.MaxWeight
ORDER BY NEWID()
I believe this has the same net effect as the temp table approach -- but you
should test on your end to make sure.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thanks for the info Adam, i appreciate it. The reason the temporary table i
s
created is because each ad has a weight factor that will increase the
probability of its beign shows. So, an ad with weight 3 is three times more
likely to be shown that one with weight one.
So, the temporary table does this...
say there are two ads
ad one, weight 1
ad two, weight 3
i creates a temporary table with four records..
id | ad
1 ad one
2 ad two
3 ad two
4 ad two
so, ad two is given three entries (instead of one). Then a random selection
is made to get an ad and that will be the one that is displayed.
is there a better alternative to temp tables for this?
ad two
"Adam Machanic" wrote:

> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> Maybe. It depends on many factors, such as memory, the disk system
> tempdb is on, and how it's configured. See the following KBA for some
> hints:
> http://support.microsoft.com/default.aspx/kb/328551?
>
> Temp tables (except global temp tables, created with ##) are local to th
e
> connection that created them. So it would work as expected.
>
> Two.
>
> I probably wouldn't use a table at all; I didn't read the article in
> much depth, but I would probably do something like:
> SELECT TOP 1
> Ad_Id
> FROM tbl_NRCA_Banner_Ads
> WHERE
> Page = @.Page
> AND StartDate < getdate()
> AND EndDate > getdate()
> AND Active = 1
> AND Weight <= @.MaxWeight
> ORDER BY NEWID()
> I believe this has the same net effect as the temp table approach -- but y
ou
> should test on your end to make sure.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>