Execution Plan difft for 2 same queries..

HI,
Some confusing execution plan of query..
below are all indexes created on tmreqbatch table..
---
ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY BatchCode
NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
----
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]=[K
eyCo1]))
|--Index Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpProdId]), SEEK[TMReqBatch].[CorpProdId]=Co
nvert([@.1])) ORDERED FORWARD)
|--Index Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[NIDX_TmRBatRst]), SEEK[TMReqBatch].[ReqBatStat]=12) ORDERED
FORWARD)
I DON'T UNDERSTAND WHY EXECUTION PLAN IS SHOWING MERGE JOIN..WHEREAS IM NOT
USING ANY JOINS IN MY QUERY..
RESPONSE TIME OF QUERY IS TOO LOW..ITS ACCESSING 2 MILLION RECORDS...
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].[ReqBat
Stat]=12))
===============
Pls help wot exactly is going on......
SAnjaySanjay
I've just tested it on [Order Details] table of the NorthWind database
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT OrderID,ProductID
FROM [order details]as t
WHERE OrderID=10348 AND ProductID=1
For me it produces the same query plan.

quote:

---

> FROM disbursement.TMREQBATCH

---

This way creating alias should throw the error 'Invalid object name
disbursement.TMREQBATCH'.
"Sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:CC28709B-6706-4C79-B20F-79339CED1805@.microsoft.com...

quote:

---

> HI,
> Some confusing execution plan of query..
> below are all indexes created on tmreqbatch table..
> ---
> ID_TmReqBatch_CorpProdId nonclustered located on PRIMARY CorpProdId
> ID_TmReqBatch_FundId nonclustered located on PRIMARY FundId
> IX_TMReqBatch nonclustered, unique, unique key located on PRIMARY

---

BatchCode

quote:

---

> NIDX_TmRBatRst nonclustered located on PRIMARY ReqBatStat
> PK_TMReqBatch clustered, unique, primary key located on PRIMARY Id
> ----
> 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[/color]

---

Seek(OBJECT[ICICI_DISB].[disbursement].[TMReqBatch].[ID_TmReqBatch_CorpPro
dId]), 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...
> 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

---

|||hi,
but im still clueless when there are no joins in query..why execution plan i
s showing merge join...|||Your 2 queries are NOT the same. The result sets include different columns;
therefore a comparison of the query plans is not meaningful. If you examine
the query plan closely for the 1st, you will note that it is using two
indexes. This is because your query selects rows based on only two columns,
and these same two columns are indexed separately. Therefore, the optimizer
concluded that it could join the two indexes to generate the appropriate
resultset.
The optimizer isn't perfect so perhaps it chose an inefficient plan.
Before you conclude that, you should make sure that your statistics are
current. In addition, you might want to re-examine your indexing design.
Perhaps you need another index on (Corpprodid,reqbatstat). You might also
want to consider adding one column to the index on the other column. Or
maybe a join hint. You might also want to consider clustering on something
other than (id); is this column the best choice for a clustered index? The
best answer is dependent on the data, how it is accessed, the frequency of
access, and the frequency of updates.
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:8E76A851-A121-411B-81B9-2275D8A47205@.microsoft.com...

quote:

---

> hi,
> but im still clueless when there are no joins in query..why execution plan

---

is showing merge join...

quote:

---

>

---