May we see the query?
The best thing to do is to write your SQL statements to search on as few a number of
fields as possible, especially when you're doing full-text searching. Turning on
full-text indexing in SQL will increase the performance a little bit. Turning your
SQL statements into stored procedures may also help. Cacheing queries definitely
helps.
Avoid using open LIKE statements wherever possible (LIKE %#var#%). If you can't avoid
using LIKE operators in your SQL statements, then at least try to limit the searching
capability to BEGINS WITH, ENDS WITH, IS EXACTLY rather than ANY PART.
Also, avoid multi-table joins to increase performance.
Has anyone written a book that specifically covers this subject? I'd buy it. Ben?
How about you buddy?
---mark
========================================Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
========================================
-----Original Message-----
From: David Clay [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 24, 2001 1:50 PM
To: CF-Talk
Subject: SQL Query
I am looking for help on a way to query a table fast and efficiently. I am currently
using a ODBC connection to SQL2000 Server with a simple CFQUERY tag doing the query.
For a table with 18K records, it takes 14 seconds.
I know this is not doing something correct!! Any help designing new query statements
or set up of CF or SQL2000 Servers would be helpful.
Thank you for your time.
Dave Clay
Internet Facilitator
Trus Joist, A Weyerhaeuser Business
5995 Greenwood Plaza Blvd, Suite 100
Greenwood Village, CO 80111
303.770.8506
303.770.8506
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
