August 23, 2016 at 9:46 am
create PROCEDURE [dbo].[sp_test]
@PropertyAssetID BIGINT,
@DatatoRetrieveDate NVARCHAR(100) = NULL,
@NoOfMonths NVARCHAR(100) = NULL,
@STATUS_CODE int=null OUTPUT,
@STATUS_MSG NVARCHAR(100)='' OUTPUT
AS
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @Count INT
--select @Count=count(*) from AccountDataItems accDatItms WHERE accDatItms.PropertyAssetId=@PropertyAssetID AND accDatItms.AccountDataType=0;
SELECT @Count=count(*) from dbo.PropertyAssets pa
inner join Scenarios sce on sce.id = pa.scenarioId
inner join Portfolios por on por.id = sce.PortfolioId
where por.Name = 'Healthcare Real Estate Active Portfolio' and pa.Id=@PropertyAssetID
IF @Count > 0
BEGIN
DECLARE @ActualValuesEnd DATE
DECLARE @ActiveRecord INT
SELECT @ActiveRecord=count(*) from dbo.PropertyAssets pa
inner join Scenarios sce on sce.id = pa.scenarioId
inner join Portfolios por on por.id = sce.PortfolioId
where por.Name = 'Healthcare Real Estate Active Portfolio' and (pa.status IS NULL or pa.status = '') and pa.Id=@PropertyAssetID
IF @ActiveRecord > 0
BEGIN
SET @ActualValuesEnd = (SELECT ActualsValuesEnd AS "ActualValuesEnd" FROM PropertyAssets WHERE Id=@PropertyAssetID);
SELECT accDatItms.PropertyAssetId as "PropertyAssetId", @ActualValuesEnd as "ActualValuesEnd", accDatItms.Name as "AccountName",
accDatItms.AccountCode as "AccountCode", accDatItms.[Values] as "Actuals", @DatatoRetrieveDate as "DatatoRetrieveDate", @NoOfMonths as "NoOfMonths" FROM
[dbo].AccountDataItems accDatItms WHERE accDatItms.PropertyAssetId=@PropertyAssetID AND accDatItms.AccountDataType=0 order by AccountCode;
SET @STATUS_CODE=0;
SET @STATUS_MSG='Success';
END
ELSE
BEGIN
SET @STATUS_CODE=2002;
SET @STATUS_MSG='Record Not Active';
END
END
ELSE
BEGIN
SET @STATUS_CODE=2001;
SET @STATUS_MSG='Data Not Found';
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
SET @STATUS_CODE=ERROR_NUMBER();
SET @STATUS_MSG=ERROR_MESSAGE();
END CATCH
INSERT INTO EVENT_LOG(
EVENT_NAME,
EVENT_INPUT,
EVENT_RESPONSE,
EVENT_SERVICENAME
)
VALUES('sp_PropertyActualsV2',
CONCAT('@PropertyAssetID=',@PropertyAssetID,',@DatatoRetrieveDate=',@DatatoRetrieveDate,',@NoOfMonths=',@NoOfMonths),
CONCAT('@STATUS_CODE=',@STATUS_CODE,',@STATS_MSG=',@STATUS_MSG),
'getPropertyActuals');
PRINT 'STATUS CODE: ' +CAST(@STATUS_CODE AS NVARCHAR)
PRINT 'STATUS MESSAGE: ' +@STATUS_MSG
END
-------
One of very helpful members of different fourm replied to my thread and suggested a few changes and tried it with those changes but looks like xml source column from DB is killing the performance. If i comment the filed response will be with 300ms and if we add the xml column it takes 3-5 seconds for 25 ids
Updated Proc
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_testV2]
@PropertyAssetID XML,
@STATUS_CODE int=null OUTPUT,
@STATUS_MSG NVARCHAR(100)='' OUTPUT
AS
BEGIN
Print '###########InputRequest################'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
declare @ID BIGINT,@ActualValuesEnd DATE,@DatatoRetrieveDate NVARCHAR(100),@NoOfMonths NVARCHAR(100)
Print 'Before Droping temporary table if present'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
IF OBJECT_ID('Tempdb..#ListActualsTable') IS NOT NULL
BEGIN
TRUNCATE TABLE #ListActualsTable
DROP TABLE #ListActualsTable
END
Print 'After Droping temporary table if present'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
CREATE TABLE #ListActualsTable(
rowID int not null identity(1,1),
PropertyAssetId BIGINT,
ActualValuesEnd DATE,
AccountName NVARCHAR(100),
AccountCode NVARCHAR(100),
Actuals XML,
DatatoRetrieveDate NVARCHAR(100),
NoOfMonths NVARCHAR(100)
)
Print 'Temporary table if present'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
declare @curse cursor,@totalRec int
set @curse = cursor fast_forward for select property.value('@id','BIGINT') as ID
from @PropertyAssetID.nodes('request/property')request(property)
open @curse
fetch next from @curse into @ID
while (@@fetch_status = 0)
begin
Print 'Before Executing stored procedur each'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
SET @ActualValuesEnd = (SELECT ActualsValuesEnd AS "ActualValuesEnd" FROM PropertyAssets WHERE Id=@ID);
--insert into #ListActualsTable execute sp_PropertyActualsV2 @ID
insert into #ListActualsTable SELECT accDatItms.PropertyAssetId as "PropertyAssetId",
@ActualValuesEnd as "ActualValuesEnd",
accDatItms.Name as "AccountName",
accDatItms.AccountCode as "AccountCode", accDatItms.[Values] as "Actuals", @DatatoRetrieveDate as "DatatoRetrieveDate", @NoOfMonths as "NoOfMonths" FROM
[dbo].AccountDataItems accDatItms WHERE accDatItms.PropertyAssetId=@ID AND accDatItms.AccountDataType=0 order by AccountCode;
Print 'After Executing stored procedur each'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
fetch next from @curse into @ID
end
close @curse;
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
select @totalRec=count(*) from #ListActualsTable;
select PropertyAssetId,ActualValuesEnd,AccountName,AccountCode,
Actuals,
DatatoRetrieveDate,NoOfMonths,@totalRec as "TotalRecords" from #ListActualsTable order by PropertyAssetId
Print 'Consolidating results'
Print CAST(GETDATE() as Datetime2 (3) )
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121))
SET @STATUS_CODE=0;
SET @STATUS_MSG='Success';
END
August 23, 2016 at 11:57 am
It looks like you can re-write your code to eliminate the cursor - that cursor is going to slow you down. I would also run this stored procedure using WITH RECOMPILE.
-- Itzik Ben-Gan 2001
September 9, 2016 at 4:09 am
For a start.
You do not need to run the same query twice:
SELECT @Count=count(*) from dbo.PropertyAssets pa
inner join Scenarios sce on sce.id = pa.scenarioId
inner join Portfolios por on por.id = sce.PortfolioId
where por.Name = 'Healthcare Real Estate Active Portfolio' and pa.Id=@PropertyAssetID
.......
SELECT @ActiveRecord=count(*) from dbo.PropertyAssets pa
inner join Scenarios sce on sce.id = pa.scenarioId
inner join Portfolios por on por.id = sce.PortfolioId
where por.Name = 'Healthcare Real Estate Active Portfolio' and (pa.status IS NULL or pa.status = '') and pa.Id=@PropertyAssetID
Do it in one go:
SELECT @Count=count(*) ,
@ActiveRecord=count(CASE WHEN (pa.status IS NULL or pa.status = '') THEN 1 ELSE NULL END )
FROM dbo.PropertyAssets pa
inner join Scenarios sce on sce.id = pa.scenarioId
inner join Portfolios por on por.id = sce.PortfolioId
where por.Name = 'Healthcare Real Estate Active Portfolio' and pa.Id=@PropertyAssetID
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply