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.