Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XMLDocuments growing bigger Expand / Collapse
Author
Message
Posted Sunday, July 28, 2013 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:12 PM
Points: 7, Visits: 223
I have a xml table in which it has 10 million records in it . Every day there will be approx 5000 files dumped into the table which is really slow. Can you suggest some best practices to improve the performance?
Post #1478379
Posted Sunday, July 28, 2013 3:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
With that many rows now it will take time, but setting up XML indexes can make a HUGE difference. Without seeing how your XML is being stored I can't really offer much more than that. But say you have an XML column that has a typical entry like this:

CREATE TABLE [dbo].[!Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Col1] [int] NULL,
[Col2] [nvarchar](50) NULL,
[Col3] [xml] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[!Test] ON
INSERT [dbo].[!Test] ([ID], [Col1], [Col2], [Col3]) VALUES (1, 2345, N'TEST', N'<code attribute="color" value="red" />')
INSERT [dbo].[!Test] ([ID], [Col1], [Col2], [Col3]) VALUES (2, 4566, N'TEST2', N'<code attribute="color" value="blue" />')
SET IDENTITY_INSERT [dbo].[!Test] OFF
use LocalTestDB
GO

ALTER TABLE [dbo].[!Test] ADD CONSTRAINT [PK_!Test] PRIMARY KEY CLUSTERED
([ID] ASC)

CREATE PRIMARY XML INDEX [IX_Test_XML]
ON [dbo].[!Test]([Col3])

CREATE XML INDEX [IX_Test_XML_Code]
ON [dbo].[!Test]([Col3])
USING XML INDEX [IX_Test_XML]
FOR PATH

CREATE XML INDEX [IX_Test_attribute]
ON [dbo].[!Test]([Col3])
USING XML INDEX [IX_Test_XML]
FOR PROPERTY

CREATE XML INDEX [IX_Test_color]
ON [dbo].[!Test]([Col3])
USING XML INDEX [IX_Test_XML]
FOR VALUE


SELECT TOP 1000 [ID]
,[Col1]
,[Col2]
,[Col3]
FROM [dbo].[!Test]


These are like any other indexes in that you may not need all three types or you may need more for additional properties. That will take some testing to see what effect the indexes have on your query plan. Test on a small chunk of that million-row table and when you have the indexes the way you want them they can be applied to the actual data.

 

 
Post #1478409
Posted Monday, July 29, 2013 7:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:12 PM
Points: 7, Visits: 223
Thanks for the reply. I thought of putting up XML Indexes, but the concern is the table is huge it will take a lot of time to create the indexes.. may be like 15-20 hours.
Post #1478600
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse