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
