quote:

---

Indexed views can be more complex to maintain than indexes on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries.

---

quote:

---

Originally posted by slacker

I have to perform multiple joins to a table that contains over 50,000 rows

---

quote:

---

...2 of the tables in the join will have frequent inserts and updates.

---

create procedure GetSeminars
(
@zip 	   int
)

select
SeminarID
SeminarName,
Cost,
Instructor,
dbo.GeoCodeDistance( zip1.latitude, zip1.longitude, zip2.latitude, zip2.longitude ) as distance
FROM
Seminars
INNER JOIN Classes ON ClassID = Seminars.NextClass
INNER JOIN Addresses ON Seminars.PrimaryAddress = AddressID
INNER JOIN ZipCodes zip1 ON zip1.Zip = Addresses.Zip
INNER JOIN ZipCodes zip2 ON zip2.Zip = @zip

quote:

---

Originally posted by Seventhnight

calling a function might not be that great... can you add the calculations into the view itself??

Corey

---

quote:

---

Originally posted by Seventhnight

in my experience, if I am trying to make a query run faster, one of the first things I do is make sure all of the UDFs that can be done inline, are.

Their probably fine for a single record, or even a few, but a large recordset will slow down significantly b/c of them

Corey

---

 zip1   zip2   distance
 ------ ------ --------
 00001  00002  25
 00001  00003  89
 00002  00001  25
 00003  00001  89
 00003  00004  62

quote:

---

Originally posted by Seventhnight

billions??? I don't think so.

You said you have 50,000 records. Cross Join that is billions, but you only want ones that are within say 100 miles.
Assume there are an average of 100 zipcodes within 100 miles of another (that should be an overestimate). That gives 5 million recors. I would bet the average is much much less than 100, probably closer to 20ish... which would give a million...


just thoughts anyway...

Corey

---

create procedure dbo.GetSeminars
(
@zip 	   int
)


DECLARE @SIN_zip2_latitude sometype,
        @COS_zip2_latitude sometype,
        @zip2_longitude sometype

SELECT  @SIN_zip2_latitude = SIN(RADIANS(zip2.latitude)),
        @COS_zip2_latitude = COS(RADIANS(zip2.latitude)),
        @zip2_longitude = zip2.longitude
FROM    dbo.ZipCodes zip2 
WHERE   zip2.Zip = @zip




select
        SeminarID
        SeminarName,
        Cost,
        Instructor,
        dbo.GeoCodeDistance( zip1.latitude, zip1.longitude, zip2.latitude, zip2.longitude ) as distance
        (DEGREES(ACOS(
        SIN(RADIANS(zip1.latitude)) *
        @SIN_zip2_latitude +
        COS(RADIANS(zip1.latitude)) *
        @COS_zip2_latitude *
        COS(RADIANS(zip1.longitude - @zip2_longitude))
        )
        )
        ) * 69.09 AS distance

FROM    dbo.Seminars
        INNER JOIN dbo.Classes
                ON ClassID = Seminars.NextClass
        INNER JOIN dbo.Addresses 
                ON Seminars.PrimaryAddress = AddressID
        INNER JOIN dbo.ZipCodes zip1 
                ON zip1.Zip = Addresses.Zip
        INNER JOIN ZipCodes zip2 
                ON zip2.Zip = @zip

quote:

---

Originally posted by Kristen

Can you do the calculation at the client? All that trigonometry has got to take a while, hasn't it?

---