Slow Query caused by indexes on different file groups??

From: FLX (nospam_at_hotmail.com)
Date: 06/07/04 

Date: Mon, 7 Jun 2004 12:19:21 -0500

A query becomes more than 50 times slower when I replace the actual values
in the where clause with the variables.

Here is the query:

-----------------------------
Start ---------------------------------------------------

declare @Clientid varchar(16)
 , @StartDateTime DateTime
 , @EndDateTime DateTime

set @ClientID = 94
set @StartDateTime = '05/01/2004'
set @EndDateTime = '05/31/2004 23:59:59'

select datediff(dd, StartDate, EndDate) as Duration
             , count(*) as PieceCount
into #tempTest
from LetterTable (nolock)
where ClientID = @ClientID -- 94 ???? problem here
         and EndDate between @StartDateTime and @EndDateTime -- between
'05/01/2004' and '05/31/2004 23:59:59'
group by datediff(dd, StartDate, DelDateAct)

------------------------------
End --------------------------------------------------

The LetterTable has more than 5 million records. It has index on ClientID
column and another index on EndDate column.
However, the index for clientid is in the INDEX file group and the index for
ClientID is on the PRIMARY file group (same filegroup with the data file).

I noticed the execution plan became extremely messy after replacing the
values with variables. Before the replacements, the query simply does a
index scan on the ClientID column and is very fast.

I think put indexes on different file groups caused this problem (Somebody
else did it and I don't know the intention anyway). How are you gurus think
about it? Is there any other root cause?

Your help is highly appreciated.
Lixin