Most performant way to make pivot table available

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

General SQL Server Forums

Database Design and Application Architecture

Most performant way to make pivot table available

Author

Topic

schmidtg
Starting Member

36 Posts

Posted - 2007-06-27 : 13:22:02

We have what I think is a pretty common setup for records with a dynamic set of descriptive fields. Something like:

People
PersonID PersonType
1 Consultant
2 Partner

PeopleFieldDefs
FieldDefID FieldName
1 FirstName
2 LastName

PeopleFields
PersonID FieldDefID FieldValue
1 1 John
1 2 Smith
2 1 Alice
2 2 Johnson

Of course, we need to be able to search and display this data in a tabular format like:
PersonID PersonType FirstName LastName
1 Consultant John Smith
2 Partner Alice Johnson

We have been building dynamic queries based on which fields are needed (users can select the fields), e.g.:

SELECT p.PersonID, p.PersonType, pf1.FieldValue AS 'First Name', pf2.FieldValue AS 'Last Name'
FROM People p
LEFT JOIN PeopleFields pf1 ON (p.PersonID=pf1.PersonID AND pf1.FieldDefID=1)
LEFT JOIN PeopleFields pf2 ON (p.PersonID=pf2.PersonID AND pf2.FieldDefID=2)

This is very flexible but slow. We've done lots of optimization but can't get this below 5 seconds for common scenarios.

So I'm back to the drawing board now trying to figure out a better way to approach this.
I'm wondering if it would be better (if even possible) to break this out into some sort of view or table UDF that would contain a full representation of all person data, pre-joined. Problem is, this is almost certainly going to have to involve dynamic SQL since we can't know anything about what fields are defined. I think that rules out any sort of view or table UDF, no?

Does anyone have a suggestion for a good approach? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-27 : 14:46:44

[code]SELECT p.PersonID,
p.PersonType,
MAX(CASE WHEN pf.FieldDefID = 1 THEN pf.FieldValue END) AS FirstName,
MAX(CASE WHEN pf.FieldDefID = 2 THEN pf.FieldValue END) AS LastName
FROM People AS p
INNER JOIN PeopleFields AS pf ON pf.PersonID = p.PersonID
GROUP BY p.PersonID,
p.PersonType
ORDER BY p.PersonID,
p.PersonType[/code]
Peter Larsson
Helsingborg, Sweden

schmidtg
Starting Member

36 Posts

Posted - 2007-06-28 : 13:31:29

Unfortunately, that doesn't seem to be any faster.

I guess I'm hoping there might be some options besides a stored procedure to get at this data. Something like a view that would let me join all associated data in a tabular format ahead of time for easy, fast searching. Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 14:16:54

Check the execution plans. Also look at the profiler the duration and number of reads.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 14:37:57

My method only requires 9 reads versus your 15. Almost half the reads required only.

Here is your execution plan

  |--Nested Loops(Left Outer Join, WHERE:([tempdb].[dbo].[#People].[PersonID] as [p].[PersonID]=[tempdb].[dbo].[#PeopleFields].[PersonID] as [pf2].[PersonID]))
       |--Nested Loops(Left Outer Join, WHERE:([tempdb].[dbo].[#People].[PersonID] as [p].[PersonID]=[tempdb].[dbo].[#PeopleFields].[PersonID] as [pf1].[PersonID]))
       |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#People] AS [p]), ORDERED FORWARD)
       |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#PeopleFields] AS [pf1]), WHERE:([tempdb].[dbo].[#PeopleFields].[FieldDefID] as [pf1].[FieldDefID]=(1)))
       |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#PeopleFields] AS [pf2]), WHERE:([tempdb].[dbo].[#PeopleFields].[FieldDefID] as [pf2].[FieldDefID]=(2)))

Here is mine

  |--Nested Loops(Inner Join, OUTER REFERENCES:([pf].[PersonID]))
       |--Stream Aggregate(GROUP BY:([pf].[PersonID]) DEFINE:([Expr1004]=MAX([Expr1006]), [Expr1005]=MAX([Expr1007])))
       |    |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [tempdb].[dbo].[#PeopleFields].[FieldDefID] as [pf].[FieldDefID]=(1) THEN [tempdb].[dbo].[#PeopleFields].[FieldValue] as [pf].[FieldValue] ELSE NULL END, [Expr1007]=CASE WHEN [tempdb].[dbo].[#PeopleFields].[FieldDefID] as [pf].[FieldDefID]=(2) THEN [tempdb].[dbo].[#PeopleFields].[FieldValue] as [pf].[FieldValue] ELSE NULL END))
       |         |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#PeopleFields] AS [pf]), ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#People] AS [p]), SEEK:([p].[PersonID]=[tempdb].[dbo].[#PeopleFields].[PersonID] as [pf].[PersonID]) ORDERED FORWARD)

And here is the sample code

CREATE TABLE	#People (PersonID TINYINT, PersonType VARCHAR(10))

INSERT	#People
SELECT	1, 'Consultant' UNION ALL
SELECT	2, 'Partner'

CREATE UNIQUE CLUSTERED INDEX IX_People ON #People (PersonID)

CREATE TABLE	#PeopleFields (PersonID TINYINT, FieldDefID TINYINT, FieldValue VARCHAR(7))

INSERT	#PeopleFields
SELECT	1, 3, 'X' UNION ALL
SELECT	2, 3, 'Y' UNION ALL
SELECT	1, 1, 'John' UNION ALL
SELECT	1, 2, 'Smith' UNION ALL
SELECT	2, 1, 'Alice' UNION ALL
SELECT	2, 2, 'Johnson'

CREATE UNIQUE CLUSTERED INDEX IX_PeopleFields ON #PeopleFields (PersonID, FieldDefID)

SELECT		p.PersonID,
		p.PersonType,
		pf1.FieldValue AS 'First Name',
		pf2.FieldValue AS 'Last Name'
FROM		#People p 
LEFT JOIN	#PeopleFields pf1 ON p.PersonID = pf1.PersonID AND pf1.FieldDefID = 1
LEFT JOIN	#PeopleFields pf2 ON p.PersonID = pf2.PersonID AND pf2.FieldDefID = 2
ORDER BY	p.PersonID,
		p.PersonType

SELECT		p.PersonID,
		p.PersonType,
		MAX(CASE WHEN pf.FieldDefID = 1 THEN pf.FieldValue END) AS FirstName,
		MAX(CASE WHEN pf.FieldDefID = 2 THEN pf.FieldValue END) AS LastName
FROM		#People AS p
INNER JOIN	#PeopleFields AS pf ON pf.PersonID = p.PersonID
GROUP BY	p.PersonID,
		p.PersonType
ORDER BY	p.PersonID,
		p.PersonType
		
DROP TABLE	#People,
		#PeopleFields

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources