Mark Streutker - All Comments

re: Handling EAV-data

ensecoz — Sat, 31 Oct 2009 22:10:30 GMT

Very good information here.

Thanks for sharing.

Anyway i have some more questions.

1. Any performance issue using this technique. (if you can provide some matrix like data size, time, should be very good)

2. What if I don't use EAV-modeled but using xml to keep data instead, how can you generate report from xml?

Thanks,

re: Handling EAV-data

marks — Thu, 13 Aug 2009 06:34:28 GMT

Thats a legitimate question, it's a common practice to use views.
In the examples I've shown, I took one important dimension out of scope : time.
Mostly when you run a report, you want data between two specific points in time.
When you use a view, you usually realize this by adding a JOIN/WHERE clause on the BeginDate and EndDate columns.
This method is efficient way of selecting data, since selecting through a view supports the usage of indexes.

However, when you use the PIVOT construction I described above, adding a JOIN/WHERE clause _after_ the PIVOT won't be as efficient. The PIVOT statement will pivot all records before applying the filters, which makes it a very expensive operation. To make a PIVOT efficient, you want to delimited as much records as possible _before_ you pivot. The only way to achieve this, is by making use of variables; i.e. a startdate and enddate.
Since views don't support the usage of variables, we were bound to use functions instead.

The downside is, of course, that the resultset of a tablebased function is a table variable; so filtering the resultset of a tablebased function won't use any indexes.

re: Handling EAV-data

Robert te Kaat — Tue, 11 Aug 2009 06:46:18 GMT

Why not create views for easy reporting? I used it previously to easily query XML-data, which is a similar problem.

With views, it's the resposibility of the original designer/developer to create them and ofcourse he knows best how the underlying model works.