Query Plan Question

I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:

> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen