包含 xml 列的查詢的索引

我有以下查詢，執行速度有點慢。所以我打算創建索引。我創建了 primay xml 索引，現在性能有所提高（通過查看執行計劃）。查詢如下所示。

Update account set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Balance/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Balance)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

Update account  set   [dailybalance].modify('replace value of   (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] with   (/Root/Row[date=''2013-02-04'']/ Transaction)[1] +280')   where   [ID]=257 and [Date]='28-Feb-2013'  and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']')=1;

該表具有以下結構

CREATE TABLE [dbo].[account ](
   [ID] [int] NULL,
   [Type] [char](10) NULL,
   [Date] [date] NULL,
   [Balance] [decimal](15, 2) NULL,
   [TRansaction] [decimal](15, 2) NULL,
   [mybal] [decimal](15, 2) NULL,
   [dailybalance] [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]

我正在使用 sql server 2008 r2 express。所以我認為我不能使用全文搜尋。所以請告訴我應該如何創建索引，以便提高上述查詢性能。

如果您經常更新 xml，那麼請考慮屬性提升，即將 xml 轉換為關係表，正如其他人所建議的那樣。

重新設計，您是否將餘額和交易儲存在多個地方？這聽起來很危險。如果 xml 是一種歷史表，那麼它可能會更好。您還可以確保歷史表與事務中的主表同時填充，這樣它是安全的。你目前的設計對我來說並不安全。您上面的兩個更新不是在事務中完成的事實有點令人擔憂。您是否在主表和 xml 之間存在不匹配？

說了這麼多之後，我做了一個簡單的測試裝置來嘗試重現這個問題，更新在我的筆記型電腦上在一百萬行的表上執行不到一秒鐘。你能看出這個鑽機和你的查詢有什麼不同嗎？

USE tempdb
GO

IF OBJECT_ID('[dbo].[account]') IS NOT NULL DROP TABLE [dbo].[account]
GO
CREATE TABLE [dbo].[account](
   [ID] [int] NULL,
   [Type] [char](10) NULL,
   [Date] [date] NULL,
   [Balance] [decimal](15, 2) NULL,
   [TRansaction] [decimal](15, 2) NULL,
   [mybal] [decimal](15, 2) NULL,
   [dailybalance] [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


-- Generate some dummy rows
;WITH cte AS
(
SELECT TOP 1000000
   ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) AS rn
FROM master.sys.columns c1
   CROSS JOIN master.sys.columns c2
   CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.account ( ID, [TYPE], [Date], Balance, [Transaction], mybal, dailybalance )
SELECT
     rn AS ID
   , CHAR( 65 + ( (rn -1) % 6 ) ) AS [TYPE]
   , DATEADD( day, (rn -1) % 90, '1 Jan 2013' ) AS [Date]
   , RAND() * 10 * ( (rn -1) % 10 ) AS [Balance]
   , RAND() * 10 * ( (rn -1) % 10 ) AS [Transaction]
   , RAND() * 10 * ( (rn -1) % 10 ) AS [mybal]

   , ( SELECT CONVERT( CHAR(10), DATEADD( month, x.y, '1 Jan 2013' ), 120 ) AS "date", x.y AS "Balance", 1 AS "Transaction" FROM ( SELECT DISTINCT TOP 12 column_id y FROM master.sys.columns ) x FOR XML PATH('Row'), ROOT('Root'), TYPE ) [dailybalance]

FROM cte
GO


-- Create data for our condition
UPDATE a
SET [Date] = '28-Feb-2013',
   dailybalance.modify('insert 
<Row>
 <date>2013-02-04</date>
 <Balance>-1</Balance>
 <Transaction>-1</Transaction>
</Row>

 as last into Root[1]
 ')
FROM dbo.account a
WHERE [ID] = 257
GO

-- Covering index for the query
-- CREATE INDEX _idx ON dbo.account ( ID, [Date] )


SELECT 'before' s, * FROM dbo.account
WHERE [ID] = 257


UPDATE account
SET [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Balance/text())[1] 
with (/Root/Row[date=''2013-02-04'']/Balance)[1] +280')   
WHERE [ID] = 257 
 AND [Date] = '28-Feb-2013'  
 AND [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;


UPDATE account
set [dailybalance].modify('replace value of (/Root/Row[date=''2013-02-04'']/Transaction/text())[1] 
with (/Root/Row[date=''2013-02-04'']/Transaction)[1] +280')   
where [ID] = 257 
 and [Date] = '28-Feb-2013'  
 and  [dailybalance].exist('/Root/Row[date=''2013-02-04'']') = 1;


SELECT 'after' s, * FROM dbo.account
WHERE [ID] = 257

您的帳戶表中有多少行？如果您的 xml 實際上非常大，那麼這可能會解釋一些事情。是否存在其他問題，例如阻塞、觸發器或伺服器忙？查看查詢，全文索引不太可能對您有太大幫助。XML 索引可能會有所幫助，但會帶來很大的儲存損失。該表最終可能會達到其原始大小的 2-5 倍，並且您在 SQL Express 2008 R2 (10GB) 中沒有那麼多空間可以使用，因此可能再次不推薦使用。

可能有幫助的另一件事是支持更新的覆蓋索引，例如

CREATE INDEX _idx ON dbo.account ( ID, [Date] )

試試看，看看這是否有助於您的更新。如果沒有，請嘗試提供上面要求的其他資訊，我再看看。

關於全文索引：如果您擁有帶有高級服務的 SQL Server 2012（或 2008 或 2008 R2）Express（全部免費），那麼您將能夠在 Express 中創建全文索引。該下載目前可在以下位置獲得：

http://msdn.microsoft.com/en-us/evalcenter/hh230763.aspx

話雖如此，我同意使用 XML 作為傳輸機制的評論，但將數據儲存在關係表中更適合執行更高性能的搜尋。基本上你從 XML 中選擇：VALUE(‘XQueryExpression’,‘SQLType’)

SQL Server 聯機叢書中的一個範例是：

DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
 <Warranty>1 year parts and labor</Warranty>
 <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
SELECT @ProdID

如果您想要一些將 XML 分解為列的範常式式碼，請參閱marc-s 的這篇文章，它為您提供了一個可遵循的模式。 https://stackoverflow.com/questions/11179195/shredding-data-from-xml-importing-into-relational-tables-sql-server-2008

我使用 xml 粉碎來消耗 SQL Server 中的一些事件數據，但不用於任何其他目的。

引用自：https://dba.stackexchange.com/questions/47705