Local Temporary Table Bottlenecks?

6 Jan 2006 1:08 AM

Fabuloussites

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/articles/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 happend
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?

6 Jan 2006 1:40 AM

Adam Machanic

"Fabuloussites" <Fabuloussi***@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
--

6 Jan 2006 1:55 AM

Fabuloussites

Thanks for the info Adam, i appreciate it. The reason the temporary table is
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

Show quote

"Adam Machanic" wrote:

> "Fabuloussites" <Fabuloussi***@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
> --
>
>
>