Help with SPs

Hi there...I'm noticing my pages are running a little slow and I'm
wondering if two SPs I have on every page are the culprits; any way we
can make these more efficient? There are three tables involved:
Zipc: this contains all the zip codes, latitudes and longitudes in the
US
Addr: this contains a user's mailing address
Adve: this contains an advertiser's mailing address
I am pulling content based on a site visitor's zip code (regardless of
whether they're a user or an advertiser), so I need to go into the Zipc
table and pull the Latitude and Longitude so I can determine a radius.
First, I get the latitude and longitude:
CREATE PROCEDURE getlocal
@.user nvarchar(30)
AS
select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
Uniq=@.user)
GO
'in other words, I'm telling the database to get me the latitude and
longitude from the Zipc table where the zip code in that table is equal
to the zip code in the Addr table where the User (or advertiser) is the
one viewing the site
Then, after getting the latitude and longitude and establishing the
outermost reaches of the radius, I pull the five advertisements closest
to the user in no particular order:
CREATE PROCEDURE getmorelocal
@.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
decimal(18,9)
AS
select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
ORDER BY newid()
GO
'in other words, give me five advertisements where their latitudes and
longitudes are contained within the parameters.
Any advice would be appreciated; thanks.Maybe this will help?
http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
"Erik Lautier" <lautier@.gmail.com> wrote in message
news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> Hi there...I'm noticing my pages are running a little slow and I'm
> wondering if two SPs I have on every page are the culprits; any way we
> can make these more efficient? There are three tables involved:
> Zipc: this contains all the zip codes, latitudes and longitudes in the
> US
> Addr: this contains a user's mailing address
> Adve: this contains an advertiser's mailing address
> I am pulling content based on a site visitor's zip code (regardless of
> whether they're a user or an advertiser), so I need to go into the Zipc
> table and pull the Latitude and Longitude so I can determine a radius.
> First, I get the latitude and longitude:
> CREATE PROCEDURE getlocal
> @.user nvarchar(30)
> AS
> select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> Uniq=@.user)
> GO
> 'in other words, I'm telling the database to get me the latitude and
> longitude from the Zipc table where the zip code in that table is equal
> to the zip code in the Addr table where the User (or advertiser) is the
> one viewing the site
> Then, after getting the latitude and longitude and establishing the
> outermost reaches of the radius, I pull the five advertisements closest
> to the user in no particular order:
> CREATE PROCEDURE getmorelocal
> @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> decimal(18,9)
> AS
> select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> ORDER BY newid()
> GO
> 'in other words, give me five advertisements where their latitudes and
> longitudes are contained within the parameters.
> Any advice would be appreciated; thanks.
>|||Aaron,
Thanks. I actually don't need to get too specific with the distances,
so trig functions are a little more than is necessary right now. What
concerns me more is to have the best syntax for the SPs I listed...any
advice on that? For example, rather than having a compound statement
in the first SP, would it be better to break it down into two SPs where
I get the Zip in the first one and then the lats and longs in the
second one? Or, conversely, would it be better to wrap *everything* up
into just one SP?
Erik
Aaron Bertrand [SQL Server MVP] wrote:
> Maybe this will help?
> http://databases.aspfaq.com/database/how-do-i-create-a-store-locator-feature.html
>
>
> "Erik Lautier" <lautier@.gmail.com> wrote in message
> news:1168881086.981248.69840@.51g2000cwl.googlegroups.com...
> > Hi there...I'm noticing my pages are running a little slow and I'm
> > wondering if two SPs I have on every page are the culprits; any way we
> > can make these more efficient? There are three tables involved:
> >
> > Zipc: this contains all the zip codes, latitudes and longitudes in the
> > US
> > Addr: this contains a user's mailing address
> > Adve: this contains an advertiser's mailing address
> >
> > I am pulling content based on a site visitor's zip code (regardless of
> > whether they're a user or an advertiser), so I need to go into the Zipc
> > table and pull the Latitude and Longitude so I can determine a radius.
> >
> > First, I get the latitude and longitude:
> >
> > CREATE PROCEDURE getlocal
> > @.user nvarchar(30)
> > AS
> > select Latitude, Longitude FROM Zipc WHERE ZipCode=(SELECT Zipc FROM
> > Addr WHERE [User]=@.user) OR ZipCode=(SELECT Zipc FROM Adve WHERE
> > Uniq=@.user)
> > GO
> > 'in other words, I'm telling the database to get me the latitude and
> > longitude from the Zipc table where the zip code in that table is equal
> > to the zip code in the Addr table where the User (or advertiser) is the
> > one viewing the site
> >
> > Then, after getting the latitude and longitude and establishing the
> > outermost reaches of the radius, I pull the five advertisements closest
> > to the user in no particular order:
> >
> > CREATE PROCEDURE getmorelocal
> > @.left decimal(18,9), @.righ decimal(18,9), @.latu decimal(18,9), @.latd
> > decimal(18,9)
> > AS
> > select Top 5 Titl, Cont, Addr, City, Stat, Zipc, Phon, Site, Emai,
> > Long, Lati, Phoy, Emay, (SELECT COUNT(Titl) FROM Adve WHERE (Long
> > BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd) FROM Adve
> > WHERE (Long BETWEEN @.left AND @.righ) AND Lati BETWEEN @.latu AND @.latd
> > ORDER BY newid()
> > GO
> > 'in other words, give me five advertisements where their latitudes and
> > longitudes are contained within the parameters.
> >
> > Any advice would be appreciated; thanks.
> >|||On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Thanks. I actually don't need to get too specific with the distances,
>so trig functions are a little more than is necessary right now. What
>concerns me more is to have the best syntax for the SPs I listed...any
>advice on that? For example, rather than having a compound statement
>in the first SP, would it be better to break it down into two SPs where
>I get the Zip in the first one and then the lats and longs in the
>second one? Or, conversely, would it be better to wrap *everything* up
>into just one SP?
The sub-selects return small sets, can't see any style problems.
J.|||Ok...having removed those two SPs on a couple test pages, it's apparent
that they are definintely the culprits...perhaps I've just got a slow
SQL Server on my host?
JXStern wrote:
> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
> wrote:
> >Thanks. I actually don't need to get too specific with the distances,
> >so trig functions are a little more than is necessary right now. What
> >concerns me more is to have the best syntax for the SPs I listed...any
> >advice on that? For example, rather than having a compound statement
> >in the first SP, would it be better to break it down into two SPs where
> >I get the Zip in the first one and then the lats and longs in the
> >second one? Or, conversely, would it be better to wrap *everything* up
> >into just one SP?
> The sub-selects return small sets, can't see any style problems.
> J.|||On 15 Jan 2007 14:16:51 -0800, "Erik Lautier" <lautier@.gmail.com>
wrote:
>Ok...having removed those two SPs on a couple test pages, it's apparent
>that they are definintely the culprits...perhaps I've just got a slow
>SQL Server on my host?
Do you have the proper indexes for the subs to run against? How to
they run on their own?
J.
>JXStern wrote:
>> On 15 Jan 2007 10:09:08 -0800, "Erik Lautier" <lautier@.gmail.com>
>> wrote:
>> >Thanks. I actually don't need to get too specific with the distances,
>> >so trig functions are a little more than is necessary right now. What
>> >concerns me more is to have the best syntax for the SPs I listed...any
>> >advice on that? For example, rather than having a compound statement
>> >in the first SP, would it be better to break it down into two SPs where
>> >I get the Zip in the first one and then the lats and longs in the
>> >second one? Or, conversely, would it be better to wrap *everything* up
>> >into just one SP?
>> The sub-selects return small sets, can't see any style problems.
>> J.