Improve performance of stored procedure called by java application

Improve performance of stored procedure called by java application

We have a stored procedure being called from our java web service.  It takes 1 sec from MS SQL management studio but takes 32 sec from the client of web service.

Java code:

long start = System.currentTimeMillis();
Query query = getSessionFactory().getCurrentSession().createSQLQuery("{call sp_GetNextNumber}");
String result = (String)query.uniqueResult();
logger.debug("sp_GetNextNumber took time:{}",(System.currentTimeMillis()-start));

Stored Procedure:

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetNextNumber]     
AS

SET NOCOUNT ON
.
.
.

Few setting which will improve performance of stored procedure

set implicit_transactions off
set transaction isolation level READ COMMITTED

So new stored procedure will be

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetNextNumber]     
AS

SET NOCOUNT ON
set implicit_transactions off
set transaction isolation level READ COMMITTED
.
.
.

Leave a Reply

Your email address will not be published. Required fields are marked *