Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Need some advice on speeding up query
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304253
Thanks for all the info, Mary Jo!
It's been ( and will be ) a big help!
Rick
> 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
----- Excess quoted text cut - see Original Post for more -----
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304248
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>
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304247
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>
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304245
> 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
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304234
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
----- Excess quoted text cut - see Original Post for more -----
the
> code that does the re-cache.
>
> http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_t_04.html
>
> --- Mary Jo
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304233
> 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
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304227
> 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
----- Excess quoted text cut - see Original Post for more -----
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
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304216
> 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
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304212
> 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).
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304200
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
----- Excess quoted text cut - see Original Post for more -----
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
----- Excess quoted text cut - see Original Post for more -----
Author: Mary Jo Sminkey
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304196
> 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.
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304192
Works like a charm! Thanks, guys!
Rick
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304185
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304183
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
----- Excess quoted text cut - see Original Post for more -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304180
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:
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304177
> 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
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304176
> SELECT sa.*,
> 'acr' as propertyType
> FROM smlc_acr sa
I don't understand that, Dominic.
What does the 'acr' refer to?
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304151
> 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
FROM smlc_com sc
etc
(I only put the select * for readability in this example).
HTH
Dominic
--
Blog it up: http://fusion.dominicwatson.co.uk
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304149
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
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304139
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
----- Excess quoted text cut - see Original Post for more -----
Author: Azadi Saryev
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304133
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:
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304129
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304128
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
----- Excess quoted text cut - see Original Post for more -----
Author: Jon Clausen
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304127
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:
----- Excess quoted text cut - see Original Post for more -----
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304126
----- Excess quoted text cut - see Original Post for more -----
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/
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304124
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>
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304066
> 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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304062
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304060
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
> 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
----- Excess quoted text cut - see Original Post for more -----
TABLE_Y
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304059
Yes, I'm using "union all", too.
Thanks...
Rick
----- Excess quoted text cut - see Original Post for more -----
Author: Dominic Watson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304054
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
----- Excess quoted text cut - see Original Post for more -----
Author: Jarlath Gallagher
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304052
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...
----- Excess quoted text cut - see Original Post for more -----
> 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>
Author: James Holmes
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304037
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.
> 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/
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304035
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
----- Excess quoted text cut - see Original Post for more -----
Author: James Holmes
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304034
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.
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304033
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
----- Excess quoted text cut - see Original Post for more -----
Author: Rizal Firmansyah
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304032
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:
----- Excess quoted text cut - see Original Post for more -----
Author: Rick Faircloth
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56141#304029
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>
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
