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: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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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
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: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...
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...
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...
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...
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
