SQLTeam.com | Weblogs | Forums

Optimize/Speedup Query

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?

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]