Optimize query using XML for mater detail insert.

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 query using XML for mater detail insert.

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-25 : 09:59:28

Sorry for writing a long post. I am using following query to do a master detail insert. When total number of records is around 9500, query takes around 1:45 or more sec for insert. How can I optimize this query.

ListHeader and ListDetail have identity column as primary keys.

XML Variables can have data like this
@ValidISBNList='<VALIDISBN><value>9780002154468</value><VALIDISBN>'
@InValidISBNList='<INVALIDISBN><value>9780002154468</value><INVALIDISBN>'

Declare @ValidISBN xml
Declare @InValidISBN xml
Declare @ValidISBNList xml
Declare @InValidISBNList xml
Declare @UserName varchar(45)
Declare @ReportName varchar(45)
Declare @Code varchar(50)
Declare @Description varchar(50)

BEGIN TRAN
SET ARITHABORT ON
DECLARE @UserId int
DECLARE @ListHdr_ID bigint
SELECT @UserId = ( SELECT PK_USER FROM S1USERS WHERE USERID =@UserName )
INSERT INTO LISTHEADER(ListHdr_CreateDate,ListHdr_Name,ListHdr_Code,
ListHdr_Comment,ListHdr_UserID,ListHdr_CreatedIn,ListHdr_Status,ListHdr_CreatedBy,ListHdr_CreatedOn,ListHdr_ModifiedBy,ListHdr_ModifiedOn)VALUES ( getdate(),'', @Code , @Description , @UserId , @ReportName, 'Active', @UserName, getdate(),@UserName, getdate() )

SELECT @ListHdr_ID = ( SELECT SCOPE_IDENTITY())

SELECT @ValidISBN = @ValidISBNList
INSERT INTO LISTDETAIL ListDtl_ListHdrID,ListDtl_ProductID,ListDtl_ISBN_Number)

SELECT @ListHdr_ID,P.PROD_PRODUCT_ID,ISBN.ISBNNumber FROM PRODUCT P INNER JOIN
(SELECT ParamValues.ID.value('.','VARCHAR(20)') as ISBNNumber FROM
@ValidISBN.nodes('/VALIDISBN/value') as ParamValues(ID) ) ISBN
ON P.PROD_NUMBER=ISBN.ISBNNumber

SELECT @InValidISBN = @InValidISBNList

INSERT INTO IMPORTISBNERROR(FK_ListHdr_Code,ISBN_Number,Publisher,Vendor,[Binding],
RetailCost,DeptCategory,Store,TBO_Quantity,Reason,CreatedBy)
SELECT @Code,INVALIDISBN.ISBNNumber,'','','','','','','','Invalid ISBN',@UserName
FROM ( SELECT ParamValues.ID.value('.','VARCHAR(20)') as ISBNNumber FROM
@InValidISBN.nodes('/INVALIDISBN/value') as ParamValues(ID) ) INVALIDISBN

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-26 : 12:15:17

Any reasons for no reponse to this post?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-26 : 12:44:56

If you provide a complete sample of the xml you are using along with the table structures of the tables with sample data it will be a lot easier for someone to take a look and work on improving.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-27 : 05:24:15

quote:
Originally posted by Vinnie881

If you provide a complete sample of the xml you are using along with the table structures of the tables with sample data it will be a lot easier for someone to take a look and work on improving.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

I have provided simple sample in my post. I did not post complete XML string for brevity.

@ValidISBNList='<VALIDISBN><value>9780002154468</value>><value>9780002154468</value><VALIDISBN>'
@InValidISBNList='<INVALIDISBN><value>9780002154468</value>><value>9780002154468</value><INVALIDISBN>'

Above string values contain 2 valid and 2 invalid ISBN.

To make myself more clear, I am reading a column value from XML file which is containing ISBN values in addition to other details. In my application logic, I am validating these and creating VALIDISBNLIST and INVALIDISBNLIST string (in XML format) as given above. I am passing these string values to sql command parameters.

Now, if my number of valid ISBN is 7000 and invalid is 2500,the query I am using takes between 1:45 to 2:10. I did take a look at execution plan but could not make out any thing. What I see are couple of Table Valued Function [XML] taking 92%.

Should I post XML format of my execution plan?

Any ideas to improve.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-30 : 00:37:24

Is there no improvement that can be done to increase performance? I selected this approach after searching web and every one suggested xml is better than CSV's. Not sure it is going same way for me.

Please suggest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources