Optimize/Speedup Query

Transact-SQL
1

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

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?

2

You need to cluster the table differently (on Date and AccountID, based on the queries above). ]The biggest common performance killer is automatically clustering a table on an identity column, without considering other columns. That's usually just a terrible idea for performance.]

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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]                                               
GO
CREATE UNIQUE CLUSTERED INDEX CL_BalanceTable ON dbo.BalanceTable ( Date, AccountID, AutoIndex ) WITH ( DATA_COMPRESSION = NONE, FILLFACTOR = 98, SORT_IN_TEMPDB = ON ) ON [PRIMARY];
ALTER TABLE dbo.BalanceTable ADD CONSTRAINT [PK_BalanceTable] PRIMARY KEY NONCLUSTERED
(                       
    [AutoIndex] ASC                 
)WITH ( DATA_COMPRESSION = NONE, FILLFACTOR = 99 ) ON [PRIMARY]
1 Like