Re: (ot) Transact-SQL Help

[Rick Root]

Brad, I'll have to look at your response in much greater detail, but I
can tell you this.

Currently, I'm running a CF script that populates a prospect_export
table once a day.  The initial query returns 25,785 rows, which gets
flattened into 20,265 rows for reporting purposes.

Ultimately I'd rather this were a view, but barring that, I'd rather
repopulate the table more often than once a day, and I'm fairly
certain that doing it with SQL instead of CF would be more efficient.


-- 
Rick Root
CFFM - Open Source Coldfusion File Manager
http://www.opensourcecf.com/cffm

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326228
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) Transact-SQL Help

[Leigh]

> As long as you are only dealing with a dozen or so records
> from the database it should perform fine and and I think it will be
> a heck of a lot simpler than trying to make your SQL server take a
> row-based list of  people and pivot them out into columns.  

Assuming the example is actually representative of the data, partitioning with 
row_number() may be a feasible alternative to pivoting or cursors.  But it 
would probably still require a bit of fancy footwork in terms of sql.

Having said that, Brad raises some valid questions about the data. Is it  
really that small and simple a recordset, and if so what is the perceived 
advantage of doing it in sql over using CF ?

-Leigh


   

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326216
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: (ot) Transact-SQL Help

[brad]

>  Original Message 
> Subject: (ot) Transact-SQL Help
> From: Rick Root 
> Date: Thu, September 10, 2009 12:25 pm
> To: cf-talk 
> 
> 
> I'm hoping someone here can point me in the right direction.  I'm
> doing something in CF that I really need to be doing in SQL:
> 

After reading your post several times I think I might understand just
what it is you are trying to accomplish here.  

Questions:  What makes you think you need to do this in SQL?  
Is the current code performing poorly?  
Are there really hundreds of columns and this is just a simplified
example?  
Are there really only 13 records in the ptTeam table or are you actually
doing dozens of times on one page?
Is there any particular difference between EXEC1 and EXEC2 or is it just
up to the randomness of the query as to who gets to be 1 and who gets to
be 2?
Do you know if you will have at least one of every type of person?

Since you asked-- as for SQL solutions, it looks like you are basically
wanting a column for each record matching a particular criteria.  My
first thought is a messy select statement that joins to the ptTeam table
once for every column.  This will actually be made harder if the order
of the members of the same type is not explicit.  

Note: you would need to change each join that isn't guaranteed to return
a match to an LEFT OUTER JOIN.  I'm also making the wild assumption that
your records all share the same entityid but have a unique MEMBERID. 
You also didn't say what column you wanted to return for each member so
I assumed you wanted the MEMBERID column.

SELECT TOP 1 EXEC1.MEMBERID AS EXEC1,
EXEC1.MEMBERID AS EXEC2,
TEAMLEAD.MEMBERID AS TEAMLEAD,
etc...
FROM ptTeam EXEC1 
INNER JOIN ptTeam EXEC2 ON EXEC1.entityid = 'foo'
AND EXEC1.status = 1
AND reltype='ETL'
AND EXEC1.MEMBERID <> EXEC2.MEMBERID
INNER JOIN ptTeam TEAMLEAD ON EXEC1.entityid = 'foo'
AND TEAMLEAD.status = 1
AND TEAMLEAD.leaderFlag=1
INNER JOIN etc...
WHERE EXEC1.status = 1
AND reltype='ETL'
AND EXEC1.entityid = 'foo'

For the record, I would expect this to perform like crap since it's
going to make every possible combination and then pick one to return
with TOP 1.

Moving on, another option would be to declare a table variable to hold
your final result set:

DECLARE @finalResult TABLE
(entityid int,
EXEC1 int,
EXEC2 int,
TEAMLEAD int,
MEMBER1 int,
MEMBER2 int,
MEMBER3 int,
MEMBER4 int
etc...)


Then populate your columns one statement at a time:

INSERT INTO @finalResult (entityid, EXEC1)
(SELECT entityid, MEMEBRID
FRKM PTRACK.dbo.ptTeam
WHERE status=1
AND reltype='ETL'

UPDATE fr
SET fr.EXEC2 = team.MEMBERID
FROM @finalResult fr
INNER JOIN ptTeam team ON fr.entityid = team.entityid
AND team.status=1
AND team.reltype='ETL'
AND fr.EXEC1 <> team.MEMBERID

UPDATE fr
SET fr.TEAMLEAD = lead.MEMBERID
FROM @finalResult fr
INNER JOIN ptTeam lead ON fr.entityid = lead.entityid
AND lead.status=1
AND lead.leaderFlag=1

etc...

Ok, now that I gave those examples let me say I think they're both
pretty crappy.  Honestly I'd do this in CF if it were me based on what
you showed us, but I would do it with query of queries.

Return your 15 or so records in a single, simple hit to the database. 
Then perform 6 qofqs on it-- one for each type of member. In other
words, get all the EXECs in one, and all team lead in another, and then
just loop over them as you go and output them as EXEC1, EXEC2, TEAMLEAD,
etc.

As long as you are only dealing with a dozen or so records from the
database it should perform fine and and I think it will be a heck of a
lot simpler than trying to make your SQL server take a row-based list of
people and pivot them out into columns.  That never works easily because
it really isn't the kind of thing SQL Server was designed to do.  On the
cursor thing-- that is another option I didn't present, but I really
don't think it's a better one.

Thanks.

~Brad





~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326214
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: (ot) Transact-SQL Help

[Leigh]

You might also take a look at ms sql 2005's row_number() function.  You may be 
able to partition the data and use row_number() to limit the returned records.  

http://msdn.microsoft.com/en-us/library/ms186734.aspx 




  

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326213
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: (ot) Transact-SQL Help

[Dave Watts]

> This is where the limiting nature of SQL comes in--SQL is a set language
> and has no (or very limited) looping/reshaping capability.  Unless MS
> has extended their SQL in ways I'm not aware of, this would be nearly
> impossible.  That's where the power of CF comes in.

That's not true. You can use cursors to iterate through a recordset,
for example.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more informatio

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326210
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: (ot) Transact-SQL Help

[Ben Conner]

Hi Rick,

This is where the limiting nature of SQL comes in--SQL is a set language
and has no (or very limited) looping/reshaping capability.  Unless MS
has extended their SQL in ways I'm not aware of, this would be nearly
impossible.  That's where the power of CF comes in.

--Ben

Rick Root wrote:
> I'm hoping someone here can point me in the right direction.  I'm
> doing something in CF that I really need to be doing in SQL:
>
> I need to flatten this data:
>
>   select
>   A.entityid,
>   A.MEMBERID,
>   A.RELTYPE,
>   A.leaderFlag
>   from
>   PTRACK.dbo.ptTeam A
>   WHERE
>   A.status=1
>
> into this format:
>
>   entityid,
>   EXEC1,  (1st row reltype='ETL')
>   EXEC2,  (2nd row reltype='ETL')
>   TEAMLEAD,   (1st row leaderFlag=1)
>   MEMBER1,(1st row reltype='TMM' and leaderflag=0)
>   MEMBER2,(2nd row reltype='TMM' and leaderflag=0)
>   MEMBER3,(3rd row reltype='TMM' and leaderflag=0)
>   MEMBER4,(4th row reltype='TMM' and leaderflag=0)
>   MEMBER5,(5th row reltype='TMM' and leaderflag=0)
>   AFSTAFF1,   (1st row reltype='AFS')
>   AFSTAFF2,   (2nd row reltype='AFS')
>   AFVOL,, (1st row reltype='AFV')
>   DOCTOR1,,   (1st row reltype='AMD')
>   DOCTOR2,,   (1st row reltype='AMD')
>
>
> The query returns multiple rows per entity.  The output will be one
> row per entity.
>
> In CF, I'm looping over the first query, and creating a resultset that
> looks like the second query.  If an entity has more than 1 executive
> team leader or more than 5 team members or more than 2 doctors
> associated, we ignore the extras.
>
> But I don't have the slightest friggin' clue how to do this in SQL.
>
> My db is MS SQL Server 2005
>
> Thanks
>
>

--
Ben Connerb...@webworldinc.com
Web World, Inc.   888-206-6486
PO Box 1122   480-704-2000
Queen Creek, AZ 85242



~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326208
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

(ot) Transact-SQL Help

[Rick Root]

I'm hoping someone here can point me in the right direction.  I'm
doing something in CF that I really need to be doing in SQL:

I need to flatten this data:

select
A.entityid,
A.MEMBERID,
A.RELTYPE,
A.leaderFlag
from
PTRACK.dbo.ptTeam A
WHERE
A.status=1

into this format:

entityid,
EXEC1,  (1st row reltype='ETL')
EXEC2,  (2nd row reltype='ETL')
TEAMLEAD,   (1st row leaderFlag=1)
MEMBER1,(1st row reltype='TMM' and leaderflag=0)
MEMBER2,(2nd row reltype='TMM' and leaderflag=0)
MEMBER3,(3rd row reltype='TMM' and leaderflag=0)
MEMBER4,(4th row reltype='TMM' and leaderflag=0)
MEMBER5,(5th row reltype='TMM' and leaderflag=0)
AFSTAFF1,   (1st row reltype='AFS')
AFSTAFF2,   (2nd row reltype='AFS')
AFVOL,, (1st row reltype='AFV')
DOCTOR1,,   (1st row reltype='AMD')
DOCTOR2,,   (1st row reltype='AMD')


The query returns multiple rows per entity.  The output will be one
row per entity.

In CF, I'm looping over the first query, and creating a resultset that
looks like the second query.  If an entity has more than 1 executive
team leader or more than 5 team members or more than 2 doctors
associated, we ignore the extras.

But I don't have the slightest friggin' clue how to do this in SQL.

My db is MS SQL Server 2005

Thanks

-- 
Rick Root
CFFM - Open Source Coldfusion File Manager
http://www.opensourcecf.com/cffm

~|
Want to reach the ColdFusion community with something they want? Let them know 
on the House of Fusion mailing lists
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326206
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Rick Root]

Query optimization didn't have anything to do with it.

I was running getDistance(zip1,zip2) instead a WHERE IN clause.  My
getDistance() function, which was based on Russ' would actually do two
selects based on the zip code arguments.

For example

select prospect, name, address, city, state, zip
from prospects
where zip in
( select zipcode from zipcodes where getDistance(prospects.zip,'27502') >
25)

Let's say there were 100,000 rows in the prospect table.

My original solution would've caused the getDistance() function to be called
100,000 times, and each time it would run two select queries to get the
latitude and longitude, and then calc the distance.

Effectively running 200,000 little select queries.

Indexes weren't going to help =)

At any rate, I got everything working super fast and blogged all about it.

Rick


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269029
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Billy Jamme]

Can you post the query execution plan?  It sounds like you forgot to add an 
index.  That or the you're killing the optimizer with the "SELECT *"; bookmark 
lookups can kill a DB.

>I've got my zip code database proximity thing all figured out.  I have a
>nice getdistance function that calcs the distance between two zip codes.
>
>It returns a result set containing all the zip codes within 25 miles of
>27502.
>
>But I can't use that in an IN clause... so what do I do?
>
>Rick

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268987
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Billy Jamme]

Can you post the query execution plan?  It sounds like you forgot to add an 
index.

>I have anice getdistance function that calcs the distance between two zip 
>codes.
>
>**snip**
>However, that is very slow.  It's very fast if I pass in the lat and long.

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268984
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Rick Root]

Jim, you make an excellent point that I hadn'e considered.

However, in my blog entry on the topic, I'm actually doubling the size of
the rectangle for the longitude, so I'm doing

@lat1-(@[EMAIL PROTECTED])

so even though my range factor technically should be larger, I actually
double it anyway, and that compensates for pretty much any different (except
maybe in Alaska, I'll have to check that)

The factor I'm multiplying by is 0.028985507

Northern alaska would be 0.52ish so I suppose I should actually multiply by
4 (4/69) to compensate.

Rick

On 2/6/07, Jim Wright <[EMAIL PROTECTED]> wrote:
>
>
> What calculation did you use to limit the longitude?...if you are using
> the same one as latitude, I'm not understanding...
> Let's take a latitude around the center of our home state of NC...35
> degrees...
> At 35 a degree of longitude is approx 57 miles long
> (per http://www.csgnetwork.com/degreelenllavcalc.html)
>
> 1 degree longitude/57 miles = .017544
> 1 degree latitude/69.172 miles = .014457
>
> ..014457 < .017544
>
> so you would be limiting too much of the longitude if you are only using
> the .014457 number.  Are you doing something different?  Or do I have it
> all wrong?
>
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268933
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transact-SQL help

[Robertson-Ravo, Neil (RX)]

We are quite lucky in that our Search Engine software has GeoSearch built in
:-) it is damn impressive to have it however you get it though, for $5 it's
a steal to get the data!




 





"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant,
Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions." 
Visit our website at http://www.reedexpo.com

-Original Message-
From: Rick Root
To: CF-Talk
Sent: Tue Feb 06 20:34:34 2007
Subject: Re: Transact-SQL help

I blogged all this here:

http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm




~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268885
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transact-SQL help

[Jim Wright]

Rick Root wrote:
> Actually, I did a limitation by longitude as well, because at the equator,
> the fudge factor is the same (approximatley 69 miles per degree)...
> everything in the US is less than that but I figured what the heck.  So I
> draw the box on both lat and long, knowing that the longitude will actually
> encompass MORE than the area I'm looking for... the getDistance() function
> actually does the work of exact distances anyway.  As long as the "box" is
> bigger than necessary, it's all good.
> 
> I was thinking it might actually be wise to use a range factor GREATER than
> 1/69 ... like 2/69...giving the latitude some wiggle room.
> 
> But thinking about the math involved as to *WHY* I'd do that makes *MY* head
> hurt!
> 
> 

What calculation did you use to limit the longitude?...if you are using 
the same one as latitude, I'm not understanding...
Let's take a latitude around the center of our home state of NC...35 
degrees...
At 35 a degree of longitude is approx 57 miles long
(per http://www.csgnetwork.com/degreelenllavcalc.html)

1 degree longitude/57 miles = .017544
1 degree latitude/69.172 miles = .014457

..014457 < .017544

so you would be limiting too much of the longitude if you are only using 
the .014457 number.  Are you doing something different?  Or do I have it 
all wrong?

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268884
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transact-SQL help

[Rick Root]

I blogged all this here:

http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268883
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transact-SQL help

[Russ]

You can also not think about the math, precompute the distance tables, and
then only keep data in there where the distance is  -Original Message-
> From: Rick Root [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 06, 2007 3:10 PM
> To: CF-Talk
> Subject: Re: Transact-SQL help
> 
> Actually, I did a limitation by longitude as well, because at the equator,
> the fudge factor is the same (approximatley 69 miles per degree)...
> everything in the US is less than that but I figured what the heck.  So I
> draw the box on both lat and long, knowing that the longitude will
> actually
> encompass MORE than the area I'm looking for... the getDistance() function
> actually does the work of exact distances anyway.  As long as the "box" is
> bigger than necessary, it's all good.
> 
> I was thinking it might actually be wise to use a range factor GREATER
> than
> 1/69 ... like 2/69...giving the latitude some wiggle room.
> 
> But thinking about the math involved as to *WHY* I'd do that makes *MY*
> head
> hurt!
> 
> 
> 
> On 2/6/07, Jim Wright <[EMAIL PROTECTED]> wrote:
> >
> > Rick Root wrote:
> > >
> > > I like how you're limiting by latitude only and using the radius as
> > well.
> > >
> >
> > Thinking about limiting it by longitude made my head hurt...I thought
> > about using some larger constant (like the 2 degrees that you used), but
> > in Alaska, 2 degrees longitude only equates to about 44 miles in some
> > places.  Limiting by latitude at least gets the bulk of the comparisons
> > out of the way.
> >
> >
> 
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268882
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transact-SQL help

[Rick Root]

Actually, I did a limitation by longitude as well, because at the equator,
the fudge factor is the same (approximatley 69 miles per degree)...
everything in the US is less than that but I figured what the heck.  So I
draw the box on both lat and long, knowing that the longitude will actually
encompass MORE than the area I'm looking for... the getDistance() function
actually does the work of exact distances anyway.  As long as the "box" is
bigger than necessary, it's all good.

I was thinking it might actually be wise to use a range factor GREATER than
1/69 ... like 2/69...giving the latitude some wiggle room.

But thinking about the math involved as to *WHY* I'd do that makes *MY* head
hurt!



On 2/6/07, Jim Wright <[EMAIL PROTECTED]> wrote:
>
> Rick Root wrote:
> >
> > I like how you're limiting by latitude only and using the radius as
> well.
> >
>
> Thinking about limiting it by longitude made my head hurt...I thought
> about using some larger constant (like the 2 degrees that you used), but
> in Alaska, 2 degrees longitude only equates to about 44 miles in some
> places.  Limiting by latitude at least gets the bulk of the comparisons
> out of the way.
>
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268878
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Jim Wright]

Rick Root wrote:
> 
> I like how you're limiting by latitude only and using the radius as well.
> 

Thinking about limiting it by longitude made my head hurt...I thought 
about using some larger constant (like the 2 degrees that you used), but 
in Alaska, 2 degrees longitude only equates to about 44 miles in some 
places.  Limiting by latitude at least gets the bulk of the comparisons 
out of the way.

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268875
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Rick Root]

so many solutions, so little time!

I like how you're limiting by latitude only and using the radius as well.

Doing that actually lowered my execution time to 94ms ...

 =)

On 2/6/07, Jim Wright <[EMAIL PROTECTED]> wrote:
>
> Russ wrote:
> > No, I'm referring to:
> >
> >
> http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818
> >
>
> That one is pretty quick here...
> SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b
> WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) <
> 10 AND a.zip = '27614'
>
> Took 1412 ms.
>
> I limited the latitude side of things with...
>
> SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b
> WHERE a.zip = '27614'
> AND b.latitude < a.latitude + (10*.014457)
> AND b.latitude > a.latitude - (10*.014457)
> AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) < 10
>
> which only took 170ms
>
> I think that .014457 is right...basically 1 degree/69.172 miles.
>
>
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268871
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Rick Root]

On 2/6/07, Russ <[EMAIL PROTECTED]> wrote:

> No, I'm referring to:
>
>
> http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818


 Ah, no I hadn't seen that one.  I've never used "CROSS JOIN" before..
interesting.

However, my final solution actually does the same thing in about 100ms for a
10 mile radius of 27502.

It actually does it in about 1500ms, but I added some code to only look at
zip codes whose lat/long were within a certain range, at jochem's
suggestion.  If I look at latitudes and longitutdes of 2.0 +/-, it improved
the query performance from 1542ms to 109ms.

Here's my solution - using your original getDistance function.

DECLARE @lat1 decimal(5,2);
DECLARE @long1 decimal(5,2);
DECLARE @zip1 char(5);
SET @zip1 = '27502'
SELECT @lat1 = LATITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1;
SELECT @long1 = LONGITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1;

SELECT B.CITY, B.STATE, B.zipcode
FROM ADSPRD.dbo.zipcodes B
WHERE
 B.LATITUDE BETWEEN @lat1-2.0 and @lat1+2.0
 AND B.LONGITUDE BETWEEN @long1-2.0 and @long1+2.0 AND
 ADSPRD.dbo.getDistance(@lat1,@long1,B.latitude,B.longitude) < 10
It's definately not as simple as your cross join solution, but it's the
equivalent of lightning fast by comparison.

Thanks for everyone's help on this!

Rick


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268870
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Transact-SQL help

[Jim Wright]

Russ wrote:
> No, I'm referring to:
> 
> http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818
> 

That one is pretty quick here...
SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b
WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) < 
10 AND a.zip = '27614'

Took 1412 ms.

I limited the latitude side of things with...

SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b
WHERE a.zip = '27614'
AND b.latitude < a.latitude + (10*.014457)
AND b.latitude > a.latitude - (10*.014457)
AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) < 10

which only took 170ms

I think that .014457 is right...basically 1 degree/69.172 miles.


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268863
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transact-SQL help

[Russ]

No, I'm referring to:

http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818

Russ

> -Original Message-
> From: Rick Root [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 06, 2007 12:42 PM
> To: CF-Talk
> Subject: Re: Transact-SQL help
> 
> Russ, if you're referring to this one:
> 
> *
> http://www.houseoffusion.com/groups/CF-
> Talk/thread.cfm/threadid:50203#268742
> *<http://www.houseoffusion.com/groups/CF-
> Talk/thread.cfm/threadid:50203#268742>
> 
> works fine if you want to pass in the lat/long directly, but I'm trying to
> come up with a way to do it off zip code.  your function got really slow
> when I added functionality inside to do lat/long lookups, because the
> function executes for each zip code you're comparing - up to 42000.
> 
> However, in attemping to explain all this, I've actually worked out a
> solution:
> 
> The following query worked pretty well, given that "TB907" (the address
> table) contains nearly 900,000 records. (Don't blame me for the table
> name,
> blame BSR, it's their product)
> 
> 
> DECLARE @long1 decimal(5,2)
> DECLARE @lat1 decimal(5,2);
> SELECT @long1 = dbo.getlongitude('27502');
> SELECT @lat1 = dbo.getLatitude('27502');
> 
> SELECT A.*
> FROM WEBREPORTS.dbo.TB907 A
> WHERE
>  zipcode in
>  (
>   SELECT B.zipcode
>   FROM zipcodes B
>   WHERE
>dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) < 25
>  )
> 
> --
> I'm not certified, but I have been told that I'm certifiable...
> Visit http://www.opensourcecf.com today!
> 
> 
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268855
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: OT: Transact-SQL help

[Rick Root]

On 2/6/07, Jochem van Dieten <[EMAIL PROTECTED]> wrote:
>
>
> This query is not indexable so it needs to do the math on each and every
> row. Prequalify the rows by drawing an imaginary box on the map from
> b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the
> points in that box (the database can do that using an index). Then
> perform your distance function only on the points in the box.


I did this to speed it up originally:

SELECT B.zipcode
FROM zipcodes B
WHERE
B.zipcode between '27002' and '28002'
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)

