We have a production application which gathers data for certain type of incidents, based on a collection of fields (aprox. 1000) of different data types - string, int, double, date, time, GUID (id of selected option for dropdown lists), list of GUID's (for checkbox lists).
The current design is like this:
- Occurrence has a collection of TermResults;
- Each TermResult has a collection StringResults, IntResults, NumberResults, DateResults, TimeResults, GuidResults, ListGuidsResults;
We have a distinct table for each type of result in the database. So, reading / updating an occurrence will require joining data from about 12 tables (we have additional data stored in other tables, besides the Results ones).
The operations we need to do with the data are:
- Read / update an occurrence;
- Query database to retrieve occurrences based on very complex dynamic queries, which can be built based on those 1000 fields. For example, retrieve occurrence where field1.Value >=3 and field2.value <= '01/01/2017' or field3.value == GUID1 etc, etc.
- Export results of queries (occurrences data) to Excel / JSON;
Up until now, we had the following performance limits, for about 30000 occurrences:
- Read / update -> 5-20 s, depending on the size of the occurrence;
- querying -> very much depending on the size of the query (number of fields queried), but still fast enough 1-20 s.
The performance of Read / Write and also the time needed to extract data for export is very bad already, so we decided to re-engineer the database design.
Solutions we are thinking to adopt:
- Store all results into a single table InputResult, with a column for Value of type nvarchar(max), so we cannot add an index on Value. Made some tests with over 1M records. Read / write would be fast. Querying is disastrous. More than 5 min to query by a single field.
- Store all results into a single table InputResult, with columns for all data types - ValueAsString, ValueAsInt, ValueAsNumber, ValueAsDate, etc. We can index each column appropriately, and query them individualy. Downsides- will have lots of nulls on each line. The table would grow pretty big.
I'm thinking the second solution combines the best of both ideas. What do you think? I am wondering if someone has encountered this kind of problem and has some experience to share.
So, what design would be better for this kind of problem?
EDIT: Redesigned the table and posted new question here: How to improve execution time for queries by multiple fields of different data types on a single table?