SP optimization with TVF, View

Had problem with a SP,

  1. SP has a few parameters
  2. SP unions three queries and then filter by a parameter and then return data 
  3. One of the three queries links a TVF with a few tables
  4. The TVF links a standard view with a few tables
  5. The View links several tables, however not all tables are used by the TVF
Symptom:
  • One table used by the TVF has many scan count and slows down the SP
Optimization options:
  1. Bring the parameter to the three queries referenced by the SP - no effect
  2. Modify the TVF and pass in the SP parameter - Speed up 4x
  3. Replace the view with the base table and remove unused tables - Speed up 4x





Comments

Post a Comment

Popular posts from this blog

Project Professional 2013 Error 12015

Image
Got an error 12015 when opening Project Professional 2013.  Clicking the More info and it shows this: <detail><ServerExecutionFault xmlns="http://Microsoft.Office.Project.Interfaces/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Actor i:nil="true"/><LastError>12015</LastError><Message/><Source/><StackTrace/><TargetSite i:nil="true"/><ExceptionDetails><errinfo xmlns=""><general><class name="ActiveCacheUnsupportedProjectProfessionalVersion"><error id="12015" name="ActiveCacheUnsupportedProjectProfessionalVersion" uid="315f3c6b-46fa-e411-80d9-00155d106504" version="15.0.4569.1504"/></class></general></errinfo></ExceptionDetails></ServerExecutionFault></detail> and the second error message is: According technet ( http://blogs.technet.com/b/p
Read more

Unable to initialize the Shared Memory listener. Access is denied

SQL Server failed to start  Run SQL Server from command line and shows this error: Unable to initialize the Shared Memory listener. Access is denied Cause: When the SQL Server starts it tries to open listening named-pipes to accept client share memory or named-pipe connections. If the listening named-pipes are not closed properly during the last shutdown of SQL Server (maybe after a forced kill of the SQL Server process), there will be orphan named-pipe handles in the windows kernel file system. To solve this issue, a quick solution will be to reboot   the host machine.
Read more