October 12, 2011 at 9:43 am
Hi Guys
I seek your opinion or any suggestions
Below is a copy of a insert in SQL Server 2005 from a XML Layout :
InstrumentCode,InstrumentDesc,CurrencyCode,CurrencyDesc,MovementType,
TransactionType,TransactionQuantity,TransactionTradeRate,CostOfSale,TransactionConversionRate,
TransactionAmount,GLMarginAccount,GLAccno,GLType,
CompanyCode,LocationCode,BranchCode,TransactionDate,
AffectsTraderFloat,BalancingID, AccPac_ClearingAC,AccPac_Module,SurrenderingPercentage)
SELECT TransactionNumber,InventoryItem,InventoryDesc,
InstrumentCode,InstrumentDesc,CurrencyCode,CurrencyDesc,MovementType,
TransactionType,TransactionQuantity,TransactionTradeRate,CostOfSale,TransactionConversionRate,
TransactionAmount,GLMarginAccount,GLAccno,GLType,
CompanyCode,LocationCode,BranchCode,TransactionDate,
AffectsTraderFloat,BalancingID, AccPac_ClearingAC,AccPac_Module,SurrenderingPercentage
FROM OpenXML(@XMLHandler,'/VFPData/TradeTransactionsDetails',1)
WITH(TransactionNumberint'@TransactionNumber',
InventoryItemint'@InventoryItem',
InventoryDescvarchar(25)'@InventoryDesc',
InstrumentCodeint'@InstrumentCode',
InstrumentDescvarchar(50)'@InstrumentDesc',
CurrencyCodeint'@CurrencyCode',
CurrencyDescvarchar(5)'@CurrencyDesc',
MovementTypevarchar(10)'@MovementType',
TransactionTypevarchar(10)'@TransactionType',
TransactionQuantityfloat'@TransactionQuantity',
TransactionTradeRatefloat'@TransactionTradeRate',
CostOfSalefloat'@CostOfSale',
TransactionConversionRatefloat'@TransactionConversionRate',
TransactionAmountfloat'@TransactionAmount',
GLMarginAccountvarchar(50)'@GLMarginAccount',
GLAccnovarchar(50)'@GLAccno',
GLTypevarchar(2)'@GLType',
CompanyCodeint'@CompanyCode',
LocationCodeint'@LocationCode',
BranchCodeint'@BranchCode',
TransactionDatedatetime'@TransactionDate',
AffectsTraderFloatbit'@AffectsTraderFloat',
BalancingIDint'@BalancingID',
AccPac_ClearingACvarchar(30)'@AccPac_ClearingAC',
AccPac_Modulevarchar(2)'@AccPac_Module',
SurrenderingPercentageFloat'@SurrenderingPercentage') TD
WHERE RTRIM(STR(TD.TransactionNumber)) + RTRIM(LTRIM(TD.TransactionType))+LTRIM(STR(TransactionQuantity))
NOT IN (SELECT RTRIM(STR(TransactionNumber)) + RTRIM(LTRIM(TransactionType))+LTRIM(STR(TransactionQuantity)) FROM TradeTransactionsDetails )
IF (@@Error<>0)
BEGIN
ROLLBACK TRANSACTION
EXEC sp_xml_removedocument @XMLHandler
RETURN(-1)
END
The problem is that it executes really slow example inserting 1646 rows take 30 minutes.
The table has no primary key or Identity column could this be the problem
Thanks in advance
October 12, 2011 at 10:30 am
I doubt very much is the last of a primary key is the issue and the lack of an identity column certainly isn't the issue.
Working with XML in SQL Server is very slow so you might be better off using SSIS to parse the XML and insert it into SQL Server over doing it all in T-SQL.
If you have to go this way I'd look at a few things:
Are there any FK's defined that are being validated at insert?
Check the execution plan and look for the bottleneck.
Take a snapshot of wait stats before running the query and a snapshot after to see what SQL is waiting on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 12, 2011 at 10:53 am
Jack Corbett
I appreciate your response. I do have 2 questions
1. How do i check the execution plan t5o look at the bottleneck
2. What do you mean by the wait stats and what am i looking for
Thanks in advance
October 12, 2011 at 11:29 am
I found that XQuery tends to be slow... so when I've had to parse XML I used OPENXML and the edge tables to parse it... it's basically a 1 time performance hit when it reads the XML into the edge tables. I will warn you, it can be very complex to work with the edge tables.
October 12, 2011 at 11:59 am
kurtlgreen (10/12/2011)
Jack CorbettI appreciate your response. I do have 2 questions
1. How do i check the execution plan t5o look at the bottleneck
2. What do you mean by the wait stats and what am i looking for
Thanks in advance
1. Check out this article, http://www.mssqltips.com/sqlservertutorial/2250/graphical-query-plan-tutorial/
2. SQL Server tracks wait it is waiting for when it runs. You can check out this post for a bit of a description, http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply