----- Original Message -----
From: "Philip Arnold - ASP" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, July 25, 2001 6:55 AM
Subject: RE: 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.
>
> Without seeing your SQL, some comments;
>
> Index the table to hell and back
> Try to only search on Indexed fields
> Never use SELECT *
> Use the (nolock) acellerator (sp?)
>
> Some queries are actually faster to do specific tables in one query, then
do
> the second, then use CF5's Query a Query to join them
>
Be wary of the NOLOCK command. Not only does it tell the database not to
administer a lock for the current read, but it also ignores other locks. So
from what I can tell, this won't make your query run any faster, just ensure
that this particular query won't be locked out and won't lock anyone else
out. This is a dangerous situation for a large scale application with
hundreds of concurrent users updating, adding, and deleting data
simultaneously. The locks are present for a reason, namely, data integrity.
Brian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
