Improve Query Performance by just a small change “OPTIMZE 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

Advertisements

One thought on “Improve Query Performance by just a small change “OPTIMZE FOR”

  1. Heya i am for the first time here. I found this board
    and I find It really useful & it helped me out much. I hope to give something back and help others like you helped
    me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s