OK, to answer a few of the points made, and why I made the original comments
> I usually get quicker queries when using SELECT * if I need to get
> the values of all or most of the fields. Of course it's the other way
> around of you've got 20 columns in your table and only need the
> values from a few of them. I used to always add all the column names
> but now I test it both ways and go with the quickest.
The reason you shouldn't use SELECT * is that the database has to do another
query before returning the one you've made - it has to ask the table what
fields it contains, and then return you that this - 99.9% of the time, it's
faster to not ask the database engine to do this
The only time I've seen it slower is when you pass it around 40+ field names
and one row, and then the SQL passed is so large that this is slower...
> 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.
With SQL Server, all it does is tell it to pass the data back before the
previous locking status - so, instead of checking each row to see if it's
locked, it passes the "current" data - if you're searching a very large
table, then what does it matter if you're 20-30 seconds out of date
I've found that the speed increase of using NOLOCK is around 30% - if that's
not worth your while, then you must be running one hell of a fast database
engine <g>
Do some tests returning several thousand records and check the query speed -
I can (virtually) guarantee that with NOLOCK you'll get a marked improvement
for the loss of the very latest updates...
> Mostly. Theres no point indexing rar,wibble,foo, if all your where clauses
> use rar,foo,theother for instance.
True, but my comment was meant as a general point, not an "Always index all
fields" - the point being that if you have indexes on the most commonly
searched field, the select speed increases.
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
