Performance issues with xml fields and pivots

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance issues with xml fields and pivots

Author

Topic

jgd12345
Starting Member

6 Posts

Posted - 2007-11-29 : 11:10:58

Hi, my database has the following tables:

Sections:
- SectionID (PK)
- SectionName

Documents:
- DocumentID (PK)
- SectionID (FK)
- Title

Attributes:
- AttributeID (PK)
- SectionID (FK)
- AttributeName

My site is split into sections ie News, Events etc (stored in the sections table). Each section has a few additional fields which are generated dynamically (stored in the attributes table). And finally the news and events are stored in the documents table. Now i need to find a way of storing the attribute values against each document.

My first approach was to store the values in an xml field in the documents table, ie:

<values>
<value attributeID="1">Value 1</value>
<value attributeID="2">Value 2</value>
<value attributeID="3">Value 3</value>
</values>

However i found that xml fields can be extremely slow when querying with thousands of documents. I converted the field to a text field which improved performance but i don't get the extra query support which sql sever 2005 introducted for xml fields. I also don't get the normal relationship checks i would normally get by storing the values in a seperate table.

Therefore my next approach was to create the following table:

DocumentValues:
- DocumentID (FK)
- AttributeID (FK)
- Value

I then created the following query:

CODE
SET @Query = N'SELECT Documents.*, ' + dbo.GetAttributes() + ' FROM
(
SELECT DocumentValues.DocumentLogID, Attributes.AttributeName, DocumentValues.Value
FROM Attributes INNER JOIN DocumentValues ON Attributes.AttributeID = DocumentValues.AttributeID
) p
PIVOT (
MAX([Value])
FOR AttributeName IN (' + dbo.GetAttributes() + ')
) AS DocumentValues RIGHT OUTER JOIN
Documents ON DocumentValues.DocumentID = Documents.DocumentID'

EXEC sp_executesql @Query

The GetAttributes function simply returns a comma seperated list of all the attributes.

This query pivots the values in the document values table and converts them to columns. Now i can query and sort against the attributes. But again the drawback with this approach is it's extremely slow (taking as long as 30 seconds to get 5 records from a table with 100,000 records).

So i don't know what to do next. Performance is very important in the next application i am building and it is also important that i can query and sort against some of the attribute values.

Appreciate if someone could give me some recommendations on what i could do.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-29 : 20:39:00

How are you selecting the 5 rows from the 100,000? Your query shows no WHERE clause.
As long as you have the correct indexes and are selecting based on indexed criteria it should perform much better than what you are seeing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources