Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 XML and Memory

Author  Topic 

sqldba20
Posting Yak Master

183 Posts
Posted - 2007-09-21 : 11:15:59
Folks:

I am having a stored procedure which is taking a little longer and using more memory. The only bottle neck I see is, I store a XML data in a variable and then it does lots of other stuff. Is there a way to deallocate the memory from the variable after a point or will it release the memory after the point I set the variable to NULL or '' ?


Thanks !

sqldba20
Posting Yak Master

183 Posts
Posted - 2007-09-21 : 11:37:14
Here is the Stored Procedure:

CREATE PROCEDURE [dbo].[uspAbsHeqAnalyticOutputImport](
@AnalyticJobIdStr VARCHAR(50),
@Error_uspAbsHeqAnalyticOutputImport_Entry XML OUTPUT
)
AS
BEGIN


DECLARE @AnalyticJobId INT
SET @AnalyticJobId = CAST(@AnalyticJobIdStr AS INT)

CREATE TABLE #tempResultsXml(id INT IDENTITY(1,1), -- ResultXML XML,
AnalyticJobEntryOrdinal INT, DigipedeJobId INT, DigipedeTaskId INT, DigipedePoolId INT, SubmissionTime DATETIME, SolveFor VARCHAR(50),
ScenarioName VARCHAR(250), SecurityXML XML,
--SimulatedBondLosses XML, SimulatedPresentValues XML, ModelArrays XML, Cashflows XML,
ScenarioParametersKey VARCHAR(250),AnaltyicEngineId INT)


/*----------------------------------------------------------------------*/
/*---------------START Grabbing the Output from the AnalyticServiceDB.dbo.tblAnalyticPersistence On Digipede Server------------------*/
/*----------------------------------------------------------------------*/
DECLARE @ResultXML XML


DECLARE @SqlString NVARCHAR(MAX)
CREATE TABLE #ResultXMLTable (ResultXML XML)

SET @SqlString =
'SELECT * FROM OPENQUERY (SBARADIGI,''
SELECT (SELECT (
SELECT
AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
UpdateDate AS UpdateDate,
MetaData,
[Entry]
FROM AnalyticServiceDB.dbo.tblAnalyticPersistence
WHERE MetaData.exist (''''//SbaAnalyticPersistence[EntryDirection="Output" or IsAnalyticResult="true"]'''') = 1
AND AnalyticJobId = '+@AnalyticJobIdStr+' AND AnalyticJobEntryOrdinal <> 0
FOR XML PATH (''''ResultXML''''),type)
FOR XML PATH (''''Results''''))'')'


INSERT #ResultXMLTable
EXEC(@SqlString)

SET @ResultXML =
(SELECT ResultXML FROM #ResultXMLTable)
DROP TABLE #ResultXMLTable



/*----------------------------------------------------------------------*/
/*----------------END Grabbing the Output from the Analytic Jobs-------------------*/
/*----------------------------------------------------------------------*/

DECLARE @AnalyticEngineId INT
DECLARE @AnalyticEngineName VARCHAR(50)

SET @AnalyticEngineName = (SELECT DISTINCT ResultXMLSecurity.SecurityNode.value('(./AnalyticEngineVersion)[1]','VARCHAR(50)')
FROM @ResultXML.nodes('./Results/ResultXML/Entry/Security') ResultXMLSecurity(SecurityNode))

EXECUTE [dbo].[uspAnalylticServiceDbGetAnalyticEngineIdFromEngineName] @AnalyticEngineName,
@AnalyticEngineId OUTPUT


INSERT #tempResultsXml
SELECT --ResultsNodes.ResultXML.query('.'),
ResultsNodes.ResultXML.value('(./AnalyticJobEntryOrdinal)[1]','INT'),
ResultXMLPersistence.PersistenceNode.value('(./DigipedeJobId)[1]','INT'),
ResultXMLPersistence.PersistenceNode.value('(./DigipedeTaskId)[1]','INT'),
ResultXMLPersistence.PersistenceNode.value('(./DigipedePoolId)[1]','INT'),
ResultXMLPersistence.PersistenceNode.value('(./SubmissionTime)[1]','DATETIME'),
ResultXMLSecurity.SecurityNode.value('(./SolveFor)[1]','VARCHAR(50)'),
ResultXMLSecurity.SecurityNode.value('(./ScenarioName)[1]','VARCHAR(50)'),
ResultXMLSecurity.SecurityNode.query('.'),
CASE ResultXMLSecurity.SecurityNode.value('(./SolveFor)[1]','VARCHAR(500)') WHEN 'Price' THEN
'PrepayMultiplier='+CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./PrepayMultiplier)[1]','FLOAT')) AS VARCHAR(50))+
'/DefaultMultiplier='+CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./DefaultMultiplier)[1]','FLOAT')) AS VARCHAR(50))+'/SeverityMultiplier='+
CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./SeverityMultiplier)[1]','FLOAT')) AS VARCHAR(50))+'/SolveFor='+ResultXMLSecurity.SecurityNode.value('(./SolveFor)[1]','VARCHAR(500)')+'/OAS='+CAST(ROUND(ResultXMLSecurity.SecurityNode.v
alue('(./OAS)[1]','FLOAT')*10000.0,0) AS VARCHAR(50))+'bps'
WHEN 'Spread' THEN
'PrepayMultiplier='+CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./PrepayMultiplier)[1]','FLOAT')) AS VARCHAR(50))+
'/DefaultMultiplier='+CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./DefaultMultiplier)[1]','FLOAT')) AS VARCHAR(50))+'/SeverityMultiplier='+
CAST(CONVERT(DECIMAL(6,3),ResultXMLSecurity.SecurityNode.value('(./SeverityMultiplier)[1]','FLOAT')) AS VARCHAR(50))+'/SolveFor='+ResultXMLSecurity.SecurityNode.value('(./SolveFor)[1]','VARCHAR(500)')+'/MarketPrice='+CAST(CONVERT(Decimal(6,3),ResultXML
Security.SecurityNode.value('(./MarketPrice)[1]','FLOAT')) AS VARCHAR(50))
END,
@AnalyticEngineId
FROM @ResultXML.nodes('./Results/ResultXML') ResultsNodes(ResultXML)
CROSS APPLY
ResultsNodes.ResultXML.nodes('./MetaData/SbaAnalyticPersistence') ResultXMLPersistence(PersistenceNode)
CROSS APPLY
ResultsNodes.ResultXML.nodes('./Entry/Security') ResultXMLSecurity(SecurityNode)

SET @ResultXML = ''

/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticScenarioNameType Update*/
DECLARE @AbsHeqAnalyticOutputScenarioNameTypeEntries XML
DECLARE @Error_tblAbsHeqAnalyticScenarioNameType_Update XML

SET @AbsHeqAnalyticOutputScenarioNameTypeEntries =
(SELECT
(SELECT DISTINCT
ScenarioName AS ScenarioNameLabel
FROM #tempResultsXml
FOR XML PATH('ScenarioName'),type)
FOR XML PATH('AbsHeqAnalyticOutputScenarioNameTypeEntries'))

EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticScenarioNameType_Entry] @AbsHeqAnalyticOutputScenarioNameTypeEntries,
@Error_tblAbsHeqAnalyticScenarioNameType_Update OUTPUT
/*End tblAbsHeqAnalyticScenarioNameType Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticScenarioParametersKeyType Update*/
DECLARE @AbsHeqAnalyticOutputScenarioParametersKeyTypeEntries XML
DECLARE @Error_tblAbsHeqAnalyticScenarioParametersKeyType_Update XML

SET @AbsHeqAnalyticOutputScenarioParametersKeyTypeEntries =
(SELECT
(SELECT DISTINCT
ScenarioParametersKey AS ScenarioParametersKeyLabel
FROM #tempResultsXml
FOR XML PATH('ScenarioParametersKey'),type)
FOR XML PATH('AbsHeqAnalyticOutputScenarioParametersKeyTypeEntries'))


EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticScenarioParametersKeyType_Entry] @AbsHeqAnalyticOutputScenarioParametersKeyTypeEntries,
@Error_tblAbsHeqAnalyticScenarioParametersKeyType_Update OUTPUT
/*End tblAbsHeqAnalyticScenarioParametersKeyType Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputMain Update*/
DECLARE @AbsHeqAnalyticOutputMainImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputMain_Update XML

SET @AbsHeqAnalyticOutputMainImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputMainEntries'))


EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputMain_Entry] @AbsHeqAnalyticOutputMainImportEntries,
@Error_tblAbsHeqAnalyticOutputMain_Update OUTPUT

/*End tblAbsHeqAnalyticOutputMain Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputBaseCasePVs Update*/
DECLARE @AbsHeqAnalyticOutputBaseCasePVsImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputBaseCasePVs_Update XML

SET @AbsHeqAnalyticOutputBaseCasePVsImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputBaseCasePVsEntries'))

EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputBaseCasePVs_Entry] @AbsHeqAnalyticOutputBaseCasePVsImportEntries,
@Error_tblAbsHeqAnalyticOutputBaseCasePVs_Update OUTPUT
/*End tblAbsHeqAnalyticOutputBaseCasePVs Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputBaseCaseLosses Update*/
DECLARE @AbsHeqAnalyticOutputBaseCaseLossesImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputBaseCaseLosses_Update XML

SET @AbsHeqAnalyticOutputBaseCaseLossesImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputBaseCaseLossesEntries'))

EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputBaseCaseLosses_Entry] @AbsHeqAnalyticOutputBaseCaseLossesImportEntries,
@Error_tblAbsHeqAnalyticOutputBaseCaseLosses_Update OUTPUT

/*End tblAbsHeqAnalyticOutputBaseCaseLosses Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputModelArraysFlt Update*/
DECLARE @AbsHeqAnalyticOutputModelArraysFltImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputModelArraysFlt_Update XML

SET @AbsHeqAnalyticOutputModelArraysFltImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputModelArraysFltEntries'))


EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputModelArraysFlt_Entry] @AbsHeqAnalyticOutputModelArraysFltImportEntries,
@Error_tblAbsHeqAnalyticOutputModelArraysFlt_Update OUTPUT
/*End tblAbsHeqAnalyticOutputModelArraysFlt Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputModelArraysFxd Update*/
DECLARE @AbsHeqAnalyticOutputModelArraysFxdImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputModelArraysFxd_Update XML

SET @AbsHeqAnalyticOutputModelArraysFxdImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputModelArraysFxdEntries'))

EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputModelArraysFxd_Entry] @AbsHeqAnalyticOutputModelArraysFxdImportEntries,
@Error_tblAbsHeqAnalyticOutputModelArraysFxd_Update OUTPUT
/*End tblAbsHeqAnalyticOutputModelArraysFxd Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*tblAbsHeqAnalyticOutputCashFlows Update*/
DECLARE @AbsHeqAnalyticOutputCashFlowsImportEntries XML
DECLARE @Error_tblAbsHeqAnalyticOutputCashFlows_Update XML

SET @AbsHeqAnalyticOutputCashFlowsImportEntries =
(SELECT
(SELECT @AnalyticJobId AS AnalyticJobId,
AnalyticJobEntryOrdinal AS AnalyticJobEntryOrdinal,
DigipedeJobId AS DigipedeJobId,
DigipedeTaskId AS DigipedeTaskId,
SubmissionTime AS SubmissionTime,
ScenarioParametersKey AS ScenarioParametersKey,
AnalyticEngineId AS AnalyticEngineId,
SecurityXml
FROM #tempResultsXml
FOR XML PATH ('HeqAnalyticRun'), type)
FOR XML PATH ('HeqAnalyticOutputCashFlowsEntries'))

EXECUTE [dbo].[uspCreate_tblAbsHeqAnalyticOutputCashFlows_Entry] @AbsHeqAnalyticOutputCashFlowsImportEntries,
@Error_tblAbsHeqAnalyticOutputCashFlows_Update OUTPUT
/*End tblAbsHeqAnalyticOutputCashFlows Update*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/
/*-----------------------------------------------------------*/


DROP TABLE #tempResultsXml

SET @Error_uspAbsHeqAnalyticOutputImport_Entry =
(SELECT
(SELECT
@AnalyticJobId AS AnalyticJobId,
@Error_tblAbsHeqAnalyticScenarioNameType_Update,
@Error_tblAbsHeqAnalyticScenarioParametersKeyType_Update,
@Error_tblAbsHeqAnalyticOutputMain_Update,
@Error_tblAbsHeqAnalyticOutputBaseCasePVs_Update,
@Error_tblAbsHeqAnalyticOutputBaseCaseLosses_Update,
@Error_tblAbsHeqAnalyticOutputModelArraysFlt_Update,
@Error_tblAbsHeqAnalyticOutputModelArraysFxd_Update,
@Error_tblAbsHeqAnalyticOutputCashFlows_Update
FOR XML PATH ('AnalyticJobIdEntry'),type)
FOR XML PATH ('AnalyticJobIdImport'))

--SELECT @Error_uspAbsHeqAnalyticOutputImport_Entry

RETURN
END
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site