subject:"need some advice"

 Where would I find info on how to use a thread like that?
 I don't have a clue... the cf docs?  Know of a blog about it?
 

The cfdocs should pretty much cover it, you're just throwing a cfthread tag 
with action=run around the code that does the re-cache. 

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_t_04.html

--- Mary Jo


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

2008-04-25 Thread Rick Faircloth

So, would this be accurate?

cfthread action=run name=update_query

cfquery...

My huge union all query...

/cfquery

/cfthread

Is that all there is to it?  And this creates a running
of the query which doesn't involve the browser?  Is that what
this means (from the cfdocs):

run: Creates a thread and starts it processing.  Code in the
cfthread tag body runs simultaneously and independently of
page-level code and code in other cfthread tags.

Rick



 -Original Message-
 From: Mary Jo Sminkey [mailto:[EMAIL PROTECTED]
 Sent: Friday, April 25, 2008 10:32 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
  Where would I find info on how to use a thread like that?
  I don't have a clue... the cf docs?  Know of a blog about it?
 
 
 The cfdocs should pretty much cover it, you're just throwing a cfthread tag 
 with action=run around
the
 code that does the re-cache.
 
 http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_t_04.html
 
 --- Mary Jo



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

 Is that all there is to it?  And this creates a running
 of the query which doesn't involve the browser?  Is that what
 this means (from the cfdocs):

Basically, yes, although I wouldn't really think of it in terms of whether or 
not the browser is involved, it's more to do with the current running thread on 
the server that is processing the page...it starts a new thread that will call 
the query which will run independent of the rest of your code and will not have 
to wait for it to complete before passing the results back to the browser, it 
sort of just skips over that and keeps running while the new thread is off 
running at the same time in parallel. The end result being that the page will 
not take any longer to run as far as the user is concerned than a typical 
update would. 

It's something you want to be judicious about using, as you don't want to 
overtax a server by constantly creating new threads...but it's a nice solution 
for a situation like this where you have a small number of users that will be 
only be updating the tables once in awhile. 


--- Mary Jo





~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

And to reiterate, don't stick the entire query in there, it should be out in a 
CFC, or at the very least, in a separate template so if you make any changes, 
you aren't updating it in multiple files. You would just use a variable for the 
cachedwithin time sort of like this:

cfparam name=Request.CacheTime default=#CreateTimeSpan(0, 1, 0, 0)#

cfquery name=ReallyBigQuery datasource=MyDS 
cachedwithin=#Request.CacheTime#

/cfquery 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

Oops, sorry about that partial post

To reiterate, don't stick the entire query in there, it should be out in a CFC, 
or at the very least, in a separate template so if you make any changes, you 
aren't updating it in multiple files. You would just use a variable for the 
cachedwithin time sort of like this:

cfparam name=Request.CacheTime default=#CreateTimeSpan(0, 1, 0, 0)#

cfquery name=ReallyBigQuery datasource=MyDS 
cachedwithin=#Request.CacheTime#
   SQL Here
/cfquery


And then in your cfthread you would just have something like this, which calls 
the above code (you wouldn't necessarily need a request variable, depending on 
your application):

cfthread action=run name=refresh_cached_query
   cfset Request.CacheTime = 0
   cfinclude template = path_to_query_page.cfm
/cfthread



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

2008-04-25 Thread Rick Faircloth

Thanks for all the info, Mary Jo!

It's been ( and will be ) a big help!

Rick

 -Original Message-
 From: Mary Jo Sminkey [mailto:[EMAIL PROTECTED]
 Sent: Friday, April 25, 2008 2:24 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Oops, sorry about that partial post
 
 To reiterate, don't stick the entire query in there, it should be out in a 
 CFC, or at the very
least, in
 a separate template so if you make any changes, you aren't updating it in 
 multiple files. You
would just
 use a variable for the cachedwithin time sort of like this:
 
 cfparam name=Request.CacheTime default=#CreateTimeSpan(0, 1, 0, 0)#
 
 cfquery name=ReallyBigQuery datasource=MyDS 
 cachedwithin=#Request.CacheTime#
SQL Here
 /cfquery
 
 
 And then in your cfthread you would just have something like this, which 
 calls the above code (you
 wouldn't necessarily need a request variable, depending on your application):
 
 cfthread action=run name=refresh_cached_query
cfset Request.CacheTime = 0
cfinclude template = path_to_query_page.cfm
 /cfthread
 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-24 Thread Azadi Saryev

how and where is that url/link generated? can you also pass the property
type in the url? that can tell you which table to query...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/



Rick Faircloth wrote:
 Now's here's a new problem I haven't encountered.

 How do I use a url variable to link to a property details page
 when the properties are in 8 different tables?  The issue really isn't
 the variable, since I can use the aliased variable property_id (once I add 
 it to the query).

 I know I could use
 property_details.cfm?property_id=cfoutput#get_properties.property_id#/cfoutput,
 however, on the details page, I would need to search all eight property tables
 for the correct property id.

 Is this the method I'm stuck with because all the properties aren't in a 
 single table?
 Pass the aliased variable property_id and search all 8 tables for it?

 Perhaps I should just put them all into one big property table instead of 
 keeping them
 separate as the data vender has them...

 Thoughts?

 Thanks,

 Rick


   

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

This is a common problem. It sounds like having a single table with all of
the fields plus an extra field for the property 'type' may be a good idea -
or, in you Union query, select the property type for each row and pass that
in the Url, so:

select sa.street_number as prop_street_number...etc
  'acr' as propertyType
from smlc_acr

union all

 select sa.street_number as prop_street_number...etc
  'com' as propertyType
from smlc_com

HTH

Dominic




On 24/04/2008, Azadi Saryev [EMAIL PROTECTED] wrote:

 how and where is that url/link generated? can you also pass the property
 type in the url? that can tell you which table to query...

 Azadi Saryev
 Sabai-dee.com
 http://www.sabai-dee.com/



 Rick Faircloth wrote:
  Now's here's a new problem I haven't encountered.
 
  How do I use a url variable to link to a property details page
  when the properties are in 8 different tables?  The issue really isn't
  the variable, since I can use the aliased variable property_id (once I
 add it to the query).
 
  I know I could use
 
 property_details.cfm?property_id=cfoutput#get_properties.property_id#/cfoutput,
  however, on the details page, I would need to search all eight property
 tables
  for the correct property id.
 
  Is this the method I'm stuck with because all the properties aren't in a
 single table?
  Pass the aliased variable property_id and search all 8 tables for it?
 
  Perhaps I should just put them all into one big property table instead
 of keeping them
  separate as the data vender has them...
 
  Thoughts?
 
  Thanks,
 
  Rick
 
 
 

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Azadi, Dominic...

One thing I did notice is that the db vendor didn't put a field for the
property type in the commercial properties table.  When I transfer the data
from my temp table (created when I import the vendor's data from their text 
file)
I would need to add a property_type field for the permanent table,
along with the property_id field I've already added.

If I did that for the commercial properties table, then I'd have a 
property_type
field in each property table that I could add to the url link and pass to the
property details page to specify which table to search for the property.

Thanks for the input!

Rick

 -Original Message-
 From: Dominic Watson [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 5:36 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 This is a common problem. It sounds like having a single table with all of
 the fields plus an extra field for the property 'type' may be a good idea -
 or, in you Union query, select the property type for each row and pass that
 in the Url, so:
 
 select sa.street_number as prop_street_number...etc
   'acr' as propertyType
 from smlc_acr
 
 union all
 
  select sa.street_number as prop_street_number...etc
   'com' as propertyType
 from smlc_com
 
 HTH
 
 Dominic
 
 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

 If I did that for the commercial properties table, then I'd have a 
 property_type
 field in each property table that I could add to the url link and pass to the
 property details page to specify which table to search for the property.

I don't think you would need to add the property type field. The table
is the property type right? So, when you select from a given table,
you can just select a string to represent the property type:

SELECT   sa.*,
   'acr' as propertyType
FROM smlc_acr sa

UNION ALL

SELECT  sc.*,
  'com' as propertyType
FROMsmlc_com sc

etc

(I only put the select * for readability in this example).

HTH

Dominic

-- 
Blog it up: http://fusion.dominicwatson.co.uk

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

 SELECT   sa.*,
'acr' as propertyType
 FROM smlc_acr sa

I don't understand that, Dominic.

What does the 'acr' refer to?



 -Original Message-
 From: Dominic Watson [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 8:24 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
  If I did that for the commercial properties table, then I'd have a 
  property_type
  field in each property table that I could add to the url link and pass to 
  the
  property details page to specify which table to search for the property.
 
 I don't think you would need to add the property type field. The table
 is the property type right? So, when you select from a given table,
 you can just select a string to represent the property type:
 
 SELECT   sa.*,
'acr' as propertyType
 FROM smlc_acr sa
 
 UNION ALL
 
 SELECT  sc.*,
   'com' as propertyType
 FROMsmlc_com sc
 
 etc
 
 (I only put the select * for readability in this example).
 
 HTH
 
 Dominic
 
 --
 Blog it up: http://fusion.dominicwatson.co.uk
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

 I don't understand that, Dominic.

 What does the 'acr' refer to?

It's just a string, it doesn't *refer* to anything and it could be set
to anything you want - I set it to 'acr' because the table was called
smlc_acr.

So what I am doing there is selecting a 'propertyType' based on the
table that I am selecting from.

Make sense?

Dominic

-- 
Blog it up: http://fusion.dominicwatson.co.uk

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-24 Thread Azadi Saryev

with SELECT ... , 'acr' AS propertyType ... Dominic is creating a query
column propertyType with value 'acr'.
he just basically created 'in-memory' propertyType column - it exists
only in this particular query. but it is returned in query results and
you can use it as any other query column - i.e. pass its value in a url...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/



Rick Faircloth wrote:
 SELECT   sa.*,
'acr' as propertyType
 FROM smlc_acr sa
 

 I don't understand that, Dominic.

 What does the 'acr' refer to?




   

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Oh, wait... perhaps I'm missing the obvious.

In:

SELECT   sa.*,
   'acr' as propertyType
FROM smlc_acr sa

You're just explicitly assigning the value acr
to the variable, propertyType.  Right?


Rick



 -Original Message-
 From: Dominic Watson [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 12:02 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
  I don't understand that, Dominic.
 
  What does the 'acr' refer to?
 
 It's just a string, it doesn't *refer* to anything and it could be set
 to anything you want - I set it to 'acr' because the table was called
 smlc_acr.
 
 So what I am doing there is selecting a 'propertyType' based on the
 table that I am selecting from.
 
 Make sense?
 
 Dominic
 
 --
 Blog it up: http://fusion.dominicwatson.co.uk
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

Yup, not sure I'd call it a variable, but yes ;) (perhaps dataset cell
/ column, dunno!). Try adding it to your big ol Union query (will have
to be in each table) and run it - should then make perfect sense.

Dominic

On 24/04/2008, Rick Faircloth [EMAIL PROTECTED] wrote:
 Oh, wait... perhaps I'm missing the obvious.

 In:

 SELECT   sa.*,
   'acr' as propertyType
 FROM smlc_acr sa

 You're just explicitly assigning the value acr
 to the variable, propertyType.  Right?


 Rick



  -Original Message-
  From: Dominic Watson [mailto:[EMAIL PROTECTED]
  Sent: Thursday, April 24, 2008 12:02 PM
  To: CF-Talk
  Subject: Re: Need some advice on speeding up query...
 
   I don't understand that, Dominic.
  
   What does the 'acr' refer to?
 
  It's just a string, it doesn't *refer* to anything and it could be set
  to anything you want - I set it to 'acr' because the table was called
  smlc_acr.
 
  So what I am doing there is selecting a 'propertyType' based on the
  table that I am selecting from.
 
  Make sense?
 
  Dominic
 
  --
  Blog it up: http://fusion.dominicwatson.co.uk
 
 

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Works like a charm!  Thanks, guys!

Rick

 -Original Message-
 From: Dominic Watson [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 12:39 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...

 Yup, not sure I'd call it a variable, but yes ;) (perhaps dataset cell
 / column, dunno!). Try adding it to your big ol Union query (will have
 to be in each table) and run it - should then make perfect sense.

 Dominic

 On 24/04/2008, Rick Faircloth [EMAIL PROTECTED] wrote:
  Oh, wait... perhaps I'm missing the obvious.

  In:

  SELECT   sa.*,
'acr' as propertyType
  FROM smlc_acr sa

  You're just explicitly assigning the value acr
  to the variable, propertyType.  Right?

  Rick

   -Original Message-
   From: Dominic Watson [mailto:[EMAIL PROTECTED]
   Sent: Thursday, April 24, 2008 12:02 PM
   To: CF-Talk
   Subject: Re: Need some advice on speeding up query...

I don't understand that, Dominic.

What does the 'acr' refer to?

   It's just a string, it doesn't *refer* to anything and it could be set
   to anything you want - I set it to 'acr' because the table was called
   smlc_acr.

   So what I am doing there is selecting a 'propertyType' based on the
   table that I am selecting from.

   Make sense?

   Dominic

   --
   Blog it up: http://fusion.dominicwatson.co.uk

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-24 Thread Mary Jo Sminkey

 Up till now, my db's have been relatively small
 and fairly quick when being queried.  I've never had to bother
 with indexes or other methods of optimizing queries.
 
 However, I've now got a db of about 6400 real estate properties.

In addition to the indexing, have you considering just caching this query? 
Since you say it's typically much heavier on the read versus write side, that's 
an ideal situation to cache the data. You can certainly do this in the 
database, or easily in ColdFusion as well using the cachedwithin parameter. 
All you have to do is add some code that will refresh the cache after any 
writes (which is pretty easy to do just by calling the same query with the 
cachedwithin set to 0). With complex queries like this that are retrieving 
entire sets of data, the performance benefit simply from caching it can be 
quite significant, and certainly faster than using LIMIT. 








~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Hi, Mary Jo, and thanks for the info!

I checked the cf docs (haven't use cachedWithin before) and
it certainly is easy to use.

However, I didn't see anything addressing the cache refresh
after any writes.  I assume you mean I could just have a select query
set up to run on the same page after a write query, without actually
outputting any info to refresh the cache?

Would that be correct?

Rick

 -Original Message-
 From: Mary Jo Sminkey [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 2:54 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
  Up till now, my db's have been relatively small
  and fairly quick when being queried.  I've never had to bother
  with indexes or other methods of optimizing queries.
 
  However, I've now got a db of about 6400 real estate properties.
 
 In addition to the indexing, have you considering just caching this query? 
 Since you say it's
typically
 much heavier on the read versus write side, that's an ideal situation to 
 cache the data. You can
 certainly do this in the database, or easily in ColdFusion as well using the 
 cachedwithin
parameter.
 All you have to do is add some code that will refresh the cache after any 
 writes (which is pretty
easy
 to do just by calling the same query with the cachedwithin set to 0). With 
 complex queries like
this
 that are retrieving entire sets of data, the performance benefit simply from 
 caching it can be
quite
 significant, and certainly faster than using LIMIT.
 
 
 
 
 
 
 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-24 Thread Mary Jo Sminkey

 However, I didn't see anything addressing the cache refresh
 after any writes.  I assume you mean I could just have a select query
 set up to run on the same page after a write query, without actually
 outputting any info to refresh the cache?
 
 Would that be correct?

Yes, exactly. You can for instance, put your query into a CFC and just set the 
cachewithin to a variable that defaults to some reasonable time for your 
application (say, 2 hours). In your normal read operations you can just call 
the method to get your dataset leaving the cache time to the default and it 
will just pull it from memory. After a write operation you would call the same 
method, passing in a setting that will refresh the cache, but you won't 
actually do anything with the dataset that is returned. 

You basically can do the same thing by saving the query into an application 
variable that you overwrite only after a write to the table(s). 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-24 Thread Mary Jo Sminkey

 After a write operation you would call the same method, passing in a 
 setting that will refresh the cache, but you won't actually do 
 anything with the dataset that is returned. 

BTW - since you don't actually *use* the dataset that is returned, this is a 
perfect example of where a cfthread can come in handy (if you are using CF8). 
You can just throw that particular refresh function into a separate thread so 
your user doesn't have to wait for it to complete. 

--- Mary Jo 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

 You can just throw that particular refresh function
 into a separate thread so your user doesn't have to wait for it to complete.

Now that sounds good... that exactly the concern that popped into my mind
when I considered the re-caching of the data.

Where would I find info on how to use a thread like that?
I don't have a clue... the cf docs?  Know of a blog about it?

Thanks for the tips!

Rick

 -Original Message-
 From: Mary Jo Sminkey [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 7:01 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
  After a write operation you would call the same method, passing in a
  setting that will refresh the cache, but you won't actually do
  anything with the dataset that is returned.
 
 BTW - since you don't actually *use* the dataset that is returned, this is a 
 perfect example of
where a
 cfthread can come in handy (if you are using CF8). You can just throw that 
 particular refresh
function
 into a separate thread so your user doesn't have to wait for it to complete.
 
 --- Mary Jo
 
 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-23 Thread Jarlath Gallagher

Hey there,

Not sure if this input will alleviate your issues more than the methods 
mentioned previously but i had a similar problem polling the gracenote db for a 
massive number of matching records. 

The method i used was to run the SQL Analyzer tool primarily to streamline my 
query but also only retrieving from the DB the records that i was displaying. 
I.E generate the record numbers you should retrieve by using the page number 
and records by page value. 

I.E. Page 1 1 - 20
 Page 2 21 - 40
 Page 3 41 - 60
 Etc.

This way you are only ever retrieving 20 records at a time. You can use SELECT 
TOP #X# FROM TABLE_Y process by building the SQL Script dynamically...

Hope this input might help just a little...
 Hi, all...
 
 Up till now, my db's have been relatively small
 and fairly quick when being queried.  I've never had to bother
 with indexes or other methods of optimizing queries.
 
 However, I've now got a db of about 6400 real estate properties.
 
 I've put together a Browse Properties page, which returns
 20 records at a time and displays pagination.
 
 The query is pretty slow.
 
 Would indexes or perhaps another approach to this query
 make it quicker?
 
 Here's the code that pulls together records from the tables
 containing the various property types...
 
 cfquery name=get_properties datasource=#dsn#
   

 select sa.street_number as prop_street_number, sa.street_name as 
 prop_street_name,
   
 sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
   
 sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as 
 prop_list_price,
   
 (select sap.photo_filename from smlc_acr_photos sap where sap.
 photo_mls_number =
 sa.mls_number limit 1) as prop_photo_filename
  
 from smlc_acr sa
 
 union   

 select sc.street_number as prop_street_number, sc.street_name as 
 prop_street_name,
   
 sc.city as prop_city, sc.public_remarks_01 as prop_public_remarks_01,
   
 sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as 
 prop_list_price,
   
 (select scp.photo_filename from smlc_com_photos scp where scp.
 photo_mls_number =
 sc.mls_number limit 1) as prop_photo_filename
  
 from smlc_com sc
 
 union

 select sl.street_number as prop_street_number, sl.street_name as 
 prop_street_name,
   
 sl.city as prop_city, sl.public_remarks_01 as prop_public_remarks_01,
   
 sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as 
 prop_list_price,
   
 (select slp.photo_filename from smlc_lot_photos slp where slp.
 photo_mls_number =
 sl.mls_number limit 1) as prop_photo_filename
  
 from smlc_lots sl
 
 union 

 select sr.street_number as prop_street_number, sr.street_name as 
 prop_street_name,
   
 sr.city as prop_city, sr.public_remarks_01 as prop_public_remarks_01,
   
 sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as 
 prop_list_price,
   (select srp.photo_filename from smlc_ren_photos srp where srp.
 photo_mls_number =
 sr.mls_number limit 1) as prop_photo_filename
  
 from smlc_ren sr
 
 union

 select sres.street_number as prop_street_number, sres.street_name as 
 prop_street_name,
   
 sres.city as prop_city, sres.public_remarks_01 as 
 prop_public_remarks_01,
   
 sres.public_remarks_02 as prop_public_remarks_02, sres.list_price as 
 prop_list_price,
   
 (select sresp.photo_filename from smlc_res_photos sresp where sresp.
 photo_mls_number =
 sres.mls_number limit 1) as prop_photo_filename
  
 from smlc_res sres
 
 /cfquery


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-23 Thread Dominic Watson

Just a note on indexes - every one of your tables should have at least one
unique constraint or index *other* than the primary key. This way, the
primary key is not what makes a row in your table unique - it just
identifies it for relationship purposes.

This helps to avoid duplicate data and helps speed up query performance as
you have found :)

HTH

Dominic


On 23/04/2008, James Holmes [EMAIL PROTECTED] wrote:

 Did you try the UNION ALL as suggested by Rizal? This might make it a
 little quicker as the DB won't have to scan for duplicates.

 On Wed, Apr 23, 2008 at 11:03 AM, Rick Faircloth
 [EMAIL PROTECTED] wrote:
  If I had set up the original db that's providing the
   data, it would have definitely been set up differently.

 --
 mxAjax / CFAjax docs and other useful articles:
 http://www.bifrost.com.au/blog/

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Yes, I'm using union all, too.

Thanks...

Rick

 -Original Message-
 From: James Holmes [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, April 22, 2008 11:18 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...

 Did you try the UNION ALL as suggested by Rizal? This might make it a
 little quicker as the DB won't have to scan for duplicates.

 On Wed, Apr 23, 2008 at 11:03 AM, Rick Faircloth
 [EMAIL PROTECTED] wrote:
  If I had set up the original db that's providing the
   data, it would have definitely been set up differently.

 --
 mxAjax / CFAjax docs and other useful articles:
 http://www.bifrost.com.au/blog/

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Hi, Jarlath, and thanks for the reply...

I thought about working directly with the SQL to limit the
number of records retrieved, but I wasn't sure how to work that
in conjunction with the paginationCFC, plus, I wasn't sure how
the limit and offset really worked, so I just decided to take
the safe route.

I checked out the MySQL docs, but didn't get enough info to understand
how it really worked with pagination.  I guess, as you said, the top
or limit or offset would have to be set dyanamically, according to
my pagination settings.

Any other thoughts on this?  (Especially good examples/tutorials of this usage?)

Thanks,

Rick



 -Original Message-
 From: Jarlath Gallagher [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, April 23, 2008 6:29 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Hey there,
 
 Not sure if this input will alleviate your issues more than the methods 
 mentioned previously but i
had a
 similar problem polling the gracenote db for a massive number of matching 
 records.
 
 The method i used was to run the SQL Analyzer tool primarily to streamline my 
 query but also only
 retrieving from the DB the records that i was displaying. I.E generate the 
 record numbers you
should
 retrieve by using the page number and records by page value.
 
 I.E. Page 1 1 - 20
  Page 2 21 - 40
  Page 3 41 - 60
  Etc.
 
 This way you are only ever retrieving 20 records at a time. You can use 
 SELECT TOP #X# FROM
TABLE_Y
 process by building the SQL Script dynamically...
 
 Hope this input might help just a little...
  Hi, all...
 
  Up till now, my db's have been relatively small
  and fairly quick when being queried.  I've never had to bother
  with indexes or other methods of optimizing queries.
 
  However, I've now got a db of about 6400 real estate properties.
 
  I've put together a Browse Properties page, which returns
  20 records at a time and displays pagination.
 
  The query is pretty slow.
 
  Would indexes or perhaps another approach to this query
  make it quicker?
 
  Here's the code that pulls together records from the tables
  containing the various property types...
 
  cfquery name=get_properties datasource=#dsn#
 
 
  select sa.street_number as prop_street_number, sa.street_name as
  prop_street_name,
 
  sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
 
  sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as
  prop_list_price,
 
  (select sap.photo_filename from smlc_acr_photos sap where sap.
  photo_mls_number =
  sa.mls_number limit 1) as prop_photo_filename
 
  from smlc_acr sa
 
  union
 
  select sc.street_number as prop_street_number, sc.street_name as
  prop_street_name,
 
  sc.city as prop_city, sc.public_remarks_01 as prop_public_remarks_01,
 
  sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as
  prop_list_price,
 
  (select scp.photo_filename from smlc_com_photos scp where scp.
  photo_mls_number =
  sc.mls_number limit 1) as prop_photo_filename
 
  from smlc_com sc
 
  union
 
  select sl.street_number as prop_street_number, sl.street_name as
  prop_street_name,
 
  sl.city as prop_city, sl.public_remarks_01 as prop_public_remarks_01,
 
  sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as
  prop_list_price,
 
  (select slp.photo_filename from smlc_lot_photos slp where slp.
  photo_mls_number =
  sl.mls_number limit 1) as prop_photo_filename
 
  from smlc_lots sl
 
  union
 
  select sr.street_number as prop_street_number, sr.street_name as
  prop_street_name,
 
  sr.city as prop_city, sr.public_remarks_01 as prop_public_remarks_01,
 
  sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as
  prop_list_price,
  (select srp.photo_filename from smlc_ren_photos srp where srp.
  photo_mls_number =
  sr.mls_number limit 1) as prop_photo_filename
 
  from smlc_ren sr
 
  union
 
  select sres.street_number as prop_street_number, sres.street_name as
  prop_street_name,
 
  sres.city as prop_city, sres.public_remarks_01 as
  prop_public_remarks_01,
 
  sres.public_remarks_02 as prop_public_remarks_02, sres.list_price as
  prop_list_price,
 
  (select sresp.photo_filename from smlc_res_photos sresp where sresp.
  photo_mls_number =
  sres.mls_number limit 1) as prop_photo_filename
 
  from smlc_res sres
 
  /cfquery
 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Well... in this case, all of the properties have a unique mls_number,
so that would work for the property tables.  I did have a normal index
on the mls_number, but I can see how unique would be better.

Thanks for the help!

Rick

 -Original Message-
 From: Dominic Watson [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, April 23, 2008 3:43 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Just a note on indexes - every one of your tables should have at least one
 unique constraint or index *other* than the primary key. This way, the
 primary key is not what makes a row in your table unique - it just
 identifies it for relationship purposes.
 
 This helps to avoid duplicate data and helps speed up query performance as
 you have found :)
 
 HTH
 
 Dominic
 
 
 On 23/04/2008, James Holmes [EMAIL PROTECTED] wrote:
 
  Did you try the UNION ALL as suggested by Rizal? This might make it a
  little quicker as the DB won't have to scan for duplicates.
 
  On Wed, Apr 23, 2008 at 11:03 AM, Rick Faircloth
  [EMAIL PROTECTED] wrote:
   If I had set up the original db that's providing the
data, it would have definitely been set up differently.
 
  --
  mxAjax / CFAjax docs and other useful articles:
  http://www.bifrost.com.au/blog/
 
 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-23 Thread Dominic Watson


 I did have a normal index on the mls_number, but I can see how unique
 would be better.


Yeh, if you can physically define what makes a row unique, do it (if you
can't define what makes a row unique, theres something wrong with the
table)! Let the database enforce that basic database rule - saves your ass
from duplicates down the line too :)

Dom


On 23/04/2008, Rick Faircloth [EMAIL PROTECTED] wrote:

 Well... in this case, all of the properties have a unique mls_number,
 so that would work for the property tables.  I did have a normal index
 on the mls_number, but I can see how unique would be better.

 Thanks for the help!

 Rick

  -Original Message-
  From: Dominic Watson [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, April 23, 2008 3:43 AM
  To: CF-Talk
  Subject: Re: Need some advice on speeding up query...
 
  Just a note on indexes - every one of your tables should have at least
 one
  unique constraint or index *other* than the primary key. This way, the
  primary key is not what makes a row in your table unique - it just
  identifies it for relationship purposes.
 
  This helps to avoid duplicate data and helps speed up query performance
 as
  you have found :)
 
  HTH
 
  Dominic
 
 
  On 23/04/2008, James Holmes [EMAIL PROTECTED] wrote:
  
   Did you try the UNION ALL as suggested by Rizal? This might make it a
   little quicker as the DB won't have to scan for duplicates.
  
   On Wed, Apr 23, 2008 at 11:03 AM, Rick Faircloth
   [EMAIL PROTECTED] wrote:
If I had set up the original db that's providing the
 data, it would have definitely been set up differently.
  
   --
   mxAjax / CFAjax docs and other useful articles:
   http://www.bifrost.com.au/blog/
  
  
 
 

 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Now's here's a new problem I haven't encountered.

How do I use a url variable to link to a property details page
when the properties are in 8 different tables?  The issue really isn't
the variable, since I can use the aliased variable property_id (once I add it 
to the query).

I know I could use
property_details.cfm?property_id=cfoutput#get_properties.property_id#/cfoutput,
however, on the details page, I would need to search all eight property tables
for the correct property id.

Is this the method I'm stuck with because all the properties aren't in a single 
table?
Pass the aliased variable property_id and search all 8 tables for it?

Perhaps I should just put them all into one big property table instead of 
keeping them
separate as the data vender has them...

Thoughts?

Thanks,

Rick

Here's the current state of my union all query with all the property tables 
finally in play:

cfquery name=get_properties datasource=#dsn#

select sa.street_number as prop_street_number, sa.street_name as 
prop_street_name,
   sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
   sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as 
prop_list_price,
   (select sap.photo_filename from smlc_acr_photos sap where 
sap.photo_mls_number =
sa.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
sa.listing_office) as
prop_listing_office
  from smlc_acr sa
 union all   
select sc.street_number as prop_street_number, sc.street_name as 
prop_street_name,
   sc.city as prop_city, sc.public_remarks_01 as prop_public_remarks_01,
   sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as 
prop_list_price,
   (select scp.photo_filename from smlc_com_photos scp where 
scp.photo_mls_number =
sc.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
sc.listing_office) as
prop_listing_office
  from smlc_com sc
 union all
select sl.street_number as prop_street_number, sl.street_name as 
prop_street_name,
   sl.city as prop_city, sl.public_remarks_01 as prop_public_remarks_01,
   sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as 
prop_list_price,
   (select slp.photo_filename from smlc_lot_photos slp where 
slp.photo_mls_number =
sl.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
sl.listing_office) as
prop_listing_office
  from smlc_lots sl
 union all 
select sr.street_number as prop_street_number, sr.street_name as 
prop_street_name,
   sr.city as prop_city, sr.public_remarks_01 as prop_public_remarks_01,
   sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as 
prop_list_price,
   (select srp.photo_filename from smlc_ren_photos srp where 
srp.photo_mls_number =
sr.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
sr.listing_office) as
prop_listing_office
  from smlc_ren sr
 union all
select sres.street_number as prop_street_number, sres.street_name as 
prop_street_name,
   sres.city as prop_city, sres.public_remarks_01 as 
prop_public_remarks_01,
   sres.public_remarks_02 as prop_public_remarks_02, sres.list_price as 
prop_list_price,
   (select sresp.photo_filename from smlc_res_photos sresp where 
sresp.photo_mls_number =
sres.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
sres.listing_office) as
prop_listing_office
  from smlc_res sres
 union all 
select smul.street_number as prop_street_number, smul.street_name as 
prop_street_name,
   smul.city as prop_city, smul.public_remarks_01 as 
prop_public_remarks_01,
   smul.public_remarks_02 as prop_public_remarks_02, smul.list_price as 
prop_list_price,
   (select smulp.photo_filename from smlc_mul_photos smulp where 
smulp.photo_mls_number =
smul.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where so.office_code = 
smul.listing_office) as
prop_listing_office
  from smlc_mul smul
 
/cfquery




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

2008-04-23 Thread Dave Watts

 How do I use a url variable to link to a property details 
 page when the properties are in 8 different tables?  The 
 issue really isn't the variable, since I can use the aliased 
 variable property_id (once I add it to the query).
 
 I know I could use
 property_details.cfm?property_id=cfoutput#get_properties.pro
 perty_id#/cfoutput,
 however, on the details page, I would need to search all 
 eight property tables for the correct property id.
 
 Is this the method I'm stuck with because all the properties 
 aren't in a single table?
 Pass the aliased variable property_id and search all 8 
 tables for it?
 
 Perhaps I should just put them all into one big property 
 table instead of keeping them separate as the data vender has 
 them...

Are all these tables identically constructed? That's what it looks like from
the query. If so, I can't imagine why the vendor would tell you to do that.
Did the vendor explain this to you? What database server are you using? How
many records are we talking about? Are the tables properly indexed?

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

Fig Leaf Training: Adobe/Google/Paperthin Certified Partners 
http://training.figleaf.com/

WebManiacs 2008: the ultimate conference for CF/Flex/AIR developers!
http://www.webmaniacsconference.com/

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-23 Thread Jon Clausen

Rick,

Based on what I can see, it appears that the tables are separated by  
property type (commercial, residential, lot, multiunit, etc.).  You  
would need to add a column in the table to identify that type, but it  
seems to me - as long as they are identical in structure as it seems -  
that merging them into a single table would make life easier in many  
ways.

Correct me if I'm wrong, but it would seem like property data would be  
low-write/high-read tables so as long as you merge them you could  
avoid the overhead of the UNION ALLs and then could  index them  
heavily(MLS#, Property Type  ZIP,etc.).  Then, depending on your DBMS  
type, you could create additional create views and  caches at the DBMS  
level to further optimize query speed.

Writes are much slower on heavily large, heavily indexed tables since  
the indexes have to be recreated or appended but I imagine, for this  
type of data, that the agents could wait a tiny bit longer for their  
entries if their records were retrieved exponentially faster.

HTH,

Jon

On Apr 23, 2008, at 10:29 PM, Rick Faircloth wrote:

 Now's here's a new problem I haven't encountered.

 How do I use a url variable to link to a property details page
 when the properties are in 8 different tables?  The issue really isn't
 the variable, since I can use the aliased variable  
 property_id (once I add it to the query).

 I know I could use
 property_details.cfm? 
 property_id=cfoutput#get_properties.property_id#/cfoutput,
 however, on the details page, I would need to search all eight  
 property tables
 for the correct property id.

 Is this the method I'm stuck with because all the properties aren't  
 in a single table?
 Pass the aliased variable property_id and search all 8 tables for  
 it?

 Perhaps I should just put them all into one big property table  
 instead of keeping them
 separate as the data vender has them...

 Thoughts?

 Thanks,

 Rick

 Here's the current state of my union all query with all the  
 property tables finally in play:

 cfquery name=get_properties datasource=#dsn#
   
select sa.street_number as prop_street_number, sa.street_name as  
 prop_street_name,
   sa.city as prop_city, sa.public_remarks_01 as  
 prop_public_remarks_01,
   sa.public_remarks_02 as prop_public_remarks_02,  
 sa.list_price as prop_list_price,
   (select sap.photo_filename from smlc_acr_photos sap where  
 sap.photo_mls_number =
 sa.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where  
 so.office_code = sa.listing_office) as
 prop_listing_office
  from smlc_acr sa
 union all
select sc.street_number as prop_street_number, sc.street_name as  
 prop_street_name,
   sc.city as prop_city, sc.public_remarks_01 as  
 prop_public_remarks_01,
   sc.public_remarks_02 as prop_public_remarks_02,  
 sc.list_price as prop_list_price,
   (select scp.photo_filename from smlc_com_photos scp where  
 scp.photo_mls_number =
 sc.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where  
 so.office_code = sc.listing_office) as
 prop_listing_office   
  from smlc_com sc
 union all
select sl.street_number as prop_street_number, sl.street_name as  
 prop_street_name,
   sl.city as prop_city, sl.public_remarks_01 as  
 prop_public_remarks_01,
   sl.public_remarks_02 as prop_public_remarks_02,  
 sl.list_price as prop_list_price,
   (select slp.photo_filename from smlc_lot_photos slp where  
 slp.photo_mls_number =
 sl.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where  
 so.office_code = sl.listing_office) as
 prop_listing_office
  from smlc_lots sl
 union all
select sr.street_number as prop_street_number, sr.street_name as  
 prop_street_name,
   sr.city as prop_city, sr.public_remarks_01 as  
 prop_public_remarks_01,
   sr.public_remarks_02 as prop_public_remarks_02,  
 sr.list_price as prop_list_price,
   (select srp.photo_filename from smlc_ren_photos srp where  
 srp.photo_mls_number =
 sr.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where  
 so.office_code = sr.listing_office) as
 prop_listing_office
  from smlc_ren sr
 union all
select sres.street_number as prop_street_number, sres.street_name  
 as prop_street_name,
   sres.city as prop_city, sres.public_remarks_01 as  
 prop_public_remarks_01,
   sres.public_remarks_02 as prop_public_remarks_02,  
 sres.list_price as prop_list_price,
   (select sresp.photo_filename from smlc_res_photos sresp  
 where sresp.photo_mls_number =
 sres.mls_number limit 1) as prop_photo_filename,
   (select so.office_name from smlc_off so where  
 so.office_code = sres.listing_office) as
 prop_listing_office
  from smlc_res

RE: Need some advice on speeding up query...

Unfortunately, the database vendor has been unreachable
during the process of trying to figure out how the constructed
their database, why they did it that way, and why their schema
for the tables doesn't match the delimited data files I had to import.

There are about 8,000 properties currently.

No, none of the tables are alike.  They do share some fields, but there
is a lot different about each one.  I'm using MySQL 5.  I've got proper
indexing on the tables now (as far as I know) based on other's advice,
which has greatly sped up my queries.

It looks as if I'm going to have to run through queries on each property table
to see if I can find a match to the property id

- query the acreage table, if no match,
- query the lot table, if no match, 
- query the rental table, etc.

until a match is found.

Really can't see another way of doing it with the properties spread out
between tables.

Is there some conditional code I can use in a single query that allows me to
search for a property id in multiple tables?

Thanks for the feedback.

Rick

 -Original Message-
 From: Dave Watts [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 12:06 AM
 To: CF-Talk
 Subject: RE: Need some advice on speeding up query...
 
  How do I use a url variable to link to a property details
  page when the properties are in 8 different tables?  The
  issue really isn't the variable, since I can use the aliased
  variable property_id (once I add it to the query).
 
  I know I could use
  property_details.cfm?property_id=cfoutput#get_properties.pro
  perty_id#/cfoutput,
  however, on the details page, I would need to search all
  eight property tables for the correct property id.
 
  Is this the method I'm stuck with because all the properties
  aren't in a single table?
  Pass the aliased variable property_id and search all 8
  tables for it?
 
  Perhaps I should just put them all into one big property
  table instead of keeping them separate as the data vender has
  them...
 
 Are all these tables identically constructed? That's what it looks like from
 the query. If so, I can't imagine why the vendor would tell you to do that.
 Did the vendor explain this to you? What database server are you using? How
 many records are we talking about? Are the tables properly indexed?
 
 Dave Watts, CTO, Fig Leaf Software
 http://www.figleaf.com/
 



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

Yes, the tables are separated by property type, but no, they don't have
the same fields.  They're all different.

And, yes, these would be, by far, heavy on the read side and light on the write 
side.

I guess I could create one massive table, encompassing all the various fields 
for all
the property types.

I'd have to check into that.  Once I get this process of importing delimited 
text data
into my MySQL db tables as temp tables, then importing that data into a 
permanent table
with a auto-incrementing, primary key property_id field added *and* processing 
batches
of photos by reading their filenames in the various directories, parsing the 
MSL number
for each photo, and storing that in a corresponding photo table... Once all 
that is complete, 
as I've now done it all manually, I have to automate the process, because all 
the data
and photos have to be updated daily.  I pretty much worked that out, too.

Scheduled tasks will take care of the automation.

And once I get this all figured out and implemented for the Savannah MLS's 
data, I get to
add in the Hinesville MLS's data...

I should be getting paid more...

Rick



 -Original Message-
 From: Jon Clausen [mailto:[EMAIL PROTECTED]
 Sent: Thursday, April 24, 2008 12:28 AM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Rick,
 
 Based on what I can see, it appears that the tables are separated by
 property type (commercial, residential, lot, multiunit, etc.).  You
 would need to add a column in the table to identify that type, but it
 seems to me - as long as they are identical in structure as it seems -
 that merging them into a single table would make life easier in many
 ways.
 
 Correct me if I'm wrong, but it would seem like property data would be
 low-write/high-read tables so as long as you merge them you could
 avoid the overhead of the UNION ALLs and then could  index them
 heavily(MLS#, Property Type  ZIP,etc.).  Then, depending on your DBMS
 type, you could create additional create views and  caches at the DBMS
 level to further optimize query speed.
 
 Writes are much slower on heavily large, heavily indexed tables since
 the indexes have to be recreated or appended but I imagine, for this
 type of data, that the agents could wait a tiny bit longer for their
 entries if their records were retrieved exponentially faster.
 
 HTH,
 
 Jon
 
 On Apr 23, 2008, at 10:29 PM, Rick Faircloth wrote:
 
  Now's here's a new problem I haven't encountered.
 
  How do I use a url variable to link to a property details page
  when the properties are in 8 different tables?  The issue really isn't
  the variable, since I can use the aliased variable
  property_id (once I add it to the query).
 
  I know I could use
  property_details.cfm?
  property_id=cfoutput#get_properties.property_id#/cfoutput,
  however, on the details page, I would need to search all eight
  property tables
  for the correct property id.
 
  Is this the method I'm stuck with because all the properties aren't
  in a single table?
  Pass the aliased variable property_id and search all 8 tables for
  it?
 
  Perhaps I should just put them all into one big property table
  instead of keeping them
  separate as the data vender has them...
 
  Thoughts?
 
  Thanks,
 
  Rick
 
  Here's the current state of my union all query with all the
  property tables finally in play:
 
  cfquery name=get_properties datasource=#dsn#
 
 select sa.street_number as prop_street_number, sa.street_name as
  prop_street_name,
sa.city as prop_city, sa.public_remarks_01 as
  prop_public_remarks_01,
sa.public_remarks_02 as prop_public_remarks_02,
  sa.list_price as prop_list_price,
(select sap.photo_filename from smlc_acr_photos sap where
  sap.photo_mls_number =
  sa.mls_number limit 1) as prop_photo_filename,
(select so.office_name from smlc_off so where
  so.office_code = sa.listing_office) as
  prop_listing_office
   from smlc_acr sa
  union all
 select sc.street_number as prop_street_number, sc.street_name as
  prop_street_name,
sc.city as prop_city, sc.public_remarks_01 as
  prop_public_remarks_01,
sc.public_remarks_02 as prop_public_remarks_02,
  sc.list_price as prop_list_price,
(select scp.photo_filename from smlc_com_photos scp where
  scp.photo_mls_number =
  sc.mls_number limit 1) as prop_photo_filename,
(select so.office_name from smlc_off so where
  so.office_code = sc.listing_office) as
  prop_listing_office
   from smlc_com sc
  union all
 select sl.street_number as prop_street_number, sl.street_name as
  prop_street_name,
sl.city as prop_city, sl.public_remarks_01 as
  prop_public_remarks_01,
sl.public_remarks_02 as prop_public_remarks_02,
  sl.list_price as prop_list_price,
(select slp.photo_filename from smlc_lot_photos slp where
  slp.photo_mls_number =
  sl.mls_number limit 1) as prop_photo_filename

Need some advice on speeding up query...

2008-04-22 Thread Rick Faircloth

Hi, all...

Up till now, my db's have been relatively small
and fairly quick when being queried.  I've never had to bother
with indexes or other methods of optimizing queries.

However, I've now got a db of about 6400 real estate properties.

I've put together a Browse Properties page, which returns
20 records at a time and displays pagination.

The query is pretty slow.

Would indexes or perhaps another approach to this query
make it quicker?

Here's the code that pulls together records from the tables
containing the various property types...

cfquery name=get_properties datasource=#dsn#

select sa.street_number as prop_street_number, sa.street_name as 
prop_street_name,
   sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
   sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as 
prop_list_price,
   (select sap.photo_filename from smlc_acr_photos sap where 
sap.photo_mls_number =
sa.mls_number limit 1) as prop_photo_filename
  from smlc_acr sa
 union   
select sc.street_number as prop_street_number, sc.street_name as 
prop_street_name,
   sc.city as prop_city, sc.public_remarks_01 as prop_public_remarks_01,
   sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as 
prop_list_price,
   (select scp.photo_filename from smlc_com_photos scp where 
scp.photo_mls_number =
sc.mls_number limit 1) as prop_photo_filename
  from smlc_com sc
 union
select sl.street_number as prop_street_number, sl.street_name as 
prop_street_name,
   sl.city as prop_city, sl.public_remarks_01 as prop_public_remarks_01,
   sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as 
prop_list_price,
   (select slp.photo_filename from smlc_lot_photos slp where 
slp.photo_mls_number =
sl.mls_number limit 1) as prop_photo_filename
  from smlc_lots sl
 union 
select sr.street_number as prop_street_number, sr.street_name as 
prop_street_name,
   sr.city as prop_city, sr.public_remarks_01 as prop_public_remarks_01,
   sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as 
prop_list_price,
(select srp.photo_filename from smlc_ren_photos srp where 
srp.photo_mls_number =
sr.mls_number limit 1) as prop_photo_filename
  from smlc_ren sr
 union
select sres.street_number as prop_street_number, sres.street_name as 
prop_street_name,
   sres.city as prop_city, sres.public_remarks_01 as 
prop_public_remarks_01,
   sres.public_remarks_02 as prop_public_remarks_02, sres.list_price as 
prop_list_price,
   (select sresp.photo_filename from smlc_res_photos sresp where 
sresp.photo_mls_number =
sres.mls_number limit 1) as prop_photo_filename
  from smlc_res sres
  
/cfquery


~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-22 Thread Rizal Firmansyah

Hi Rick,
index will speed up the query if used correctly.

I think you can put index on these tables
smlc_acr_photos - photo_mls_number
smlc_com_photos  - photo_mls_number
smlc_lot_photos  - photo_mls_number
smlc_ren_photos  - photo_mls_number
smlc_res_photos  - photo_mls_number

Also if the content of smlc_acr, smlc_com, 
smlc_lots, smlc_ren, smlc_res are different
try using union all instead of union

Rizal

At 06:21 AM 4/23/2008, you wrote:
Hi, all...

Up till now, my db's have been relatively small
and fairly quick when being queried. I've never had to bother
with indexes or other methods of optimizing queries.

However, I've now got a db of about 6400 real estate properties.

I've put together a Browse Properties page, which returns
20 records at a time and displays pagination.

The query is pretty slow.

Would indexes or perhaps another approach to this query
make it quicker?

Here's the code that pulls together records from the tables
containing the various property types...

cfquery name=get_properties datasource=#dsn#

select sa.street_number as 
 prop_street_number, sa.street_name as prop_street_name,
   sa.city as prop_city, 
 sa.public_remarks_01 as prop_public_remarks_01,
   sa.public_remarks_02 as 
 prop_public_remarks_02, sa.list_price as prop_list_price,
   (select sap.photo_filename from 
 smlc_acr_photos sap where sap.photo_mls_number =
sa.mls_number limit 1) as prop_photo_filename
  from smlc_acr sa
 union
select sc.street_number as 
 prop_street_number, sc.street_name as prop_street_name,
   sc.city as prop_city, 
 sc.public_remarks_01 as prop_public_remarks_01,
   sc.public_remarks_02 as 
 prop_public_remarks_02, sc.list_price as prop_list_price,
   (select scp.photo_filename from 
 smlc_com_photos scp where scp.photo_mls_number =
sc.mls_number limit 1) as prop_photo_filename
  from smlc_com sc
 union
select sl.street_number as 
 prop_street_number, sl.street_name as prop_street_name,
   sl.city as prop_city, 
 sl.public_remarks_01 as prop_public_remarks_01,
   sl.public_remarks_02 as 
 prop_public_remarks_02, sl.list_price as prop_list_price,
   (select slp.photo_filename from 
 smlc_lot_photos slp where slp.photo_mls_number =
sl.mls_number limit 1) as prop_photo_filename
  from smlc_lots sl
 union
select sr.street_number as 
 prop_street_number, sr.street_name as prop_street_name,
   sr.city as prop_city, 
 sr.public_remarks_01 as prop_public_remarks_01,
   sr.public_remarks_02 as 
 prop_public_remarks_02, sr.list_price as prop_list_price,
(select srp.photo_filename from 
 smlc_ren_photos srp where srp.photo_mls_number =
sr.mls_number limit 1) as prop_photo_filename
  from smlc_ren sr
 union
select sres.street_number as 
 prop_street_number, sres.street_name as prop_street_name,
   sres.city as prop_city, 
 sres.public_remarks_01 as prop_public_remarks_01,
   sres.public_remarks_02 as 
 prop_public_remarks_02, sres.list_price as prop_list_price,
   (select sresp.photo_filename from 
 smlc_res_photos sresp where sresp.photo_mls_number =
sres.mls_number limit 1) as prop_photo_filename
  from smlc_res sres

/cfquery




~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

2008-04-22 Thread Rick Faircloth

Wow, Rizal...

I had my doubts about how much putting indexes
on just the photo_mls_number fields in the photo tables
would help, but the speed is greatly improved!

I figured with the complexity of the query that just those
indexes wouldn't make much difference, but they did!

Thanks!

Rick

 -Original Message-
 From: Rizal Firmansyah [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, April 22, 2008 9:18 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Hi Rick,
 index will speed up the query if used correctly.
 
 I think you can put index on these tables
 smlc_acr_photos - photo_mls_number
 smlc_com_photos  - photo_mls_number
 smlc_lot_photos  - photo_mls_number
 smlc_ren_photos  - photo_mls_number
 smlc_res_photos  - photo_mls_number
 
 Also if the content of smlc_acr, smlc_com,
 smlc_lots, smlc_ren, smlc_res are different
 try using union all instead of union
 
 Rizal
 
 At 06:21 AM 4/23/2008, you wrote:
 Hi, all...
 
 Up till now, my db's have been relatively small
 and fairly quick when being queried. I've never had to bother
 with indexes or other methods of optimizing queries.
 
 However, I've now got a db of about 6400 real estate properties.
 
 I've put together a Browse Properties page, which returns
 20 records at a time and displays pagination.
 
 The query is pretty slow.
 
 Would indexes or perhaps another approach to this query
 make it quicker?
 
 Here's the code that pulls together records from the tables
 containing the various property types...
 
 cfquery name=get_properties datasource=#dsn#
 
 select sa.street_number as
  prop_street_number, sa.street_name as prop_street_name,
sa.city as prop_city,
  sa.public_remarks_01 as prop_public_remarks_01,
sa.public_remarks_02 as
  prop_public_remarks_02, sa.list_price as prop_list_price,
(select sap.photo_filename from
  smlc_acr_photos sap where sap.photo_mls_number =
 sa.mls_number limit 1) as prop_photo_filename
   from smlc_acr sa
  union
 select sc.street_number as
  prop_street_number, sc.street_name as prop_street_name,
sc.city as prop_city,
  sc.public_remarks_01 as prop_public_remarks_01,
sc.public_remarks_02 as
  prop_public_remarks_02, sc.list_price as prop_list_price,
(select scp.photo_filename from
  smlc_com_photos scp where scp.photo_mls_number =
 sc.mls_number limit 1) as prop_photo_filename
   from smlc_com sc
  union
 select sl.street_number as
  prop_street_number, sl.street_name as prop_street_name,
sl.city as prop_city,
  sl.public_remarks_01 as prop_public_remarks_01,
sl.public_remarks_02 as
  prop_public_remarks_02, sl.list_price as prop_list_price,
(select slp.photo_filename from
  smlc_lot_photos slp where slp.photo_mls_number =
 sl.mls_number limit 1) as prop_photo_filename
   from smlc_lots sl
  union
 select sr.street_number as
  prop_street_number, sr.street_name as prop_street_name,
sr.city as prop_city,
  sr.public_remarks_01 as prop_public_remarks_01,
sr.public_remarks_02 as
  prop_public_remarks_02, sr.list_price as prop_list_price,
 (select srp.photo_filename from
  smlc_ren_photos srp where srp.photo_mls_number =
 sr.mls_number limit 1) as prop_photo_filename
   from smlc_ren sr
  union
 select sres.street_number as
  prop_street_number, sres.street_name as prop_street_name,
sres.city as prop_city,
  sres.public_remarks_01 as prop_public_remarks_01,
sres.public_remarks_02 as
  prop_public_remarks_02, sres.list_price as prop_list_price,
(select sresp.photo_filename from
  smlc_res_photos sresp where sresp.photo_mls_number =
 sres.mls_number limit 1) as prop_photo_filename
   from smlc_res sres
 
 /cfquery
 
 
 
 
 

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-22 Thread James Holmes

Well, apart from a DB redesign so that you can do 1 query instead of a
union of 5, lets look at the first query as an example:

select sa.street_number as prop_street_number, sa.street_name as
prop_street_name,
  sa.city as prop_city, sa.public_remarks_01 as prop_public_remarks_01,
  sa.public_remarks_02 as prop_public_remarks_02,
sa.list_price as prop_list_price,
  (select sap.photo_filename from smlc_acr_photos sap where
sap.photo_mls_number =
sa.mls_number limit 1) as prop_photo_filename

Since you're joining smlc_acr_photos.photo_mls_number to
smlc_acr.mls_number, put an index on each column. Hopefully
smlc_acr.mls_number is a primary key and is already indexed. Repeat
for the other tables.

On Wed, Apr 23, 2008 at 7:21 AM, Rick Faircloth
[EMAIL PROTECTED] wrote:
 Hi, all...

  Up till now, my db's have been relatively small
  and fairly quick when being queried.  I've never had to bother
  with indexes or other methods of optimizing queries.

  However, I've now got a db of about 6400 real estate properties.

  I've put together a Browse Properties page, which returns
  20 records at a time and displays pagination.

  The query is pretty slow.

  Would indexes or perhaps another approach to this query
  make it quicker?

  Here's the code that pulls together records from the tables
  containing the various property types...

  cfquery name=get_properties datasource=#dsn#

 select sa.street_number as prop_street_number, sa.street_name as 
 prop_street_name,
sa.city as prop_city, sa.public_remarks_01 as 
 prop_public_remarks_01,
sa.public_remarks_02 as prop_public_remarks_02, sa.list_price as 
 prop_list_price,
(select sap.photo_filename from smlc_acr_photos sap where 
 sap.photo_mls_number =
  sa.mls_number limit 1) as prop_photo_filename
   from smlc_acr sa
  union
 select sc.street_number as prop_street_number, sc.street_name as 
 prop_street_name,
sc.city as prop_city, sc.public_remarks_01 as 
 prop_public_remarks_01,
sc.public_remarks_02 as prop_public_remarks_02, sc.list_price as 
 prop_list_price,
(select scp.photo_filename from smlc_com_photos scp where 
 scp.photo_mls_number =
  sc.mls_number limit 1) as prop_photo_filename
   from smlc_com sc
  union
 select sl.street_number as prop_street_number, sl.street_name as 
 prop_street_name,
sl.city as prop_city, sl.public_remarks_01 as 
 prop_public_remarks_01,
sl.public_remarks_02 as prop_public_remarks_02, sl.list_price as 
 prop_list_price,
(select slp.photo_filename from smlc_lot_photos slp where 
 slp.photo_mls_number =
  sl.mls_number limit 1) as prop_photo_filename
   from smlc_lots sl
  union
 select sr.street_number as prop_street_number, sr.street_name as 
 prop_street_name,
sr.city as prop_city, sr.public_remarks_01 as 
 prop_public_remarks_01,
sr.public_remarks_02 as prop_public_remarks_02, sr.list_price as 
 prop_list_price,
 (select srp.photo_filename from smlc_ren_photos srp where 
 srp.photo_mls_number =
  sr.mls_number limit 1) as prop_photo_filename
   from smlc_ren sr
  union
 select sres.street_number as prop_street_number, sres.street_name as 
 prop_street_name,
sres.city as prop_city, sres.public_remarks_01 as 
 prop_public_remarks_01,
sres.public_remarks_02 as prop_public_remarks_02, sres.list_price 
 as prop_list_price,
(select sresp.photo_filename from smlc_res_photos sresp where 
 sresp.photo_mls_number =
  sres.mls_number limit 1) as prop_photo_filename
   from smlc_res sres

  /cfquery


  

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

RE: Need some advice on speeding up query...

2008-04-22 Thread Rick Faircloth

If I had set up the original db that's providing the
data, it would have definitely been set up differently.

However, I'm pulling data from a data provider and inserting
that in MySQL.  I'm not sure how the tables could be set up much
differently since, while they do have data in common, all the tables
have quite a few differences because of the different property types.

Unfortunately, the data vendor didn't use the mls number as a primary key
for their tables (there's no keys specified, actually), so I just added
a property_id field as an auto-incrementing primary key to each table
when I transferred the data.

Please advise if I'm missing something helpful!

Anyway, as far as indexing the mls_number fields in the property tables
(non-photo) tables... I did that and it did shave another 1-2 seconds
off the time it takes for the results to appear, down from 3-4 seconds, so
that was a good move!

I've got to get this site live asap, so redesigning the db at this point
isn't possible.  The performance of the query for this part is good, so with
the indexing, I don't think I'll have any performance problems with the
search page, either.

But again, if you've got any more ideas, let me know!

Thanks for the help!

Rick




 -Original Message-
 From: James Holmes [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, April 22, 2008 9:27 PM
 To: CF-Talk
 Subject: Re: Need some advice on speeding up query...
 
 Well, apart from a DB redesign so that you can do 1 query instead of a
 union of 5, lets look at the first query as an example:
 
 select sa.street_number as prop_street_number, sa.street_name as
 prop_street_name,
   sa.city as prop_city, sa.public_remarks_01 as 
 prop_public_remarks_01,
   sa.public_remarks_02 as prop_public_remarks_02,
 sa.list_price as prop_list_price,
   (select sap.photo_filename from smlc_acr_photos sap where
 sap.photo_mls_number =
 sa.mls_number limit 1) as prop_photo_filename
 
 Since you're joining smlc_acr_photos.photo_mls_number to
 smlc_acr.mls_number, put an index on each column. Hopefully
 smlc_acr.mls_number is a primary key and is already indexed. Repeat
 for the other tables.



~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

Re: Need some advice on speeding up query...

2008-04-22 Thread James Holmes

Did you try the UNION ALL as suggested by Rizal? This might make it a
little quicker as the DB won't have to scan for duplicates.

On Wed, Apr 23, 2008 at 11:03 AM, Rick Faircloth
[EMAIL PROTECTED] wrote:
 If I had set up the original db that's providing the
  data, it would have definitely been set up differently.

-- 
mxAjax / CFAjax docs and other useful articles:
http://www.bifrost.com.au/blog/

~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k

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

CF and Legacy Systems... Need Some Advice/Help

Good morning all. I have a client that has a CF based website (built by
myself around 5 years ago) and a SCO Unix Open Server backend. It's a legacy
system with software that's been heavily modified over the last 20-25 years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and to
get with 21st century. They wish to add a live inventory and live credit
card processing feature to their website.

Though I have done this type of work in the past, I have never worked with a
legacy system to complete this. And, to complicate matters, the software
technicians that administer the SCO Unix Open Server DO NOT wish to use a
HTTP protocol to communicate between the two servers. They tell me that they
wish to communicate to my CF server via the FTP protocol.

A basic interaction would go something like this. A customer would attempt
to place an item in their shopping cart. Before the item is added to the
cart an inventory check is made. A CF process would write an XML file and
FTP it to the remote SCO Unix Open Server. The remote server would then
respond and push a response file back to the CF server. The CF server would
process the file and determine a Yes/No answer and either add the item to
the cart or display a message that the item is out of stock to the customer.

I was wondering, can this even be done with CF and the FTP protocol instead
of using HTTP? With HTTP, this would be relatively easy with the cfhttp
tag. With FTP, I am not sure how the communication would work. :(

In my mind, even if I used the CF Event Gateway to monitor a predefined
directory for FTP traffic... I cannot figure out how to parse the FTP file
and send the response back to the right shopping cart.

Does that make sense? Any help anyone could provide me would be much
appreciated.

Regards, Che

~|
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:268794
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Dan G. Switzer, II

Che,

While it could be done, using FTP is going to be extremely sluggish--unless
you can manage to keep the FTP session open all day. 

If you have to log in for each request, you're going to end up adding a good
second or two to the entire process just for the FTP authentication
operations. That's going to seem like forever to a customer on their
website.

If the client insists on FTP, I'd probably look into writing/finding an
application that would monitor a specific folder and push any new files to
the FTP server--something that would run as a service and would maintain the
FTP session.

That way you'd simply write a file to one folder, it would be pushed to the
SCO server and then when it's done it would push a result back to another
folder on your server.

It still might be too sluggish, but it would seem like the best method for
managing this problem.

-Dan


-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 8:32 AM
To: CF-Talk
Subject: CF and Legacy Systems... Need Some Advice/Help

Good morning all. I have a client that has a CF based website (built by
myself around 5 years ago) and a SCO Unix Open Server backend. It's a
legacy
system with software that's been heavily modified over the last 20-25
years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and to
get with 21st century. They wish to add a live inventory and live credit
card processing feature to their website.

Though I have done this type of work in the past, I have never worked with
a
legacy system to complete this. And, to complicate matters, the software
technicians that administer the SCO Unix Open Server DO NOT wish to use a
HTTP protocol to communicate between the two servers. They tell me that
they
wish to communicate to my CF server via the FTP protocol.

A basic interaction would go something like this. A customer would attempt
to place an item in their shopping cart. Before the item is added to the
cart an inventory check is made. A CF process would write an XML file and
FTP it to the remote SCO Unix Open Server. The remote server would then
respond and push a response file back to the CF server. The CF server would
process the file and determine a Yes/No answer and either add the item to
the cart or display a message that the item is out of stock to the
customer.

I was wondering, can this even be done with CF and the FTP protocol instead
of using HTTP? With HTTP, this would be relatively easy with the cfhttp
tag. With FTP, I am not sure how the communication would work. :(

In my mind, even if I used the CF Event Gateway to monitor a predefined
directory for FTP traffic... I cannot figure out how to parse the FTP file
and send the response back to the right shopping cart.

Does that make sense? Any help anyone could provide me would be much
appreciated.

Regards, Che




~|
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:268800
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Tom Chiverton

On Tuesday 06 Feb 2007, Che Vilnonis wrote:
I was wondering, can this even be done with CF and the FTP protocol instead
of using HTTP? With HTTP, this would be relatively easy with the cfhttp
tag. With FTP, I am not sure how the communication would work. :(

Yeah, you can do it.
If you have a version of CF that can use the event gateway / directory watcher
it may even not be utterly horrible.

directory for FTP traffic... I cannot figure out how to parse the FTP file
and send the response back to the right shopping cart.

You'd have to have the response XML include some sort of user or session
token.

The end result will be a unresponsive GUI that hangs around a lot
saying 'please wait... adding item to cart' and similar. Eww. But you know
that :-)

--
Tom Chiverton
Helping to authoritatively scale six-generation infrastructures

This email is sent for and on behalf of Halliwells LLP.

Halliwells LLP is a limited liability partnership registered in England and
Wales under registered number OC307980 whose registered office address is at St
James's Court Brown Street Manchester M2 2JF. A list of members is available
for inspection at the registered office. Any reference to a partner in relation
to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law
Society.

CONFIDENTIALITY

This email is intended only for the use of the addressee named above and may be
confidential or legally privileged. If you are not the addressee you must not
read it and must not use any information contained in nor copy it nor inform
any person other than Halliwells LLP or the addressee of its existence or
contents. If you have received this email in error please delete it and notify
Halliwells LLP IT Department on 0870 365 8008.

For more information about Halliwells LLP visit www.halliwells.com.

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

RE: CF and Legacy Systems... Need Some Advice/Help

Thanks Dan. My thoughts exactly. In my mind, the SCO software technicians
simply don't want to add a webserver because of the increased security
issues that would arise. And yes, it *would* be terribly slow.

Che

-Original Message-
From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 9:09 AM
To: CF-Talk
Subject: RE: CF and Legacy Systems... Need Some Advice/Help

Che,

While it could be done, using FTP is going to be extremely sluggish--unless
you can manage to keep the FTP session open all day. 

If you have to log in for each request, you're going to end up adding a good
second or two to the entire process just for the FTP authentication
operations. That's going to seem like forever to a customer on their
website.

If the client insists on FTP, I'd probably look into writing/finding an
application that would monitor a specific folder and push any new files to
the FTP server--something that would run as a service and would maintain the
FTP session.

That way you'd simply write a file to one folder, it would be pushed to the
SCO server and then when it's done it would push a result back to another
folder on your server.

It still might be too sluggish, but it would seem like the best method for
managing this problem.

-Dan

-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 8:32 AM
To: CF-Talk
Subject: CF and Legacy Systems... Need Some Advice/Help

Good morning all. I have a client that has a CF based website (built by 
myself around 5 years ago) and a SCO Unix Open Server backend. It's a 
legacy system with software that's been heavily modified over the last 
20-25 years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and 
to get with 21st century. They wish to add a live inventory and live 
credit card processing feature to their website.

Though I have done this type of work in the past, I have never worked 
with a legacy system to complete this. And, to complicate matters, the 
software technicians that administer the SCO Unix Open Server DO NOT 
wish to use a HTTP protocol to communicate between the two servers. 
They tell me that they
wish to communicate to my CF server via the FTP protocol.

A basic interaction would go something like this. A customer would 
attempt to place an item in their shopping cart. Before the item is 
added to the cart an inventory check is made. A CF process would 
write an XML file and FTP it to the remote SCO Unix Open Server. The 
remote server would then respond and push a response file back to the 
CF server. The CF server would process the file and determine a Yes/No 
answer and either add the item to the cart or display a message that 
the item is out of stock to the customer.

I was wondering, can this even be done with CF and the FTP protocol 
instead of using HTTP? With HTTP, this would be relatively easy with 
the cfhttp tag. With FTP, I am not sure how the communication would 
work. :(

In my mind, even if I used the CF Event Gateway to monitor a predefined 
directory for FTP traffic... I cannot figure out how to parse the FTP 
file and send the response back to the right shopping cart.

Does that make sense? Any help anyone could provide me would be much 
appreciated.

Regards, Che

~|
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:268803
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Jochem van Dieten

Che Vilnonis wrote:
Good morning all. I have a client that has a CF based website (built by
myself around 5 years ago) and a SCO Unix Open Server backend. It's a legacy
system with software that's been heavily modified over the last 20-25 years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and to
get with 21st century. They wish to add a live inventory and live credit
card processing feature to their website.

That would not be my preferred option.

I was wondering, can this even be done with CF and the FTP protocol instead
of using HTTP?

Yes. But the question is, what response time can the software
technicians guarantee from the moment you start your FTP transaction to
the moment they complete theirs? Will they guarantee a sub-second
response time (which an interactive website needs).

It is probably easier not to use an event gateway at all and just FTP to
them, sleep for a second and parse the result (because they are
guaranteeing sub-second response times, right?).

Jochem

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

RE: CF and Legacy Systems... Need Some Advice/Help

Yeah... what Tom said... Eww. Now, I have to go do some convincing.

-Original Message-
From: Tom Chiverton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 9:10 AM
To: CF-Talk
Subject: Re: CF and Legacy Systems... Need Some Advice/Help

On Tuesday 06 Feb 2007, Che Vilnonis wrote:
 I was wondering, can this even be done with CF and the FTP protocol 
 instead of using HTTP? With HTTP, this would be relatively easy with 
 the cfhttp tag. With FTP, I am not sure how the communication would 
 work. :(

Yeah, you can do it.
If you have a version of CF that can use the event gateway / directory
watcher 
it may even not be utterly horrible.

 directory for FTP traffic... I cannot figure out how to parse the FTP 
 file and send the response back to the right shopping cart.

You'd have to have the response XML include some sort of user or session 
token.

The end result will be a unresponsive GUI that hangs around a lot 
saying 'please wait... adding item to cart' and similar. Eww. But you know 
that :-)

-- 
Tom Chiverton
Helping to authoritatively scale six-generation infrastructures

This email is sent for and on behalf of Halliwells LLP.

Halliwells LLP is a limited liability partnership registered in England and
Wales under registered number OC307980 whose registered office address is at
St James's Court Brown Street Manchester M2 2JF.  A list of members is
available for inspection at the registered office. Any reference to a
partner in relation to Halliwells LLP means a member of Halliwells LLP.
Regulated by the Law Society.

CONFIDENTIALITY

This email is intended only for the use of the addressee named above and may
be confidential or legally privileged.  If you are not the addressee you
must not read it and must not use any information contained in nor copy it
nor inform any person other than Halliwells LLP or the addressee of its
existence or contents.  If you have received this email in error please
delete it and notify Halliwells LLP IT Department on 0870 365 8008.

For more information about Halliwells LLP visit www.halliwells.com.

~|
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:268805
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Peterson, Chris

Why not just look into an ODBC connection to the database on the legacy
system?  It may not be the best thing in the world, but if you setup
permissions properly for the connecting user you shouldn't be exposed to
anything bad.  Maybe the inventory check can go over ODBC and the actual
order can be an FTP'd XML file?

Chris 

-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 9:16 AM
To: CF-Talk
Subject: Re: CF and Legacy Systems... Need Some Advice/Help

Che Vilnonis wrote:
 Good morning all. I have a client that has a CF based website (built
by
 myself around 5 years ago) and a SCO Unix Open Server backend. It's a
legacy
 system with software that's been heavily modified over the last 20-25
years.
 Neither system communicates with the other.
 
 The client is looking to streamline the order fulfillment process and
to
 get with 21st century. They wish to add a live inventory and live
credit
 card processing feature to their website.
 
 Though I have done this type of work in the past, I have never worked
with a
 legacy system to complete this. And, to complicate matters, the
software
 technicians that administer the SCO Unix Open Server DO NOT wish to
use a
 HTTP protocol to communicate between the two servers. They tell me
that they
 wish to communicate to my CF server via the FTP protocol.

That would not be my preferred option.


 A basic interaction would go something like this. A customer would
attempt
 to place an item in their shopping cart. Before the item is added to
the
 cart an inventory check is made. A CF process would write an XML
file and
 FTP it to the remote SCO Unix Open Server. The remote server would
then
 respond and push a response file back to the CF server. The CF server
would
 process the file and determine a Yes/No answer and either add the item
to
 the cart or display a message that the item is out of stock to the
customer.
 
 I was wondering, can this even be done with CF and the FTP protocol
instead
 of using HTTP?

Yes. But the question is, what response time can the software 
technicians guarantee from the moment you start your FTP transaction to 
the moment they complete theirs? Will they guarantee a sub-second 
response time (which an interactive website needs).


 In my mind, even if I used the CF Event Gateway to monitor a
predefined
 directory for FTP traffic... I cannot figure out how to parse the FTP
file
 and send the response back to the right shopping cart.

It is probably easier not to use an event gateway at all and just FTP to

them, sleep for a second and parse the result (because they are 
guaranteeing sub-second response times, right?).

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:268806
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Cutter (CFRelated)

Che,

That is unfortunate, and terribly short sighted on their part. It is 
actually fairly easy to limit port 80 traffic to specific IP addresses, 
thereby limiting communication to their server only from the webserver 
itself. They could even set it up so that the web traffic used a 
non-standard port, to which your server requests would specify in the 
http requests. The security issue is valid, but they aren't thinking 
outside of the box, which will potentially cost them a worthwhile 
application.

Cutter
_
http://blog.cutterscrossing.com

Che Vilnonis wrote:
 Thanks Dan. My thoughts exactly. In my mind, the SCO software technicians
 simply don't want to add a webserver because of the increased security
 issues that would arise. And yes, it *would* be terribly slow.
 
 Che
 
 -Original Message-
 From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, February 06, 2007 9:09 AM
 To: CF-Talk
 Subject: RE: CF and Legacy Systems... Need Some Advice/Help
 
 
 Che,
 
 While it could be done, using FTP is going to be extremely sluggish--unless
 you can manage to keep the FTP session open all day. 
 
 If you have to log in for each request, you're going to end up adding a good
 second or two to the entire process just for the FTP authentication
 operations. That's going to seem like forever to a customer on their
 website.
 
 If the client insists on FTP, I'd probably look into writing/finding an
 application that would monitor a specific folder and push any new files to
 the FTP server--something that would run as a service and would maintain the
 FTP session.
 
 That way you'd simply write a file to one folder, it would be pushed to the
 SCO server and then when it's done it would push a result back to another
 folder on your server.
 
 It still might be too sluggish, but it would seem like the best method for
 managing this problem.
 
 -Dan
 
 
 
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 8:32 AM
To: CF-Talk
Subject: CF and Legacy Systems... Need Some Advice/Help

Good morning all. I have a client that has a CF based website (built by 
myself around 5 years ago) and a SCO Unix Open Server backend. It's a 
legacy system with software that's been heavily modified over the last 
20-25 years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and 
to get with 21st century. They wish to add a live inventory and live 
credit card processing feature to their website.

Though I have done this type of work in the past, I have never worked 
with a legacy system to complete this. And, to complicate matters, the 
software technicians that administer the SCO Unix Open Server DO NOT 
wish to use a HTTP protocol to communicate between the two servers. 
They tell me that they
wish to communicate to my CF server via the FTP protocol.

A basic interaction would go something like this. A customer would 
attempt to place an item in their shopping cart. Before the item is 
added to the cart an inventory check is made. A CF process would 
write an XML file and FTP it to the remote SCO Unix Open Server. The 
remote server would then respond and push a response file back to the 
CF server. The CF server would process the file and determine a Yes/No 
answer and either add the item to the cart or display a message that 
the item is out of stock to the customer.

I was wondering, can this even be done with CF and the FTP protocol 
instead of using HTTP? With HTTP, this would be relatively easy with 
the cfhttp tag. With FTP, I am not sure how the communication would 
work. :(

In my mind, even if I used the CF Event Gateway to monitor a predefined 
directory for FTP traffic... I cannot figure out how to parse the FTP 
file and send the response back to the right shopping cart.

Does that make sense? Any help anyone could provide me would be much 
appreciated.

Regards, Che



 
 
 
 
 

~|
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:268810
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Dan G. Switzer, II

Che,

Thanks Dan. My thoughts exactly. In my mind, the SCO software technicians
simply don't want to add a webserver because of the increased security
issues that would arise. And yes, it *would* be terribly slow.

If you can keep the FTP session active all the time, it will help w/the
performance, but I still think it's going to be tediously sluggish. 

Are you planning on having the CF server on Windows or *nix? 

-Dan


~|
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:268812
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Rick Root

Pardon my off topic reply, but I think I might have nightmares about this
scenario when I go to bed tonight!!!

On 2/6/07, Che Vilnonis [EMAIL PROTECTED] wrote:

Good morning all. I have a client that has a CF based website (built by
myself around 5 years ago) and a SCO Unix Open Server backend. It's a
legacy
system with software that's been heavily modified over the last 20-25
years.
Neither system communicates with the other.

The client is looking to streamline the order fulfillment process and to
get with 21st century. They wish to add a live inventory and live credit
card processing feature to their website.

Though I have done this type of work in the past, I have never worked with
a
legacy system to complete this. And, to complicate matters, the software
technicians that administer the SCO Unix Open Server DO NOT wish to use a
HTTP protocol to communicate between the two servers. They tell me that
they
wish to communicate to my CF server via the FTP protocol.

A basic interaction would go something like this. A customer would attempt
to place an item in their shopping cart. Before the item is added to the
cart an inventory check is made. A CF process would write an XML file
and
FTP it to the remote SCO Unix Open Server. The remote server would then
respond and push a response file back to the CF server. The CF server
would
process the file and determine a Yes/No answer and either add the item to
the cart or display a message that the item is out of stock to the
customer.

I was wondering, can this even be done with CF and the FTP protocol
instead
of using HTTP? With HTTP, this would be relatively easy with the cfhttp
tag. With FTP, I am not sure how the communication would work. :(

Does that make sense? Any help anyone could provide me would be much
appreciated.

Regards, Che

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

RE: CF and Legacy Systems... Need Some Advice/Help

Dan, the server will be on Windows. I'm basically using everyone's replies
as a vote for how this should be handled. I don't want to re-invent the
wheel and that is why I have my doubts with doing this via FTP.

~Ché

-Original Message-
From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 10:15 AM
To: CF-Talk
Subject: RE: CF and Legacy Systems... Need Some Advice/Help

Che,

Thanks Dan. My thoughts exactly. In my mind, the SCO software 
technicians simply don't want to add a webserver because of the 
increased security issues that would arise. And yes, it *would* be 
terribly slow.

If you can keep the FTP session active all the time, it will help w/the
performance, but I still think it's going to be tediously sluggish. 

Are you planning on having the CF server on Windows or *nix? 

-Dan

~|
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:268815
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Peterson, Chris

Even using cffile would be faster than FTP.  You would have to have the user 
that Coldfusion is running as (the service) setup with permissions to your 
legacy system, maybe a specific samba share (if possible?)

Chris 

-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 10:20 AM
To: CF-Talk
Subject: RE: CF and Legacy Systems... Need Some Advice/Help

Dan, the server will be on Windows. I'm basically using everyone's replies
as a vote for how this should be handled. I don't want to re-invent the
wheel and that is why I have my doubts with doing this via FTP.

~Ché

-Original Message-
From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 10:15 AM
To: CF-Talk
Subject: RE: CF and Legacy Systems... Need Some Advice/Help

Che,

Thanks Dan. My thoughts exactly. In my mind, the SCO software 
technicians simply don't want to add a webserver because of the 
increased security issues that would arise. And yes, it *would* be 
terribly slow.

If you can keep the FTP session active all the time, it will help w/the
performance, but I still think it's going to be tediously sluggish. 

Are you planning on having the CF server on Windows or *nix? 

-Dan

~|
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:268816
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

2007-02-06 Thread Dan G. Switzer, II

Che,

Dan, the server will be on Windows. I'm basically using everyone's replies
as a vote for how this should be handled. I don't want to re-invent the
wheel and that is why I have my doubts with doing this via FTP.

Unix definitely handles FTP operations much more efficiently than Windows.
On a project that I once worked on, we need to FTP new images to a server
every second (this was for a traffic camera application.) 

We could never find a Windows FTP server that could keep up w/the FTP
operations, on the other hand our Unix server had no issues keeping up w/the
stream of images.

-Dan


~|
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:268817
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: CF and Legacy Systems... Need Some Advice/Help

Dan, this is great to know. And since we have no plans to move to a *nix
environment, that's all the more reason to not use FTP.

Thanks, Ché

-Original Message-
From: Dan G. Switzer, II [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 10:27 AM
To: CF-Talk
Subject: RE: CF and Legacy Systems... Need Some Advice/Help

Che,

Dan, the server will be on Windows. I'm basically using everyone's 
replies as a vote for how this should be handled. I don't want to 
re-invent the wheel and that is why I have my doubts with doing this 
via FTP.

Unix definitely handles FTP operations much more efficiently than Windows.
On a project that I once worked on, we need to FTP new images to a server
every second (this was for a traffic camera application.) 

We could never find a Windows FTP server that could keep up w/the FTP
operations, on the other hand our Unix server had no issues keeping up w/the
stream of images.

-Dan

~|
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:268821
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

RE: cffunction with a loop inside - need some advice

2006-12-15 Thread Richard Kroll

I was not sure if you needed to invoke this as a web service or simply
an object, so I wrote up each.  This makes one call for each 'class'
that needs to be created.  If you wanted to reduce hits on your
database, I would refactor this to take an array of structures of
'classes' as an argument and then loop over the array and do the inserts
from that.

!--- OBJECT CALL ---
cfset myObj = createObject('component', 'path.to.obj') /

cfloop from=1 to=#form.totalPERSONS# index=idx
cfset myObj.createEnrollment(evaluate('form.enrID#idx#'),
evaluate('form.part_class#idx#')) /
/cfloop

!--- WEBSERVICE CALL ---
cfloop from=1 to=#form.totalPERSONS# index=idx
cfinvoke webservice=myWEBSERVICE method=createEnrollment
returnvariable=newID
cfinvokeargument name=ernID
value=#evaluate('form.enrID#idx#')# /
cfinvokeargument name=class
value=#evaluate('form.part_class#idx#')# /
/cfinvoke
/cfloop


!--- FUNCTION ---
cffunction name=createEnrollment access=remote
returntype=numeric
cfargument name=ernID required=true type=numeric
cfargument name=class required=true type=string
cfset var qWrite =  /

cfquery name=qWrite datasource=yourdsn
INSERT INTO participants (
enrID,
part_class )
VALUES (
cfqueryparam cfsqltype=cf_sql_integer
value=#arguments.ernID#,
cfqueryparam cfsqltype=cf_sql_varchar
value=#arguments.class#
)
/cfquery

cfreturn qWrite.newID
/cffunction 

HTH,

Rich Kroll

 -Original Message-
 From: Les Mizzell [mailto:[EMAIL PROTECTED]
 Sent: Thursday, December 14, 2006 9:36 PM
 To: CF-Talk
 Subject: cffunction with a loop inside - need some advice
 
 OK - before I tried turning this particular problem into a service, I
 had a simple query like this, which works (examples simplified for
 clarity, whatever that is!):
 
 cfloop from=1 to=#form.totalPERSONS# index=idx
cfquery name=WRITE_ENROLL
   INSERT INTO participants (
 enrID,
 part_class )
VALUES (
 #evaluate(form.enrID#idx#)#,
 '#evaluate(form.part_class#idx#)#' )
/cfquery
 /cfloop
 
 
 The function to handle this once converted is failing with 'Can't
 generate stubblan, blah so I've got a syntax problem or just
 simply don't know what I'm doing trying to translate the above.
 
 Here's what I've got:
 
 To invoke the beast:
 
 cfinvoke webservice=myWEBSERVICE
method=ENROLLMENTS
WStotalPERSONS=#form.totalPERSONS#
 cfloop from=1 to=#form.totalPERSONS# index=idx
 WSenrID#idx#=#evaluate(form.enrID#idx#)#
 WSpart_class#idx#=#evaluate(form.part_class#idx#)#
 /cfloop
 
  
 
 
 And here's the function:
 
 cffunction name=ENROLLMENT_TWO
  access=remote
  returntype=query 
 
 cfargument name=WStotalPERSONS type=numeric /
 cfloop from=1 to=#arguments.WStotalPERSONS# index=idx
 cfargument name=WSenrID#idx# type=string /
 cfargument name=WSpart_class#idx# type=string /
 /cfloop

cfloop
 from=1 to=#WStotalPERSONS# index=idx
cfquery name=WRITE_ENROLL
 INSERT INTO participants (
enrID,
part_class )
 VALUES (
WSenrID#idx#,
'WSpart_class#idx#)#' )
 /cfquery
 
 /cfloop
 
 
 
 
 I probably need to rethink the way this particular function has to
work,
 but I desperately need some pointers please!
 
 Thanks,
 
 
 Les
 
 
 

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
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:264107
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: cffunction with a loop inside - need some advice

2006-12-14 Thread Les Mizzell

Despite my obvious error in the query itself, the function error seems
to be caused by the loop:

cfinvoke webservice=myWEBSERVICE
   method=ENROLLMENTS
   WStotalPERSONS=#form.totalPERSONS#
cfloop from=1 to=#arguments.WStotalPERSONS# index=idx
  WSenrID#idx#=#evaluate(form.enrID#idx#)#
  WSpart_class#idx#=#evaluate(form.part_class#idx#)#
/cfloop

If I remove the loop - it validates properly.

OK, how do I get a loop of the value WStotalPERSONS to work inside a
function?

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
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:264099
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

cffunction with a loop inside - need some advice

2006-12-14 Thread Les Mizzell

OK - before I tried turning this particular problem into a service, I 
had a simple query like this, which works (examples simplified for 
clarity, whatever that is!):

cfloop from=1 to=#form.totalPERSONS# index=idx
   cfquery name=WRITE_ENROLL
  INSERT INTO participants (
enrID,
part_class )
   VALUES (
#evaluate(form.enrID#idx#)#,
'#evaluate(form.part_class#idx#)#' )
   /cfquery
/cfloop


The function to handle this once converted is failing with 'Can't 
generate stubblan, blah so I've got a syntax problem or just 
simply don't know what I'm doing trying to translate the above.

Here's what I've got:

To invoke the beast:

cfinvoke webservice=myWEBSERVICE
   method=ENROLLMENTS
   WStotalPERSONS=#form.totalPERSONS#
cfloop from=1 to=#form.totalPERSONS# index=idx
WSenrID#idx#=#evaluate(form.enrID#idx#)#
WSpart_class#idx#=#evaluate(form.part_class#idx#)#
/cfloop

 


And here's the function:

cffunction name=ENROLLMENT_TWO
 access=remote
 returntype=query 

cfargument name=WStotalPERSONS type=numeric /
cfloop from=1 to=#arguments.WStotalPERSONS# index=idx
cfargument name=WSenrID#idx# type=string /
cfargument name=WSpart_class#idx# type=string /
/cfloop

cfloop from=1 to=#WStotalPERSONS# index=idx
   cfquery name=WRITE_ENROLL
INSERT INTO participants (
   enrID,
   part_class )
VALUES (
   WSenrID#idx#,
   'WSpart_class#idx#)#' )
/cfquery

/cfloop




I probably need to rethink the way this particular function has to work, 
but I desperately need some pointers please!

Thanks,


Les


~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
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:264101
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4

Re: cffunction with a loop inside - need some advice

2006-12-14 Thread Les Mizzell

I'm completely stumped. Nothing I've tried seems to work. All I need to 
do is turn the query below, which inserts multiple records, into a 
function and then invoke that function.

cfloop from=1 to=#form.totalPERSONS# index=idx
cfquery name=WRITE_ENROLL
   INSERT INTO participants (
 enrID,
 part_class )
VALUES (
 #evaluate(form.enrID#idx#)#,
'#evaluate(form.part_class#idx#)#' )
/cfquery
/cfloop



How would you folks handle this?

~|
Create robust enterprise, web RIAs.
Upgrade  integrate Adobe Coldfusion MX7 with Flex 2
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:264102
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: need some advice

2001-06-09 Thread Steve Reich


First join JS-Jive at http://groups.yahoo.com/group/JS-Jive for all the
Javascript help you can stand. But to answer your question, your best bet is
to control the forms action depending on how the form in submitted (either
through the submit button or the onchange event of the select box). You can
find some good information on how to do this here...
http://developer.irt.org/script/form.htm#7.2.

HTH,
Steve

S R [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
Hi Everyone,

I've been staring at my code to long and I'm stumped. I have a select menu
(see below) that is being populated from a databse.  My problem is, if a
user chooses an option from the pull down menu it needs to also send the
rest of my form variables to the next page. Right now, using the 'onChange'
handler, its only send the ID variable within the 'option' tag. Now I know I
can use the 'this.form.submit()' to accomplish this but I cannot use it
because I need the page to go somewhere different when they press 'submit'.
What would be cool is if I could dynamically change the form tag to go one
place when they click submit and another if they choose an option from the
pull down menu. I'm not well versed in Javascript, but I probably could do
it through Javascript. Any help will do.

Thanks

 function openURL(){
 // grab index number of the selected option
 selInd = document.myForm.CompanyID2.selectedIndex;

 // get value of the selected option
 goURL = document.myForm.CompanyID2.options[selInd].value;

 // redirect browser to the grabbed value (hopefully a URL)
 top.location.href = goURL;
}
// end hiding script--

  cfoutputselect name=CompanyID2 onchange=openURL()/cfoutput
option value=/option
   cfoutput query=GetCompanies
option value=index.cfm?ID=#ID# cfif IsDefined(ThisVar) AND
FORM.CompanyID2 IS NOT ''cfif GetCompanies.ID IS
FORM.CompanyID2selected/cfif/cfif#CompanyName#/option
   /cfoutput
  /select



~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

need some advice

2001-06-08 Thread S R


htmlDIVHi Everyone,/DIV
DIVnbsp;/DIV
DIVI've been staring at my code to long and I'm stumped. I have a select menu (see 
below) that is being populated from a databse.nbsp; My problem is, if a user chooses 
an option from the pull down menu it needs to also send the rest of my form variables 
to the next page. Right now, using the 'onChange' handler, its only send the ID 
variable within the 'option' tag. Now I know I can use the 'this.form.submit()' to 
accomplish this but I cannot use it because I need the page to go somewhere different 
when they press 'submit'. What would be cool is if I could dynamically change the 
lt;formgt; tag to go one place when they click submit and another if they choose an 
option from the pull down menu. I'm not well versed in Javascript,nbsp;but I probably 
could do it through Javascript. Any help will do./DIV
DIVnbsp;/DIV
DIVThanks/DIV
DIVnbsp;/DIV
DIVnbsp;function openURL(){ /DIV
DIVnbsp;// grab index number of the selected optionBRnbsp;selInd = 
document.myForm.CompanyID2.selectedIndex; BRnbsp;BRnbsp;// get value of the 
selected optionBRnbsp;goURL = 
document.myForm.CompanyID2.options[selInd].value;BRnbsp;BRnbsp;// redirect 
browser to the grabbed value (hopefully a URL)BRnbsp;top.location.href = goURL; 
BR}BR// end hiding script--gt;/DIV
DIVnbsp;/DIV
DIVnbsp;nbsp;lt;cfoutputgt;lt;select name=CompanyID2 
onchange=openURL()gt;lt;/cfoutputgt;BRnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
 lt;option value=gt;lt;/optiongt;BRnbsp;nbsp;nbsp;lt;cfoutput 
query=GetCompaniesgt;BRnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
 lt;option value=index.cfm?ID=#ID# lt;cfif IsDefined(ThisVar) AND 
FORM.CompanyID2 IS NOT ''gt;lt;cfif GetCompanies.ID IS 
FORM.CompanyID2gt;selectedlt;/cfifgt;lt;/cfifgt;gt;#CompanyName#lt;/optiongt;BRnbsp;nbsp;nbsp;lt;/cfoutputgt;BRnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
 lt;/selectgt;/DIVbr clear=allhrGet your FREE download of MSN Explorer at a 
href=http://explorer.msn.com;http://explorer.msn.com/abr/p/html

~~
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm

Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: CFAPPLICATION - Need some advice


Let me see if I understand you correctly..if you create your own cookies
to store CFID and TOKEN, overwriting the ones put there by default by CF, do
these cookies automatically get destroyed when the browser is closed as
opposed to the default cookies which expire on the client when the session
expires?

The reason I am trying to do this is because I have a large site, split over
three servers (2 web and a database) and I am setting them up so they are
load balanced (so using Client variables instead of session variables).  The
site is driven by a single page that pulls in other pages and at the top of
this page there is a test to ensure the user has logged in.  I want to force
the site to log the user out when they close their browser.  If this code
does what I think your saying it does then I think it'll workthe only
problem I'll have is a build up of client variable data in the database but
I guess I can get CF to purge this on a regular basis.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 23:39
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice


  Put this code in your application.cfm file. It will kill the session
  variables when the user closes the browser:
 
  !--- if the user closes their browser, make sure all sesion
  variables get
  killed ---
  cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")
cfset cfid_local = Cookie.CFID
cfset cftoken_local = Cookie.CFTOKEN
cfcookie name="CFID" value="#cfid_local#"
cfcookie name="CFTOKEN" value="#cftoken_local#"
  /cfif
...
 I don't think this will do what we are hoping it will. It looks to
 me as if all it's doing is testing to see if the session is open,
 if it is it's reading the values from the cookies and then putting
 them back in there.am I completely off the mark here??

The above code is overwriting the cookies that CF automatically creates,
which are persistent beyond the life of the browser instance, with cookies
that will be destroyed when the browser is closed. Thus, when a user closes
the browser, they destroy their link to the session variables on the server,
effectively ending their session.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444


--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: CFAPPLICATION - Need some advice

2000-08-10 Thread Matt Rodosky


Yes, setting the cookie without an expires attribute makes a cookie that
will expire when the browser closes. All you are doing is replacing a
persistent cookie with a session cookie. When the user closes their browser
the cookie will die and they won't be logged in if they reopen the page.
Your client variable data will build up but scheduling a purge of old data
should be easy enough.

Matt
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 5:10 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice


Let me see if I understand you correctly..if you create your own cookies
to store CFID and TOKEN, overwriting the ones put there by default by CF, do
these cookies automatically get destroyed when the browser is closed as
opposed to the default cookies which expire on the client when the session
expires?

The reason I am trying to do this is because I have a large site, split over
three servers (2 web and a database) and I am setting them up so they are
load balanced (so using Client variables instead of session variables).  The
site is driven by a single page that pulls in other pages and at the top of
this page there is a test to ensure the user has logged in.  I want to force
the site to log the user out when they close their browser.  If this code
does what I think your saying it does then I think it'll workthe only
problem I'll have is a build up of client variable data in the database but
I guess I can get CF to purge this on a regular basis.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 23:39
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice


  Put this code in your application.cfm file. It will kill the session
  variables when the user closes the browser:
 
  !--- if the user closes their browser, make sure all sesion
  variables get
  killed ---
  cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")
cfset cfid_local = Cookie.CFID
cfset cftoken_local = Cookie.CFTOKEN
cfcookie name="CFID" value="#cfid_local#"
cfcookie name="CFTOKEN" value="#cftoken_local#"
  /cfif
...
 I don't think this will do what we are hoping it will. It looks to
 me as if all it's doing is testing to see if the session is open,
 if it is it's reading the values from the cookies and then putting
 them back in there.am I completely off the mark here??

The above code is overwriting the cookies that CF automatically creates,
which are persistent beyond the life of the browser instance, with cookies
that will be destroyed when the browser is closed. Thus, when a user closes
the browser, they destroy their link to the session variables on the server,
effectively ending their session.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444


--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: CFAPPLICATION - Need some advice

Thanks...I'll try it and let you know how I get on..

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 10 August 2000 15:57
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

Yes, setting the cookie without an expires attribute makes a cookie that
will expire when the browser closes. All you are doing is replacing a
persistent cookie with a session cookie. When the user closes their browser
the cookie will die and they won't be logged in if they reopen the page.
Your client variable data will build up but scheduling a purge of old data
should be easy enough.

Matt
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 5:10 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

Let me see if I understand you correctly..if you create your own cookies
to store CFID and TOKEN, overwriting the ones put there by default by CF, do
these cookies automatically get destroyed when the browser is closed as
opposed to the default cookies which expire on the client when the session
expires?

The reason I am trying to do this is because I have a large site, split over
three servers (2 web and a database) and I am setting them up so they are
load balanced (so using Client variables instead of session variables).  The
site is driven by a single page that pulls in other pages and at the top of
this page there is a test to ensure the user has logged in.  I want to force
the site to log the user out when they close their browser.  If this code
does what I think your saying it does then I think it'll workthe only
problem I'll have is a build up of client variable data in the database but
I guess I can get CF to purge this on a regular basis.

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 23:39
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

  Put this code in your application.cfm file. It will kill the session
  variables when the user closes the browser:

  !--- if the user closes their browser, make sure all sesion
  variables get
  killed ---
  cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")
cfset cfid_local = Cookie.CFID
cfset cftoken_local = Cookie.CFTOKEN
cfcookie name="CFID" value="#cfid_local#"
cfcookie name="CFTOKEN" value="#cftoken_local#"
  /cfif
...
 I don't think this will do what we are hoping it will. It looks to
 me as if all it's doing is testing to see if the session is open,
 if it is it's reading the values from the cookies and then putting
 them back in there.am I completely off the mark here??

The above code is overwriting the cookies that CF automatically creates,
which are persistent beyond the life of the browser instance, with cookies
that will be destroyed when the browser is closed. Thus, when a user closes
the browser, they destroy their link to the session variables on the server,
effectively ending their session.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.

--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit 
http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a 
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.

RE: CFAPPLICATION - Need some advice

Superstar.that worked a treat.you don't know how long I've been
looking at this!!

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 20:29
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

Actually rewriting the cookies turns all cfid/tokens on the client into
session cookies (no expires attribute). This doesn't do anything to the
server side, where the sessions will eventually die off in memory. this does
mess up client variables unfortunately (leaving the cfid/token in your
datasource, while killing the cookies on the client). We use only
app/session vars, and use a login/database system for client vars so it has
not ben an issue. Sorry if this doesn't help you.

Matt

-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 11:10 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

Okso are you saying that when you re-write the cookie the session/client
variables are cleared?  If that is the case what happens on the server side?
If I am storing client variables in a database o the server and the user
closes their browser the record remains in the database.  When they re-visit
the site the site knows who they are (because of the cookie on their PC) and
gives them the same CFID and CFTOKEN.  Are you saying that by reassigning
these exact same values to the cookies that all of the custom Client
variables will get deleted/reset?

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 17:41
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

The code will keep session variables going as long as the browser stays
open. It will overwrite the old cfid/token when a new browser window is
opened, the result is your old session variables are killed and you start
over.

-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 10:02 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

I don't think this will do what we are hoping it will.  It looks to me as if
all it's doing is testing to see if the session is open, if it is it's
reading the values from the cookies and then putting them back in
there.am I completely off the mark here??

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 16:27
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

Put this code in your application.cfm file. It will kill the session
variables when the user closes the browser:

!--- if the user closes their browser, make sure all sesion variables get
killed ---
cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")
  cfset cfid_local = Cookie.CFID
  cfset cftoken_local = Cookie.CFTOKEN
  cfcookie name="CFID" value="#cfid_local#"
  cfcookie name="CFTOKEN" value="#cftoken_local#"
/cfif

-Original Message-
From: Miguel [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 9:08 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

There's none, either in asp nor php4
Javascript is the way

Miguel Gonzalez
www.ibcmass.com
mailto:[EMAIL PROTECTED]

 -Mensaje original-
 De: Andy Ewings [mailto:[EMAIL PROTECTED]]
 Enviado el: miércoles 9 de agosto de 2000 14:52
 Para: '[EMAIL PROTECTED]'
 Asunto: RE: CFAPPLICATION - Need some advice

 Session management is a bit of a nightmare in CF in my
 opinion.  If the user
 closes their browser their session doesn't expire
 automatically.  One only
 way to force it to expire is to use the 'OnUnload' event of
 the browser to
 trigger some Java Script which ends the session.

 If anyone else has any other ideas on this I'd love to hear them.

 -Original Message-
 From: Gavin Myers [mailto:[EMAIL PROTECTED]]
 Sent: 09 August 2000 13:41
 To: '[EMAIL PROTECTED]'
 Subject: CFAPPLICATION - Need some advice

 I've decided to take a look at application.cfm too see what I
 can gather.
 I've never really used it before

 Now in the simplest form:

 application.cfm
 htmlhead/headbody

 cfapplication name="id" clientmanagement="Yes"
 sessionmanagement="Yes"
 setclientcookies="Yes"

 /body/html
 end application.cfm

 The only thing I am using with the application.cfm file is
 #session.sessionid#, and I need to carry that sessionid
 through pages. But,
 I also want to make it so that when a person leaves my site,
 and then goes
 back they don't get the same sessionid, but a brand new one.
 I could set an
 expire date, but I'd rather have it not use the same one over again
 automatically. Any suggestions?

 Oh and while were on the subject, I want to set global variables while
 person-x is in the site. Example:

 Making a shopping cart that records when a person checks on a
 radio button
 labled purchase. That way when they are

RE: CFAPPLICATION - Need some advice

2000-08-10 Thread mherbene

Curious - in what way does this mess up client vars in a database?  Doesn't
CF remove abandoned client vars cleanly?

-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 10, 2000 12:02 PM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

Superstar.that worked a treat.you don't know how long I've been
looking at this!!

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 20:29
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

Actually rewriting the cookies turns all cfid/tokens on the client into
session cookies (no expires attribute). This doesn't do anything to the
server side, where the sessions will eventually die off in memory. this does
mess up client variables unfortunately (leaving the cfid/token in your
datasource, while killing the cookies on the client). We use only
app/session vars, and use a login/database system for client vars so it has
not ben an issue. Sorry if this doesn't help you.

Matt

-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 11:10 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

Okso are you saying that when you re-write the cookie the session/client
variables are cleared?  If that is the case what happens on the server side?
If I am storing client variables in a database o the server and the user
closes their browser the record remains in the database.  When they re-visit
the site the site knows who they are (because of the cookie on their PC) and
gives them the same CFID and CFTOKEN.  Are you saying that by reassigning
these exact same values to the cookies that all of the custom Client
variables will get deleted/reset?

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 17:41
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

The code will keep session variables going as long as the browser stays
open. It will overwrite the old cfid/token when a new browser window is
opened, the result is your old session variables are killed and you start
over.

-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 10:02 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

I don't think this will do what we are hoping it will.  It looks to me as if
all it's doing is testing to see if the session is open, if it is it's
reading the values from the cookies and then putting them back in
there.am I completely off the mark here??

-Original Message-
From: Matt Rodosky [mailto:[EMAIL PROTECTED]]
Sent: 09 August 2000 16:27
To: [EMAIL PROTECTED]
Subject: RE: CFAPPLICATION - Need some advice

Put this code in your application.cfm file. It will kill the session
variables when the user closes the browser:

!--- if the user closes their browser, make sure all sesion variables get
killed ---
cfif IsDefined("Cookie.CFID") AND IsDefined("Cookie.CFTOKEN")
  cfset cfid_local = Cookie.CFID
  cfset cftoken_local = Cookie.CFTOKEN
  cfcookie name="CFID" value="#cfid_local#"
  cfcookie name="CFTOKEN" value="#cftoken_local#"
/cfif

-Original Message-
From: Miguel [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 09, 2000 9:08 AM
To: '[EMAIL PROTECTED]'
Subject: RE: CFAPPLICATION - Need some advice

There's none, either in asp nor php4
Javascript is the way

Miguel Gonzalez
www.ibcmass.com
mailto:[EMAIL PROTECTED]

 -Mensaje original-
 De: Andy Ewings [mailto:[EMAIL PROTECTED]]
 Enviado el: miércoles 9 de agosto de 2000 14:52
 Para: '[EMAIL PROTECTED]'
 Asunto: RE: CFAPPLICATION - Need some advice

 Session management is a bit of a nightmare in CF in my
 opinion.  If the user
 closes their browser their session doesn't expire
 automatically.  One only
 way to force it to expire is to use the 'OnUnload' event of
 the browser to
 trigger some Java Script which ends the session.

 If anyone else has any other ideas on this I'd love to hear them.

 -Original Message-
 From: Gavin Myers [mailto:[EMAIL PROTECTED]]
 Sent: 09 August 2000 13:41
 To: '[EMAIL PROTECTED]'
 Subject: CFAPPLICATION - Need some advice

 I've decided to take a look at application.cfm too see what I
 can gather.
 I've never really used it before

 Now in the simplest form:

 application.cfm
 htmlhead/headbody

 cfapplication name="id" clientmanagement="Yes"
 sessionmanagement="Yes"
 setclientcookies="Yes"

 /body/html
 end application.cfm

 The only thing I am using with the application.cfm file is
 #session.sessionid#, and I need to carry that sessionid
 through pages. But,
 I also want to make it so that when a person leaves my site,
 and then goes
 back they don't get the same sessionid, but a brand new one.
 I could set an
 expire date, but I'd

RE: CFAPPLICATION - Need some advice