Using Variables / Parameters in a Select
-
I know I've seen this done before but I can't remember where (maybe I was smoking something that day). I want to be able to use a variable / parameter dynamically in a Select statement to cut down on the amount of calculations. This example is calculating the distance between 2 zipcodes.
Here's my example:
Select *
From dbo.PostalCodes P WITH(NOLOCK)
Where
Sqrt(
(69.1 * (P.Latitude - @Lat1)) *
(69.1 * (P.Latitude - @Lat1)) + (53 * (P.Longitude - @Long1)) *
(53 * (P.Longitude - @Long1))
) <= @Distance I'd like to do something like the following...
Select *
From dbo.PostalCodes P WITH(NOLOCK)
Where
Sqrt(
Set @x = (69.1 * (P.Latitude - @Lat1)) *
@x + Set @y = (53 * (P.Longitude - @Long1)) * @y
) <= @Distance The problem is that the @x and @y need calculated on a Row-by-Row basis. The original select is very fast, however, it seems unnecessary to calculate everything 3 times per row. Any ideas? -
It can be done in an update statement but not in select. You'd have to use a derived table, but I don't think you'd get much of a gain with that. Also I'm pretty sure that this calculation is done only once per row, but I can't prove it.
-
Because I don't think that query is using "any" index you can create a udf and make it that "bit" more efficient.
Cheers,
* Noel -
This would work...
SELECT *
FROM dbo.PostalCodes P WITH (NOLOCK)
WHERE SQRT(
POWER((69.1 * (P.Latitude - @Lat1)),2.000)
+ POWER((53 * (P.Longitude - @Long1)),2.000)
) <= @Distance
...but I believe this only works for short distances because the old SQRT(C2)=SQRT(A2+B2) Pythagorean formula (which is what you have) doesn't take the curvature of the Earth into acount nor the fact the the constant for longitude changes as you approach the poles of the Earth. You might want to find a better formula or switch to a "Donald Projection Coordinate System" which does flatten out the Earth.
Search for the term "DISTANCE" on this forum and you'll get a bunch of formulas. Search for the Haversine formula or calculation or "Great Circle Distance" on the Web and you'll also get some pretty good explanations as to why it's better than the formula you are currently using.
--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) -
Thanks for the replies, I guess it had to be done the long way. BTW, this was for a function, the function was enhanced using bits and pieces from all over and does take into account the Radius of the Earth at the specified @Latitude1.
Thanks!
-
What exactly are you trying to accomplish? Perhaps rather than telling how to do a specific thing (That may or may not be possible) you could detail what you need your end result to be?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
Not if you're using a constant for a multiplier on latitude... if you are not using a Cosine based formula, it does not take the curvature of the Earth into account. What you currently have posted is a very simple formula for calculating distances at or near the Equator. It will produce a distance error of (1-Cos(Lat))*100 % because you are using equtorial constants.
Now, I don't really care what you use 'cause it's your data... and when the PUC, State Attorney General, or SEC sues you and the company you work for for ripping off your customers distance-wise at the higher latitudes, that won't be my problem, either.
If you are using constants instead of Cosines, you ARE doing it wrong.
--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) -
I'm quite aware of that. The function I wrote contains a Parameter "@Accuracy" which can be set 1 - 3 (1 being the most accurate). I posted the "worst" accuracy version here.
I have already accomplished this, my only hope was that I wouldn't have to have SQL compute everything multiple times. It's quick enough though even when using the highest accuracy.
-
It would be much quicker if you would have table dbo.PostCodesDistance with columns
PostCode1ID, PostCode2ID, Distance.
You may insert/update records in this table every time you add new post codes in your database or change Latitude/Longitude for existing PostCodes. I would suggest to use triggers on dbo.PostalCodes.
Then you need to do calculations just ones per pair of PostCodes, when it's being created.
_____________
Code for TallyGenerator -
Are you sure that would be true in this case? A trip to the disk should always turn out to be slower than a relatively simple trig calculation...
--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) -
-
Only after I know the answer for sure
--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) -
They say that one of two betting is a cheater and another one is a fool.
_____________
Code for TallyGenerator -
That, my old friend, is the reason I won't bet you... neither of us got this far being the fool...
--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) -
Or actually neither of us got this far by being fooled twice for the same thing
.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply