Get index scan on zip code radius query - not sure why.. ! ?

Question

Get index scan on zip code radius query - not sure why.. ! ?

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

To be clear, you'll still need to do the radius calculation but the addition of the MAX variables I spoke of should really cut down on the number of complex calculations your query has to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

matt6749 Ten Centuries Points: 1204 More actions · Answer 2

Thanks Jeff! - p.s. is my sql ok? I repeat the fnDistance() function three times..

Matt

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

Elliott Whitlow

SSC Guru

Points: 102296

October 19, 2009 at 11:19 pm

#1067956

Not sure you have a lot of choices..

CEWII

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Actually, it's probably not...

In a CTE, create a result set using the "Narrow Horizontal Corridor" formulas we talked about. Then, in another CTE, do the radius calculations on the first CTE result set to get the final result set. Last but not least, do a final select on the CTE that did the radius calculations to do your count's, rownumbering, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

On second thought, since you need the calculated distance as part of the result set, you may want to include the calculated distance in the first CTE which is limited by the "NHC" calculations we spoke of... then you could jump right to a final CTE to select a distance of <10 and do your counts and things in some nice clean SQL. If you can, do the radius formula in the first CTE instead of using a function. Scalar functions are a form of RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

matt6749 Ten Centuries Points: 1204 More actions · Answer 6

Ok thanks Jeff - I am going to try, and try hard, to implement multiple CTEs like you suggest.. (gulp, wish me luck!) - Matt

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 7

matt6749 (10/19/2009)
Wow! I wouldn't have guessed that the formula in the query would be faster!

Much faster..

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

matt6749 (10/19/2009)
Ok thanks Jeff - I am going to try, and try hard, to implement multiple CTEs like you suggest.. (gulp, wish me luck!) - Matt

You'll do fine... just pretend each one is building a Temp Table... peel one potato at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions