Tuesday, 21 July 2015

Creating Index within Stored Procedure

Introduction
One of my friends asking a question is it better to create Index within Stored Procedure depends on the columns used in stored procedure to improve the Join performance and Drop the stored procedure after getting the desired output and just before the ending of Stored Procedure.

Technical Answer
Yes we can create Index within the Stored Procedure

Now we have to think what we answer it
Technically it is possible what my friend is mentioned in the scenario. But performance wise it’s again a problematic solution. That’s means to solve something we again create another problem. To understand it properly let’s take an example of such kind of Stored Procedure.

IF OBJECT_ID(N'[dbo].[sproc_StudentDetails]', N'P')IS NOT NULL
   DROP PROCEDURE [dbo].[sproc_StudentDetails];
GO

CREATE PROCEDURE [dbo].[sproc_StudentDetails]
     (
         @p_StdClass   INT
      )
AS
BEGIN
      -- Creating Non Clustered Index on IDNO
      CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdDtl
              ON tbl_StudentDetails(IDNO);

       CREATE NONCLUSTERED INDEX IX_NC_IDNO_StdMarks
              ON tbl_StudentMarks(IDNO);

      -- Making JOIN on IDNO for Performance Reason
       SELECT a.Roll, a.StdName, b.Marks1, b.Marks2
       FROM   tbl_StudentDetails AS a
              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

      -- Droping the Index
       DROP INDEX IX_NC_IDNO_StdDtl ON tbl_StudentDetails;
       DROP INDEX IX_NC_IDNO_StdMarks ON tbl_StudentMarks;
END
GO

Here in this example if we look carefully the No clustered Index is created and after successful joining it again drops.
It is technically perfect.

So what the Problem is

SELECT a.Roll, a.StdName, b.Marks1, b.Marks2
       FROM   tbl_StudentDetails AS a
              INNER JOIN tbl_StudentMarks AS b ON a.IDNO = b.IDNO;

The performance of the JOIN condition is increased due to non clustered index. So we think that we got it.

NO it is NOT.  We have to understand something before implemented it.
When the Index Created the Index table and the Statistical table both are updated, so making index within stored procedure again takes a long time to create index.
By the index creation we solve the Join performance but Index creation time is again a pain faction and slowdown the performance of Stored procedure.



Hope you like it.



Posted by: JOYDEEP DAS


No comments:

Post a Comment