1

Every now and then Entity Framework Timesout on always the same SQL View then I always have two options to fix the website

I run this query

USE [ibeems]
DBCC FREEPROCCACHE

OR

I alter the view

Then the webpage opens instantly for the next few days.

this is the View

USE [ibeems]

GO
ALTER VIEW [dbo].[ViewNewVerificationList]
AS
   SELECT        
      dbo.Quotation.ClientId, dbo.Quotation.ActualTurnover,    
      dbo.Quotation.VerificationFee, dbo.Quotation.ConsultationFee, 
      dbo.Quotation.RequestDateTime, dbo.Quotation.ConsultantFirmId, 
      dbo.Quotation.CompanyId,
      dbo.ActionHistory.ActionNameId AS LastActionID,
      dbo.ActionHistory.ActionDateTime AS LastActionDateTime,
      dbo.ActionHistory.Message AS LastActionNote,
      dbo.ActionHistory.ActionHistoryId AS LastQuotationActionHistoryId, 
      dbo.ActionHistory.ActionCategoryId AS LastActionCategoryID, 
      dbo.Client.CompanyName, dbo.Client.RegistrationNumber,    
      dbo.Client.ContactPerson, dbo.Client.ContactCellNumber, 
      dbo.Client.ContactEmail, dbo.Client.ConsultantId, 
      dbo.Consultant.Name, dbo.Consultant.Surname, 
      dbo.Quotation.Active AS QuotationActive, 
      dbo.Client.ClientCategoryId, dbo.Quotation.VerificationType, 
      dbo.Client.NoClientLogin, 
      dbo.VerificationTask.VerificationTaskId, 
      dbo.VerificationTask.StartedDateTime, 
      dbo.VerificationTask.FilePrepConsultantId, 
      dbo.VerificationTask.SubCharterSectorID, 
      dbo.VerificationTask.Active, 
      dbo.VerificationTask.CertificateDirectory, 
      dbo.VerificationTask.FinishDateTime, dbo.VerificationTask.UserId, 
      dbo.VerificationTask.QuoteId, 
      dbo.[User].Name AS UserName, dbo.[User].Surname AS UserSurname, 
      dbo.[User].IdNumber, dbo.[User].CellNumber, 
      dbo.[User].EmailAddress
   FROM                 
      dbo.Quotation 
   INNER JOIN
      dbo.Client ON dbo.Quotation.ClientId = dbo.Client.ClientId 
   INNER JOIN
      dbo.VerificationTask ON dbo.Quotation.QuoteId = dbo.VerificationTask.QuoteId 
   INNER JOIN
      dbo.Consultant ON dbo.VerificationTask.FilePrepConsultantId = dbo.Consultant.ConsultantId 
   INNER JOIN
      dbo.[User] ON dbo.VerificationTask.UserId = dbo.[User].UserId 
   INNER JOIN
      dbo.ActionHistory ON dbo.Quotation.QuoteId = dbo.ActionHistory.QuoteId
   INNER JOIN 
      (SELECT 
           ActionHistory.QuoteId, 
           MAX(ActionHistory.ActionHistoryId) As MaxId 
       FROM     
           ActionHistory 
       WHERE 
           (ActionNameId <> 9) AND (ActionNameId <> 23) 
           AND IsRemoved = 0
       GROUP BY 
           ActionHistory.QuoteId) MaxActionHistory ON MaxActionHistory.MaxId = ActionHistory.ActionHistoryId
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Donald Jansen
  • 1,937
  • 4
  • 22
  • 41

1 Answers1

0

Have a look at this article.

http://blogs.u2u.be/diederik/post/2011/01/31/Building-an-Entity-Framework-40-model-on-views-practical-tips.aspx

Some tips

  1. Use SCHEMABINDING
  2. Index the views

You can also remove a single plan from SQL Server, or possibly maybe create a stored procedure and map it with EF.

Community
  • 1
  • 1
Mez
  • 4,666
  • 4
  • 29
  • 57
  • How would I remove the single plan from SQL Server on the database. – Donald Jansen Oct 30 '14 at 08:20
  • Check the link I sent you, and also - you can use the FREEPROCCACHE sending the plan_handle - http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/ – Mez Oct 30 '14 at 08:40
  • Awesome I will get back in a few days, currently the cache is clean after running "DBCC FREEPROCCACHE" I will wait when the users contact me again because of Time Out on web page – Donald Jansen Oct 30 '14 at 09:04
  • You can either clear the plan before running it, if you are sure you are better off without it, or catch timeout exceptions, and clear the plan then. – Mez Oct 30 '14 at 09:13