Hello All,
CF 4.01, Winnt 4.0 sp 5, mssql 6.5
I am having major problems with the speed of a particular query in one of my
CF apps. In the application, users are able to search the db for a list of
physicians within their area (can search by city, state, zip, and physician
specialty). The problem is that users are allowed to choose multiple
specialties (there are 164 of them). I have set up the code to loop through
the specialty list and perform the query on each loop (possibly 164
iterations). That wouldn't be so bad if the db table weren't so large, but
there are ~580k rows in the table. Thus if you choose a large number of
physician specialties, the query becomes extremely slow. I tried to set up
indexes on each of the searchable fields in the table (mentioned above), but
that doesn't seem to help. I'll include the code below and a link to look
at the app. If you have any suggestions, hints, comments I would be glad to
hear them.
http://isnt3.intellistar.net/ppha/printdirectory.cfm
<cfloop list="#FORM.specialty#" index="spec">
<cfquery name="getproviders" datasource="ppha">
select * from providers
where 0=0
<cfif len(trim(spec))>
and specialty='#spec#'
</cfif>
<cfif len(trim(new_zip))>
and zip like '#trim(new_zip)#%'
</cfif>
<cfif len(trim(FORM.city))>
and city='#trim(FORM.city)#'
</cfif>
<cfif len(trim(state))>
and state='#trim(FORM.state)#'
</cfif>
order by lname
</cfquery>
<!--- CODE TO OUTPUT RESULTS OF QUERY --->
</cfloop>
Thanks,
Stephanie Cunningham
Web System Developer
IntelliSTAR(sm)
Exclusive Provider of Internet LifeCycle Services(sm)
http://www.intellistar.net - mailto:[EMAIL PROTECTED]
407-206-0788 - 407-206-0826 FAX
Providing Residence for Professionals on the Internet(sm), Est. 1995
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
