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