Improve Query Performance by just a small change “OPTIMIZE FOR”

 

Dear All,

If your SQL statement is slow then sometimes just a little change help you a lot in my case it was helpful hope it will helpful in your problem also.

Suppose you want to fetch  data from two large tables  for a specific constant value. Let take example of this

suppose you have two table 1. Student table 2. student enrollment table both table have large amount of data.

Now you want to fetch max record of enrollment id student wise for  particular status in Student enrollment  table.

Then for this you need to write following query .

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
          se.StudentId      
      FROM dbo.StudentEnrollment se WITH (NOLOCK)       
      INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                            AND s. YearId = @YearID
      WHERE sh.Statusid = @StatusActivateId 
      GROUP BY sh.serializationid

Now if you find here the StatusActivateId  is fixed in this case so  we need to optimize this statement   here we used optimize for option 

SELECT ISNULL(MAX(EnrollmentHistoryId),0) AS maxenrmollmentHistoryId ,     
           se.StudentId      
       FROM dbo.StudentEnrollment se WITH (NOLOCK)       
       INNER JOIN dbo.student s WITH (NOLOCK) ON s.studentId = sh.studentId       
                             AND s. YearId = @YearID
       WHERE sh.Statusid = @StatusActivateId 
       GROUP BY sh.serializationid
     OPTION (OPTIMIZE FOR (@StatusActivateId =21))

if you  compare above statements you will find there is only one difference in last line which is option (OPTIMIZE FOR)

As i shared earlier we know the fixed value so we explicitly added the variable name and value for which we need to optimization for the query.

In SQL Server 2008  this option ins more robust which taking any unknown option.

I hope it will help you some where . Just hit and try.

Learn & Share

Thanks

Rajat Jaiswal