0

I'm currently working with a large table that contains several hundred different types of records (with around 100 million rows in total). To fit all of these types of records into the same table, it has ~400 nvarchar(1000) "flexible" columns that are either filled with some form of data or left null, depending on the record type (specified by a typeId). Most record types don't use anywhere near that many columns and so are mostly null, but a few of them do. Users are able to add or modify record types through the system's UI, and can define which fields are stored in which columns.

Most queries on this table are on a single record type at a time. The queries may filter or sort on any of the flexible columns in use by the record type, but some are much more likely to be filtered/sorted on than others. However, given that the same column might be important/unimportant/unused depending on the record type, there are no indexes on any of the flexible columns.

I'm not sure why this design was originally chosen, but I have been considering different options for a redesign, as it is not very performant. Furthermore, I'm unable to make any updates to the current table schema, as this causes SQL Server to attempt to verify that each row remains within the row size limit, and this process takes too long to feasibly complete.

Some options I've considered so far:

  1. Combining all flexible columns into a single nvarchar(max) column that contains record data in JSON format. In a test, this resulted in generally slower queries, seemingly due to the overhead of querying with SQL Server's JSON functions. It also ballooned the storage size of the table, due to all the extra characters needed for JSON objects/properties, and because the table could no longer effectively use compression.
  2. Storing one record field/value per row in a new table, which I see from other questions is usually called EAV. In a test, this also resulted in slower queries, due to the additional joining required. It also resulted in much slower inserts, as it seems inserting many small rows takes noticeably longer than one large row.
  3. Creating a new table for each record type. This has not yet been tested, but I'm unsure if it would be wise to create so many tables in SQL Server for this data. It would also present challenges in maintaining these tables, given that record types can be added or modified by users (I suppose it might involve dynamic SQL to add tables/columns, but I'm unsure how removing columns would be handled without losing historical data). However, it would have the huge benefit of enabling the use of proper indexes, as well as column types that better reflect the data.

I'm hoping to get some feedback on best practices in handling this kind of data, and whether option 3 is the right direction to explore next, or if there are better options I haven't yet considered.

5
  • 1
    For query performance, it's highly likely that option 3 will be an improvement, but the appropriate answer depends on balancing the complexity of the (changes to the) UI which facilitates inserts and the database performance. Given that you have a type ID for each record already, it sounds like it would be pretty natural to have a new table for each type ID, but there will be some effort involved in adapting the backend code which inserts user input into the database. Without seeing the structure, it's hard to say anything more... Nov 19, 2021 at 6:55
  • 2
    "I'm not sure why this design was originally chosen". A lazy analyst and designer. Or a visionary wanting to create a panacea, in which he/she seems to have succeeded up to a point: you can't have unlimited freedom and good performance. Solution 3 is the one that fits an RDBMS. An RDBMS can be used to solve a problem, It can be maintained to keep up with changing needs. I wonder what application is using your database. How does it adapt to the changes? Nov 19, 2021 at 10:28
  • 1
    There's not enough information here to provide a concrete answer, but I would recommend you read these two SO answers to start thinking about things in the right context: stackoverflow.com/questions/4304217/… stackoverflow.com/questions/4011956/…
    – bbaird
    Nov 19, 2021 at 18:08
  • If your data was more static, I'd recommend clustered columnstore, but since you need the ability for user-defined columns, using a 6NF approach (like the one PerformanceDba illustrates) would be more suitable. Don't let the number of joins/tables dissuade you - with the proper clustered index and view definitions, SQL Server will manage just fine thanks to join elimination/efficient index seeks.
    – bbaird
    Nov 19, 2021 at 18:23
  • @bbaird Thanks for the links, I will review them, and will work on adding more concrete details to the question.
    – mdk
    Nov 20, 2021 at 2:02

2 Answers 2

0

As others have hinted at, having some more concrete information about your model structure and your application's use cases may be helpful in providing a more meaningful answer here.

But yes, in general option 3 is usually most suitable of a design pattern in a relational database management system (RDBMS). It will naturally help you normalize your schema which should generally result in improved performance and maintainability as a byproduct.

Currently your single wide table is a very denornalized implementation, and likely has worse maintainability, since any structural change needs to be applied to every row, even when a majority of the models stored in that table are unrelated to the change being made.

Also when you say:

