I am having some trouble figuring out how to tackle a certain problem. I need to aleviate a server of some of the workload a particular report is causing due to queries and data processing.
I am pulling data from two different servers and I need to perform various comparative functions.
What I have is two DataTable structures that are loaded with identical field formats.
user_id, unit_id, task_id, task_result
I'm sorry, I can't reveal the actual data structure, but this should serve fine.
The User_id is unique to the unit it is in, however the same user_id may be used again in other units
The Unit_id is the grouping of users this record belongs to.
The task_id is a particular function that the user is performing. These, like the user_id are only unique in the unit_id they belong to. So Unit 1 may have a task id of 40, and so will unit 2, but there will never be two task ids of 40 in the same unit.
The task_result can be one of three results (pass, fail, error) - but the numerical value representing these is determined by the version of the task. (i.e. all tasks have a new version and the results are stored differently, but I need to still be able to make a comparison between old versions and new versions)
The user_id should be relatively useless in this comparison
Each DataTable structure holds a run of tasks (i.e. a run of tasks run one day - and then the same tasks (minus a few) that are run on a later date)
What I need to do is get a breakdown of what tasks were previously passes that are now fails, What tasks were previously passes that are now errors, what tasks that were previously fails that are now passes, what tasks that were previously fails that are now errors, what tasks that were previously errors that are now passes, and what tasks that were previously errors that are now fails.
pass to fail
pass to error
fail to pass
fail to error
error to pass
error to fail
I set this up as a counter to just go through all the tasks in a datatable that matched a unit_id and a result_id, but with 100k plus tasks per unit, this takes a couple hours to compute.
What I am looking for is some method of cross referencing datatable structures with each other.
I tried looking into datasets, but the relationship capabilities don't seem to be what I need.
If anyone has any suggestions on this I would forever be gratefull for them.