That caused it to only look at 1000 zip codes instead of 42000 zip codes.

the lat/long adjustment would probably be more effective.. or perhaps at
least, more accurate.

Rick

-- 
> I'm not certified, but I have been told that I'm certifiable...
> Visit http://www.opensourcecf.com today!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268854
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: Transact-SQL help

[Rick Root]

Russ, if you're referring to this one:

*
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
*

works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code.  your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.

However, in attemping to explain all this, I've actually worked out a
solution:

The following query worked pretty well, given that "TB907" (the address
table) contains nearly 900,000 records. (Don't blame me for the table name,
blame BSR, it's their product)


DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');

SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
 zipcode in
 (
  SELECT B.zipcode
  FROM zipcodes B
  WHERE
   dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) < 25
 )

-- 
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268853
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: OT: Transact-SQL help

[Jochem van Dieten]

Rick Root wrote:
> 
> syntax of my function:  getDistance(zip1,long1,lat1,zip2,long2,lat2)
> taking either the zip code or the lat/long for each...

> SELECT *
> FROM prospects A
> WHERE
>  zipcode in
>  (
>   SELECT B.zipcode
>   FROM zipcodes B
>   WHERE
>getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
>  )

This query is not indexable so it needs to do the math on each and every 
row. Prequalify the rows by drawing an imaginary box on the map from 
b.lat + X to b.lat -X and b.lon + X to b.lon -X and finding only the 
points in that box (the database can do that using an index). Then 
perform your distance function only on the points in the box.

Jochem

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268848
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: Transact-SQL help

[Russ]

Rick, 

Did you take a look at the query that I sent earlier?  It takes only 3
seconds to run on my machine.  

Russ

> -Original Message-
> From: Rick Root [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, February 06, 2007 11:49 AM
> To: CF-Talk
> Subject: OT: Transact-SQL help
> 
> I've got my zip code database proximity thing all figured out.  I have a
> nice getdistance function that calcs the distance between two zip codes.
> 
> syntax of my function:  getDistance(zip1,long1,lat1,zip2,long2,lat2)
> taking either the zip code or the lat/long for each...
> 
> I can now do:
> 
> SELECT *
> FROM prospects A
> WHERE
>  zipcode in
>  (
>   SELECT B.zipcode
>   FROM zipcodes B
>   WHERE
>getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
>  )
> 
> However, that is very slow.  It's very fast if I pass in the lat and long.
> 
> So I've written a stored procedure that when executed looks like this:
> 
> sp_zipcodes '27502' 25
> 
> It returns a result set containing all the zip codes within 25 miles of
> 27502.
> 
> But I can't use that in an IN clause... so what do I do?
> 
> Rick
> 
> --
> I'm not certified, but I have been told that I'm certifiable...
> Visit http://www.opensourcecf.com today!
> 
> 
> 

~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268844
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

OT: Transact-SQL help

[Rick Root]

I've got my zip code database proximity thing all figured out.  I have a
nice getdistance function that calcs the distance between two zip codes.

syntax of my function:  getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...

I can now do:

SELECT *
FROM prospects A
WHERE
 zipcode in
 (
  SELECT B.zipcode
  FROM zipcodes B
  WHERE
   getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
 )

However, that is very slow.  It's very fast if I pass in the lat and long.

So I've written a stored procedure that when executed looks like this:

sp_zipcodes '27502' 25

It returns a result set containing all the zip codes within 25 miles of
27502.

But I can't use that in an IN clause... so what do I do?

Rick

-- 
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!


~|
Upgrade to Adobe ColdFusion MX7 
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs 
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268841
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4