It would also present challenges in maintaining these tables, given that record types can be added or modified by users (I suppose it might involve dynamic SQL to add tables/columns, but I'm unsure how removing columns would be handled without losing historical data).

These challenges already exist just the same whether you have a single table to store all models or multiple tables broken out per model. So that's nothing new being added by option 3. The only difference now is in the application you just need to provide a list of all the models (e.g. a dropdown) so that the user can choose which one to modify.

The EAV pattern is actually considered an anti-pattern by most, but in my opinion can be useful under the right circumstances too. I have a feeling your test implementation of it wasn't architected as best as it could've been, since joining and inserts shouldn't be much noticeably different in performance from your existing implementation. But hard to say without a schema in front of me. Either way, I think EAV doesn't fully solve your issue either (as I imagine you still had all your core models stored in a single table still).

As mentioned in the comments, since you already have your models identified by typeid then the different types are probably great starting candidates for a separate table for each.

6
  • "These challenges already exist ..." I don't quite understand this. We do use dynamic SQL for querying the table, but dynamically modifying tables and columns feels like a pretty big step beyond that. And currently, if a field is removed from a record type, the associated column stops being used for new records, but all historical data remains. I suppose we could do the same for the individual tables, never removing columns even if the field is removed from the type, but it's risky to rely on users to not create unnecessary columns that we're then stuck with.
    – mdk
    Nov 20, 2021 at 1:42
  • "since any structural change needs to be applied to every row" That's actually one issue we haven't yet had; there are so many columns in the table that all types have fit into it, and any structural changes we've wanted to make have been for metadata that applies to every row.
    – mdk
    Nov 20, 2021 at 1:46
  • 1
    And thanks for the feedback, I will work on adding more concrete details to the question.
    – mdk
    Nov 20, 2021 at 2:04
  • @mdk Sorry I misunderstood what you were referring to by the challenges. Though such a challenge is still possible should a user needs to add a record type that doesn't fit the table you currently have. Just as you must maintain the structure of the existing denornalized table, you theoretically would have to maintain the structure of the other normslized tables, which is fine. So it still isn't much of a new problem. Also you don't have to use dynamic SQL to create new tables, that's actually an easy problem solved with application code that generates the correlating regular SQL statements.
    – J.D.
    Nov 20, 2021 at 2:50
  • @mdk Regarding maintainability, my point is when you make a change to your existing table, it does currently apply to every row of a large denormalized table, even if you make the column nullable. This could result in a lot of unnecessary wait times and locking that could've been avoided if your table was normalized, since only the specific model table would need to change and not lock all the other data.
    – J.D.
    Nov 20, 2021 at 2:52
0

There are a couple of things you could try to improve performance that may give you some breathing space while you figure out a new design and rewrite the code.

Filtered Indexes

Normally an index would reference all rows in a table. It is possible to limit the rows indexed by adding a WHERE clause. I'd suggest having each index apply to only one type and then index it as you would a typical well-normalised table i.e. focus on columns used in predicates, joins, aggregates and sorts. If types have values 1,2,3.. and generic columns are named col1,col2.. and we determine that col1 is particularly important for type=1 the index will be something like

create nonclustered index T1C1
on dbo.GenericTable(col1)
where Type = 1;

Now a query like select .. from dbo.GenericTable where Type = 1 and col1 = 'whatever' should be able to use index T1C1 to produce a more efficient plan. There are, unsurprisingly, caveats of which here are a couple are examples, inter alia.

Partitioned Views

Moving the data to type-specific tables and re-writing the application all in one go could be too much work. A partitioned view would allow refactoring the data while maintaining the programming interface to the application. It also surfaces the new schema so the application rewrite can proceed.

Let's say each type gets its own table so type 1 is moved to table dbo.T1 and type 2 to dbo.T2. Further, let's suppose type 1 uses columns 1 and 2 from the current generic table and type 2 uses columns 3 and 4 only. The view will be something like

create view dbo.Generic_Table -- the old table's name
as

select
    1 as Type, C1, C2, NULL as C3, NULL as C4
dbo.T1

union all

select
    2 as Type, NULL as C1, NULL as C2, C3, C4
dbo.T2

A partitioned view is constructed in such a way that it is unambiguous which base table provided any row returned from the view. There are a number of conditions on the schema to make this work. They are documented in the link above. This makes the view writeable and the application can continue to insert rows as before.

The underlying tables are just normal tables. They can be indexed however best suits the use cases. Application code can be refactored incrementally to address these new base tables instead of the generic table. Once migration is complete the now-superfluous columns and object can be dropped.

View Triggers

A view can have triggers. Triggers can execute a broad range of TSQL. Having created separate tables for each type a view can be created to replace reads against the current generic table. This view will have a trigger for each DML statement which directs writes to the new correct schema.

Of course partitioned views and view triggers require the base table schemas to be somewhat static. It is possible to have application code add and remove columns to base tables at run-time. The performance implications may be more than your users are willing to bear, however.

JSON

You say you have had no luck with JSON. I think there are a few more things you could try.

First, vertically partition the table with only the PK and JSON in the new table. Then create a clustered columnstore index (CCI) on this JSON (see here). By keeping the JSON physically separate it will have no read impact on queries which do not use it.

Second, pull specific values from the JSON to persisted computed columns (here). Create secondary B-tree indexes on these computed columns, filtered by the type that holds them.

XML

XML is handled differently to JSON. It is a separate data type. Documents are shredded and stored internally in a binary format. Indexes can be declared directly on the XML column rather than on computed extracts. As with JSON, I would suggest not mixing XML and 400 nullable columns in the same base table.

Sparse Columns

There is an optimisation for tables which have many columns, most of which are NULL. I've never used it, nor do I know of any application that has. It does seem to be a good match for this use-case, however.

1
  • Wow, thank you for this answer, there's a lot to research and consider here.
    – mdk
    Nov 23, 2021 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.