September 1, 2006 at 9:18 am
Hi all,
If anybody can come up with a solution to the problem described I would be most grateful...
We have a database table structure as follows:
Workflow table
|
/|\
Stage table
|
/|\
Element table
|
Element template table
The table structire is unique in that the elements are in fact dynamic columns of each stage, which in turn belong to the workflow. This design is fixed and cannot be cahnged as it is the core part of the system. It was designed this way so that a work can be constructed of any number/type of elements. There are over 500 different types. These are listed in the element template table.
Now the problem. I have a requirement to generate a report from this structure which must be able to select all of the workflows whose elements match 10 different criteria.
If these were columns it would be:
column a = x
column b = x
column c = x etc.... and only the rows that match all the criteria would be returned.
Because of the dynamic nature of the structure all the columns are effectively rows in the element table. Of which any given workflow can have 100 rows. I need to find all the workflows that have say:
row1.code = x and row1.value = x
row2.code = x and row2.value = x
row3.code = x and row3.value = x
I have worked out one way to do this. The sql is a follows...
select *
from dat_workflow_instance wi,
dat_stage_instance si,
dat_element_instance ei,
dat_element_template et,
dat_element_instance ei2,
dat_element_template et2,
dat_element_instance ei3,
dat_element_template et3
where wi.id = si.workflow_id
and si.id = ei.stage_id
and ei.template_id = et.id
and si.id = ei2.stage_id
and ei2.template_id = et2.id
and si.id = ei3.stage_id
and ei3.template_id = et3.id
and et.code = 'GI11' and ei.value = 'MARKET'
and et2.code = 'G12' and ei2.value < '24/08/2006'
and et3.code = 'DATE5' and ei3.value = '24/08/2006'
.....................
and so on adding a new instance & template table for each new criteria. The problem being that performance will suck as the element table can have millions of rows of data.....
One alternative could be to create a temporary table that has 500 columns width so as to flat structure all the possible rows into columns, but this seems to be an ugly solution as well...
Can anyone come up with a way of achieving this??? Im at a loss....
September 1, 2006 at 1:35 pm
Your description is still fuzzy, and you have no sample data, but here's what I do when faced with trickiness problems:
#1 Encapsulate in a stored proc. That is, don't plan on it being a handy view for the report.
Now once in the proc you can decide to break it up into 15 differents steps (or 1000).
So the report criteria is like, give me everybody with up to ten different code/value combinations.
In multiple passes, select the key columns of workflows into a temp table that match criteria x. (e.g. WHERE @parmvalue1 in ( column1, col2, col3, col4, ...) )
With each subsequent pass join to the temp table to filter your selection on what was selected prior.
At the end of (up to) pass 10 you are left with the workflow keys that match all 10 criteria. Then join as appropriate for the relevant columns and return the result set.
Then tweak as appropriate for performance.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply