Optimise/ speedup query

Question

Optimise/ speedup query

SSCertifiable

Points: 7606

March 10, 2020 at 8:54 am

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.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?

Query

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';   

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;

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 https://www.brentozar.com/pastetheplan/?id=rkwgD7U4L

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>

Viewing 2 posts - 1 through 1 (of 1 total)

The topic ‘Optimise/ speedup query’ is closed to new replies.

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

Closing this as it's a duplicate of https://www.sqlservercentral.com/forums/topic/optimise-speedup-query

Thomas Rushton
blog: https://thelonedba.wordpress.com