Optimize this SP - SQL Server Forums

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

Transact-SQL (2005)

Optimize this SP

Author

Topic

grindEspresso
Starting Member

2 Posts

Posted - 2007-02-01 : 05:50:16

Hi,

I'm fairly new to stored procedures and the following SP is probably not optimally correct so I was wondering if someone could cast an eye over it and make some suggestions on how to improve it.

WHAT IT DOES:
As you can see it takes an XML file of data that comes from a flat file and puts it into 3 tables. Because the tables are related each record gets broken into 3 parts. First the Employer details are extracted and added to the Employers table (if they are not already there), the Employers ID (Identity) gets passed to Contacts as a parameter and the Contacts details are added returning the contact ID (Identity) to pass to the student details record.
This all runs from an asp.net web app. and I'm experiencing timeouts when inserting the data.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[spUpdateExcelData]
@xmlDocument NTEXT
AS

DECLARE @docHandle INT
DECLARE @EmployerID INT
DECLARE @ContactID INT
DECLARE @Err INT
DECLARE @StudentID NVARCHAR(11)
DECLARE @FirstName NVARCHAR(50)
DECLARE @Surname NVARCHAR(50)
DECLARE @StudentAddress NVARCHAR(200)
DECLARE @StudentPhone NVARCHAR(50)
DECLARE @EmployerName NVARCHAR(100)
DECLARE @EmployerAddress NVARCHAR(200)
DECLARE @EmployerContact NVARCHAR(50)
DECLARE @EmployerPhone NVARCHAR(50)
DECLARE @EmployerFax NVARCHAR(50)
DECLARE @Message NVARCHAR(500)

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
SET NOCOUNT ON

--Declare and initialise cursor
DECLARE excelCursor CURSOR FOR
SELECT * FROM OPENXML(@docHandle,N'/NewDataSet/ExcelData',2)
WITH ([StudentID] varchar(11), [FirstName] varchar(50),[Surname] varchar(50),
[StudentAddress] varchar(200), [StudentPhone] varchar(50),[EmployerName] varchar(100),
[EmployerAddress] varchar(200),[EmployerContact] varchar(50),
[EmployerPhone] varchar(50), [EmployerFax] varchar(50)) XMLExcelData

SELECT @Err=@@ERROR
IF @Err <> 0
BEGIN DEALLOCATE excelCursor RETURN @err END

--Open and check for errors
OPEN excelCursor
SELECT @err = @@ERROR
IF @err <> 0
BEGIN DEALLOCATE excelCursor RETURN @err END

WHILE 1=1
BEGIN
FETCH excelCursor INTO @StudentID, @Surname, @FirstName, @StudentAddress, @StudentPhone, @EmployerName, @EmployerAddress, @EmployerContact, @EmployerPhone, @EmployerFax
SELECT @Err = @@ERROR
IF @Err <> 0 OR @@FETCH_STATUS <> 0
BREAK
--BEGIN TRANSACTION
--Do Employer details first and return row inserted to
EXEC @Err = [dbo].[spUpdateEmployers] @EmployerName, @EmployerAddress, @EmployerPhone, @EmployerFax, @EmployerID OUTPUT
SELECT @Err = COALESCE(NULLIF(@err, 0), @@ERROR)
IF @Err <> 0 GOTO InsEmployerErr
--Add contact to the contacts table
EXEC @Err = [dbo].[spUpsKeyEmployerContacts] @EmployerID, @EmployerContact, @ContactID OUTPUT
SELECT @Err = COALESCE(NULLIF(@err, 0), @@ERROR)
IF @Err <> 0 GOTO InsContactErr
--Add Student details using contactID
EXEC @Err = [dbo].[spUpsKeyStudents] @StudentID, @FirstName, @Surname, @StudentAddress, @StudentPhone, @ContactID
SELECT @Err = COALESCE(NULLIF(@err, 0), @@ERROR)
IF @Err <> 0 GOTO InsStudentErr

--COMMIT TRANSACTION
SELECT @err = @@error
IF @err <> 0 BREAK

--Move onto the next record
CONTINUE
InsEmployerErr:
SELECT @Message = 'InsEmployerErr - ' + CONVERT(NVARCHAR, @Err)
PRINT @Message
InsContactErr:
SELECT @Message = 'InsContactErr - ' + CONVERT(NVARCHAR,@Err)
PRINT @Message
InsStudentErr:
SELECT @Message = 'InsStudentErr - ' + CONVERT(NVARCHAR,@Err)
PRINT @Message
--ROLLBACK TRANSACTION
END
DEALLOCATE excelCursor
SET NOCOUNT OFF
RETURN @Err
EXEC sp_xml_removedocument @docHandle

TIA

grindEspresso
Starting Member

2 Posts

Posted - 2007-02-01 : 06:32:38

Fixed it...no more timeouts...

What I did was run the profiler and look at all the work being done to process the file, and there was lots...so I decided to put the UPDATE and INSERT code from the Employers, Contacts and Students into the one procedure. This fixed the timeout, obviously. It looks neat and saves duplication using already existing SP's but I guess there's a trade off.

Am I on the right track ??

Time for a coffee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources