Performance issues with xml fields and pivots 1

[jgd1234567]

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:

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

 

[jgd1234567]

Anyone? Sorry for being impatient but i have a tight deadline.

 

[SQLSister]

pivoting in the user interface is often faster. Replace get_attributes with with inline SQl, functions are notorious performance hogs.

Also if speed is important to you stop using subqueries and functions and dynamic SQl. (Also cursors if you also use those in other things)

"NOTHING is more important in a database than integrity." ESquared

 

[jgd1234567]

Hi, cheers for your advice. I don't think i can get around the sub query problem but i will take that into consideration in the future. I removed the function and simply replaced it with the attributes i wanted and it dramatically improved the performance. Thanks for your help.

 

[SQLSister]

yeah functions are nice for code reuse but are bad for performance


"NOTHING is more important in a database than integrity." ESquared