Query taking more time to execute and causing time out error

• Prakash-485822

  SSC Eights!

  Points: 982

  More actions

  September 16, 2008 at 5:25 am

  #123620

  Dear All,

  The following query is taking more time to execute and causing time out error. Tradedetails table contains more than 5 lakhs of data.

  Select FormCode,P.DateCreated, StateName,

  Case When S.Name Is Null Then SenderId Else S.Name End As SenderId ,

  Case When R.Name Is Null Then RecipientId Else R.Name End As RecipientId,

  SubmissionComments As Comments, SenderId AS Userid

  From TradeDetails P

  Inner Join DataProfileClassWorkflowStates D

  On P.StateId=D.StateId

  Inner Join Users S

  On S.UserId = SenderId

  Inner Join Users R

  On R.UserId = RecipientId

  Where P.FormCode ='Emp-123' Order By P.DateCreated

  Please help me to solve this..

  Regards

  Prakash

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 16, 2008 at 5:37 am

  #872267

  Can you please post the table definitions, the index definitions and the execution plan?

  To get the exec plan on SQL 2000, run the query with SHOWPLAN_ALL on, run to grid, copy the entire grid, paste in excel. Zip the spreadsheet and attach it to your post.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass