6

Below query is used for inserting and updating the tables in the SQL Server database. The XQuery is slow while executing in SSMS for first time.

Query

insert new <ROW>

Update BalanceTable  set [daily_balance].modify('
  insert <Row><date>2007-05-10</date><Balance>-8528</Balance><Transactiondr>835</Transactiondr><Transactioncr>9363</Transactioncr><Rowid>2</Rowid></Row>  
  as first into (/Root)[1]') 
where [daily_balance].exist('/Root/Row[date=''2007-05-10''] ')=0 
and [daily_balance].exist('/Root')=1 
and  [AccountID]=61 
and [Date] = '31-May-2007';

modify balance

Update BalanceTable 
set   [daily_balance].modify('
  replace value of (/Root/Row[date=''2007-05-10'']/Balance/text())[1] 
  with (/Root/Row[date=''2007-05-10'']/ Balance)[1] -3510')   
where [AccountID]=577 
and [Date]='31-May-2007'  
and [daily_balance].exist('/Root/Row[date=''2007-05-10'']')=1;

modify transactioncr

Update BalanceTable 
set   [daily_balance].modify('
  replace value of (/Root/Row[date=''2007-05-10'']/Transactioncr/text())[1] 
  with (/Root/Row[date=''2007-05-10'']/ Transactioncr)[1] +3510')   
where [AccountID]=577 
and [Date]='31-May-2007'  
and  [daily_balance].exist('/Root/Row[date=''2007-05-10'']')=1;

Table schema

USE [Fitness Te WM16]                       
GO                                                              
SET ANSI_NULLS ON                       
GO                                              
SET QUOTED_IDENTIFIER ON                        
GO                                              
SET ANSI_PADDING ON                     
GO                                              
CREATE TABLE [dbo].[BalanceTable](                      
    [AccountID] [int] NULL,                 
    [Type] [char](10) NULL,                 
    [Date] [date] NULL,                 
    [Balance] [decimal](15, 2) NULL,                    
    [TRansactionDr] [decimal](15, 2) NULL,                  
    [TRansactionCr] [decimal](15, 2) NULL,                  
    [daily_Balance] [xml] NULL,                 
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,                   
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED                         
(                       
    [AutoIndex] ASC                 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]                       
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]                                               
GO                                              
SET ANSI_PADDING OFF                        
GO  

Execution plan

The execution plan is attached here sql execution plan

Sample data

The sample XML data for reference is given below.

<Root>              
      <Row>             
        <date>2007-05-31</date>             
        <Balance>-47718</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>47718</Transactioncr>                
        <Rowid>7</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-29</date>             
        <Balance>-31272</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>31272</Transactioncr>                
        <Rowid>6</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-18</date>             
        <Balance>-48234</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>48234</Transactioncr>                
        <Rowid>5</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-11</date>             
        <Balance>-42120</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>42120</Transactioncr>                
        <Rowid>4</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-10</date>             
        <Balance>-21060</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>21060</Transactioncr>                
        <Rowid>3</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-08</date>             
        <Balance>-10530</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>10530</Transactioncr>                
        <Rowid>2</Rowid>                
      </Row>                
      <Row>             
        <date>2007-05-04</date>             
        <Balance>-21060</Balance>               
        <Transactiondr>0</Transactiondr>                
        <Transactioncr>21060</Transactioncr>                
        <Rowid>1</Rowid>                
      </Row>                
      <Maxrowid>7</Maxrowid>                
    </Root> 

Question

I am using SQL Server 2008 R2. The total time taken for 500 queries is 20 to 40 seconds. How can I optimise this query to speed up the execution?

  • Can you please add some details. Is field [daily_Balance] in the table [BalanceTable] consequence of other fields? I mean what is suppose to do with xml example? – QuickJoe Mar 11 at 11:56
8
+25

I don't think there is anything you can do to optimize the XML_DML statements.

But you can do something about the exists check in the where clauses.

It is better to fully traverse down to the value you are checking before the predicate.

So instead of .exist('/Root/Row[date=''2007-05-10'']') you could do .exist('/Root/Row/date/text()[.=''2007-05-10'']').

Query plan for your version of exists that executes in 173 milliseconds over 3200 rows on my computer.

select count(*)
from dbo.BalanceTable
where [daily_balance].exist('/Root/Row[date=''2007-05-10'']')=1

enter image description here

Query plan for the modified version that executes in 36 milliseconds.

select count(*)
from dbo.BalanceTable
where [daily_balance].exist('/Root/Row/date/text()[.=''2007-05-10'']')=1

enter image description here

I don't expect the impact of this change will be that dramatic with your update statements since there are a lot more going on there that also takes time. In tests I did with the update statements I saw duration drop about 30%. You have to test on your data to see how much this affects your performance.

| improve this answer | |
3

I'd try and specify your xml format in the column by creating a XML schema, this should help you with the numerous calls as you're describing as you're currently parsing untyped XML.

You can check your current defined schema collections with the following query:

SELECT * FROM sys.xml_schema_collections AS XML1
INNER JOIN sys.xml_schema_elements AS XML2
    ON XML1.xml_collection_id = XML2.xml_collection_id

To createa a new one for your provided sample data this should look something along the lines of:

CREATE XML SCHEMA COLLECTION BalanceXMLSchema AS
'<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Root">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Row" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:date" name="date"/>
              <xs:element type="xs:decimal" name="Balance"/>
              <xs:element type="xs:decimal" name="Transactiondr"/>
              <xs:element type="xs:decimal" name="Transactioncr"/>
              <xs:element type="xs:int" name="Rowid"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element type="xs:int" name="Maxrowid"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
'

--And then use it in your table creation                                           
CREATE TABLE [dbo].[BalanceTable](                      
    [AccountID] [int] NULL,                 
    [Type] [char](10) NULL,                 
    [Date] [date] NULL,                 
    [Balance] [decimal](15, 2) NULL,                    
    [TRansactionDr] [decimal](15, 2) NULL,                  
    [TRansactionCr] [decimal](15, 2) NULL,                  
    [daily_Balance] [xml](BalanceXMLSchema) NULL, --By specifying it in the XML column definition              
    [AutoIndex] [int] IDENTITY(1,1) NOT NULL,                   
 CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED                         
(                       
    [AutoIndex] ASC                 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]                       
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]                                               
GO 

Of note, i took the datatypes from your table definition. Because i can't say for certain based on your XML/true values. So you could fiddle with them. Above code is tested to work for creation of the table.

You can also try and implement (probably secondary) XML indexes to speed up your filtering.

Links:

MSDN XML Schema

MSDN Typed VS Untyped XML

MSDN XML Indexes

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.