qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.

QA vs sproc mystery

Here's a poser (at least to me!)
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S on the Juvenile table - killing performance.
Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:

> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.