Performance issue in SQL

Hi friends I am in big trouble.

I have query which is taking around 20 mins in execution.(4.5 crores records).

This is the query.

  SELECT 
     a.cmddefinitionid,

     b.cmdinstanceid,

     b.mobileid,

     d.phonenumber,

     d.hardwareid,

     d.smsemail,

     a.cmdid,

     c.cmdname,

     c.cmdxmldesc,

     a.eventflag,

     a.recurrenceflag,

     a.paramflag,

     a.file开发者_高级运维name,

     a.paramname,

     a.VALUE,

     a.meterflag,

     a.gosilentflag,

     a.regurl,

     b.scheduleddate,

   --  e.TxnTypeID, -- Added 



    e.TxnID,-- Added

    e.StatusMsg,-- Added

    b.LastModified as TimeCreated,-- Added newly

    d.PanelistID -- Added newly

FROM   

 ( select CmdInstanceID, TxnTypeID, TxnID, StatusMsg

              from  ODM_TDCS.dbo.CMD_TXN

              where TxnTypeID < 3 

              and TxnID IN (

              select max(TxnID)

              from ODM_TDCS.dbo.CMD_TXN                 

              group by CmdInstanceID)

  ) AS e, 

  dbo.cmd_definition AS a, 
  dbo.cmd_instance AS b, 
  dbo.lu_cmd AS c,
  dbo.lu_mobile AS d
  WHERE  
  a.cmddefinitionid = b.cmddefinitionid 
  and 
  a.cmdid = c.cmdid and b.mobileid = d.mobileid and 
  b.cmdtypeid = 2 AND
  b.scheduleddate > Getdate() - 2 AND
  b.CmdInstanceID = e.CmdInstanceID

Now out of this:

select CmdInstanceID, TxnTypeID, TxnID, StatusMsg
            from  ODM_TDCS.dbo.CMD_TXN
            where TxnTypeID < 3 
            and TxnID IN (
            select max(TxnID)
            from ODM_TDCS.dbo.CMD_TXN           
            group by CmdInstanceID)

this is taking above 5 mins , but if i remove this condition the query gets executed in

0.17 sec.

Any help or suggestion??

---

Try with this...

SELECT a.cmddefinitionid,

 b.cmdinstanceid,

 b.mobileid,

 d.phonenumber,

 d.hardwareid,

 d.smsemail,

 a.cmdid,

 c.cmdname,

 c.cmdxmldesc,

 a.eventflag,

 a.recurrenceflag,

 a.paramflag,

 a.filename,

 a.paramname,

 a.VALUE,

 a.meterflag,

 a.gosilentflag,

 a.regurl,

 b.scheduleddate,

-- e.TxnTypeID, -- Added

e.TxnID,-- Added

e.StatusMsg,-- Added

b.LastModified as TimeCreated,-- Added newly

d.PanelistID -- Added newly

FROM   

 (Select * from (
select CmdInstanceID, TxnTypeID, TxnID, StatusMsg, 
ROW_NUMBER() over (partition by CmdInstanceID order by TxnID desc ) as Row
from  ODM_TDCS.dbo.CMD_TXN
where TxnTypeID < 3 
)as t where e.Row = 1

) AS e, 

  dbo.cmd_definition AS a, 
  dbo.cmd_instance AS b, 
  dbo.lu_cmd AS c,
  dbo.lu_mobile AS d
  WHERE  
  a.cmddefinitionid = b.cmddefinitionid 
  and 
  a.cmdid = c.cmdid and b.mobileid = d.mobileid and 
  b.cmdtypeid = 2 AND
  b.scheduleddate > Getdate() - 2 AND
  b.CmdInstanceID = e.CmdInstanceID

Just used Row_number function and this will avoid travelling ODM_TDCS.dbo.CMD_TXN repeatedly.

Good luck.