Execution Plan difft for 2 same queries..

sql server is using an index intersection, you query is
equivalent to:
SELECT Corpprodid,reqbatstat
FROM TMREQBATCH a
INNER JOIN TMREQBATCH b ON b.ID = a.ID
WHERE a.Corpprodid = 5
AND b.Reqbatstat =12
so it using the index on each column of the SARG, then
does a merge join on the PK
your query would also run fast with a covered index (on
both columns of the SARG) if both columns are frequently
specified as SARGs

quote:

---

>--Original Message--
>HI,
> Some confusing execution plan of query..
>below are all indexes created on tmreqbatch table..
>---
>ID_TmReqBatch_CorpProdId nonclustered located on

---

PRIMARY CorpProdId

quote:

---

>ID_TmReqBatch_FundId nonclustered located on PRIMARY

---

FundId

quote:

---

>IX_TMReqBatch nonclustered, unique, unique key located

---

on PRIMARY BatchCode

quote:

---

>NIDX_TmRBatRst nonclustered located on PRIMARY

---

ReqBatStat

quote:

---

>PK_TMReqBatch clustered, unique, primary key located on

---

PRIMARY Id

quote:

---

>----

---

--

quote:

---

>This is query and execution plan.
>SELECT Corpprodid,reqbatstat
> FROM disbursement.TMREQBATCH with (nolock)
> WHERE Corpprodid = 5
> AND Reqbatstat =12
> |--Merge Join(Inner Join, MERGE[KeyCo1])=([KeyCo1]),

---

RESIDUAL[KeyCo1]=[KeyCo1]))

quote:

---

[col
or=darkred]
> |--Index Seek(OBJECT[ICICI_DISB].[disbursement].[/color]

---

[TMReqBatch].[ID_TmReqBatch_CorpProdId]), SEEK:
([TMReqBatch].[CorpProdId]=Convert([@.1])) ORDERED FORWARD)

quote:

---

> |--Index Seek(OBJECT[ICICI_DISB].[disbursement].

---

[TMReqBatch].[NIDX_TmRBatRst]), SEEK[TMReqBatch].
[ReqBatStat]=12) ORDERED FORWARD)

quote:

---

>I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE

---

JOIN..WHEREAS IM NOT USING ANY JOINS IN MY QUERY..

quote:

---

>RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2

---

MILLION RECORDS...

quote:

---

>I THINK it should use one index and bookmark loop......
> ========================================
==
>same query but using alias showing other execution plan...
>--
>SELECT TMREQBATCH.id, TMREQBATCH.batchcode
> FROM disbursement.TMREQBATCH WITH (NOLOCK)
> WHERE TMREQBATCH.Corpprodid = 5
> AND TMREQBATCH.Reqbatstat =12
>
> |--Clustered Index Scan(OBJECT[ICICI_DISB].

---

[disbursement].[TMReqBatch].[PK_TMReqBatch]), WHERE:
([TMReqBatch].[CorpProdId]=5 AND [TMReqBatch].[ReqBatStat]
=12))

quote:

---

>===============
>Pls help wot exactly is going on......
>SAnjay
>.
>

---

Thanks Joe.
It solved the confusion