Re: Using CAST() in WHERE Clause hangs/slows Query
From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 05/26/04
- Next message: Dan: "AutoShrink Database Transaction Log"
- Previous message: Wayne Snyder: "Re: Help query..."
- In reply to: ckaneko: "Re: Using CAST() in WHERE Clause hangs/slows Query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 May 2004 07:17:22 -0400
When you use a column in a WHERE clause which has any function on it, SQL
Server can NOT use index statistics to determine which is the best index to
use. The optimizer then uses some hard coded estimates as to the estimated
number of rows that may be returned...THen picks the best index based on the
hard coded estimates.
Although using functions in where clauses does not actually prevent the use
of indexes, the inability to see index statistics increases the liklihood
that a less than optimal index or even a table scan will be chosen.....
-- Wayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "ckaneko" <ckaneko@ffi-hq.com> wrote in message news:OD95ibpQEHA.3140@TK2MSFTNGP11.phx.gbl... > Thanks everyone. I did remove the casting and it did make a big difference! > I created the query in a view then placed it into a stored procedure. The > view placed the Cast function in there. > > I still don't understand as to why the query runs quickly sometimes and > other times it doesn't. > > "Guillaume" <mssupport@candg2.com> wrote in message > news:310186B3-1158-4766-BB4F-07D239A65D77@microsoft.com... > > I don't pretend to be giving a definitive answer on this, but from what I > know, when you use a function (like CAST) on a column it disables the use of > any regular index on that column. > > > > If you have a lot of data, the simple overhead of using a function could > also slow it down. > > > > > >
- Next message: Dan: "AutoShrink Database Transaction Log"
- Previous message: Wayne Snyder: "Re: Help query..."
- In reply to: ckaneko: "Re: Using CAST() in WHERE Clause hangs/slows Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
