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 `

Update BalanceTable  set [daily_balance].modify('
  insert 2007-05-10-852883593632  
  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 NULL,
[Date] [date] NULL,
[Balance] decimal NULL,
[TRansactionDr] decimal NULL,
[TRansactionCr] decimal NULL,
[daily_Balance] [xml] NULL,
[AutoIndex] [int] IDENTITY(1,1) NOT NULL,
C

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

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

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.

StackExchange Database Administrators Q#260520, answer score: 8