Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Thumbs down Unanswered: Sqlserver takes long time to execute. why?

    Hello friends,
    I am using Sqlserver 2005 for accounting software. Software is loading data according to financial year. Each March 31, we can transfer it to next financial year and software automatically calculates opening amount, closing amount etc. In this software application , one ledger search form is there to pick the data according to search criteria as amount. It searches that financial year's data and retrieve the result. Last two financial it works fine. But now, 2011-2012, it slow down and it takes 57 second to retrieve 7000 records. But last two years, for 40 thousand records , it takes max 2 second only. Will anyone help me to solve this problem? My Sql query is like this..

    Select TranHead.Narration,TranTail.* From Trantail,TranHead Where TranHead.CompanyCode=Trantail.CompanyCode And TranHead.YearCode=TranTail.YearCode And TranTail.TranNo=TranHead.TranNo and (TranTail.Credit=0 or TranTail.Debit=0) and Tranhead.CompanyCode='0' And TranHead.Yearcode='20102011' Order By TranHead.TranDate

  2. #2
    Join Date
    Aug 2011
    What types of indexes are on those tables?

    Are there any foreign keys?

    What happens if you tell everyone to get off the software, run "DBCC FREEPROCCACHE" in SSMS then run the query for the 7000 records before the 40,000 record query?

    You need to make sure everyone is off first, I think this is a result of parameter sniffing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts