Need some advice on speeding up query

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>

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

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

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 <Rick@whitestonemedia.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

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

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 <Rick@whitestonemedia.com> 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/

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

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

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

Yes, I'm using "union all", too. Thanks... Rick ----- Excess quoted text cut - see Original Post for more -----

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>

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

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>

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

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

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

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

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

> 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

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

> 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

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

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

Works like a charm!  Thanks, guys! Rick ----- Excess quoted text cut - see Original Post for more -----

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

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

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

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

> 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

> 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

> 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

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

> 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

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>

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>

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

----- 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/

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