SQL: Temp Tables vs View

Folks...

I have created a DTSX package for a requirement which fetched data from MS access and store it in SQL Server.For business calculations there we some Views required. The final stored procedure will insert data into a table which will be utilized by a ERP system. The insert query was a bit complex like it called fields in a View which was created from a view (can call it a nested view?) & so on... The execution time was more than 30 mins :( So we need to optimize things for better performance....

now my next attempt will be to try it out with temp tables. [drafting post...]

Yes... In place of the views I used two temporary tables and one view i kept it as it is because, It was only displaying distinct values from a table.

This time to complete execution the Stored procedure took only 34.031 seconds...

Wow... that's a huge difference.... I believe using Temp tables optimized the stored procedure than views. The point to be noted here is that the decision to go for View & Temporary tables is based on the scenario... In my case Temp tables did the trick :)

Best Regards
Fauzi

Sample to use XML in Stored Procedure

Hi,

Here is a sample to use XML string as a input to a Stored procedure. The stored procedure uses SP sp_xml_preparedocument. Which is capable of reading a XML string. It parses the XML and provides a parsed document which could be used like a temporary table in run time.

Following is the simple code:

1. SQL Script to Create Table:

/****** Object: Table [dbo].[Fund_Index_Relationship] Script Date: 05/24/2009 12:21:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Fund_Index_Relationship](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Fund_ID] [int] NULL,

[FundIndex_ID] [int] NOT NULL,

CONSTRAINT [PK_Fund_Index_Relationship] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. Stored Procedure:

-- =============================================

-- Author: Fauzi

-- Create date: 180808

-- Description: TO update Relationship between Fund & Index

-- =============================================

CREATE PROCEDURE [dbo].[XML_SP_Sample]

@xmlString TEXT,

@Fund_ID int,

@Output VARCHAR(1000) OUTPUT

AS

BEGIN

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

-- Insert Section

INSERT into [Fund_Index_Relationship]

(

Fund_ID,

FundIndex_ID

)

SELECT

Fund_ID,

FundIndex_ID

FROM OPENXML (@idoc, '/Items/Item',1)

WITH (

Fund_ID int './Fund_ID',

FundIndex_ID int './FundIndex_ID'

)

END

3. XML string to Test SP:

[XML_SP_Sample] '<Items><Item><Fund_ID>34</Fund_ID><FundIndex_ID>2</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>3</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>4</FundIndex_ID></Item><Item><Fund_ID>34</Fund_ID><FundIndex_ID>5</FundIndex_ID></Item></Items>'

Hope it Helps :)

Regards

Fauzi

SQL SP without Cursors for Row by Row Operations

Hi...

As everyone now's we can use Cursors for row-by-row operations in SQL Stored procedures. The drawback of using this option is Performance. So to overcome the issue we can write a work around code to implement row-by-row operations in your SQL stored procedure. Following are the code written for this with appropriate comments to explain its operation.

BEGIN

-- Here we Initialize variables

DECLARE @iNextRowId int,@iCurrentRowId int,@iLoopControl int,@FundID int, @FundIndexId int

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(ID)FROM Fund_Index_Relationship Where Fund_ID = @ID

-- Retrieve 1st row

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId AND Fund_ID = @ID,

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- Loop is started

WHILE @iLoopControl = 1

BEGIN

SELECT @iNextRowId = NULL

-- Getting next ID

SELECT @iNextRowId = MIN(ID)

FROM Fund_Index_Relationship

WHERE Fund_ID = @ID AND ID > @iCurrentRowId

-- check whether there is a ID next

IF ISNULL(@iNextRowId,0) = 0

BEGIN

BREAK

END

-- Your Code Logic Starts

-- Code...

-- Your Code Logic End

-- get the next row.

SELECT @iCurrentRowId = ID,

@FundID = @ID,

@FundIndexId = FundIndex_ID

FROM Fund_Index_Relationship

WHERE ID = @iNextRowId

END

END