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
