CODEWITHSUNDEEP
sqlsql-server
knpwrs
knpwrs

Reputation: 16426

Lots of data in one column vs lots of small rows

I am interested in the performance of each of the following, specifically in SQL Server.

Simple overall concept: I have objects (let's say Type1) that each have a list of another type of object (let's say Type2).

My current schema has a table for each type. Table1 holds Type1 and Table2 holds Type2. For each record in Table2 there is a reference linking each Type2 to its respective Type1.

So now for the question. I haven't gone too far into making this yet, so I'm not too far in to change. I don't plan on ever pulling up a Type2 object individually from the database. I will always pull a Type1 which will either come with the list of Type2 objects or have a method to retrieve them. In this case would it be better to just serialize the list of Type2 objects (say, to JSON) and store them in a column in Table1 for each Type1? Would I notice a slow down if this column were to get very very large?

Upvotes: 0

Views: 78

Answers (1)

VoteyDisciple
VoteyDisciple

Reputation: 37803

The question is: would you ever, under any circumstances, make changes to what Type2s are available? Almost certainly the answer is yes: at some point in the future you will want to add one, or remove one, or edit one. And given that, having them serialized in JSON will be disadvantageous.

Keep the arrangement you have with two separate tables, create an INDEX on the Type1 ID in Table2 (so the query to find the associated Type2s will be nice and speedy, even if the table becomes extremely large), and you shouldn't have any trouble.

Upvotes: 2

Related Questions