0

Currently I am storing JSON in my database as VARCHAR(max) that contains some transformations. One of our techs is asking to store the original JSON it was transformed from.

I'm afraid that if I add another JSON column it is going to bloat the page size and lead to slower access times. On the other hand this table is not going to be real big (about 100 rows max with each JSON column taking 4-6 K bytes) and could get accessed as much as 4 or 5 times a minute.

Am I being a stingy gatekeeper mercilessly abusing our techs or a sagacious architect keeping the system scalable?

Also, I'm curious about the (relatively) new filestream/BLOBs type. From what I've read I get the feeling that BLOBs are stored in some separate place such that relational queries aren't slowed down at all. Would switching varchar to filestream help?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
micahhoover
  • 2,101
  • 8
  • 33
  • 53
  • I'd go with the stingy version - 4/5 times a minute is nothing, even for a table with a shedload of rows. Obviously it does depend on the server hardware, and what else is sitting on there/being accessed but I don't think you need to worry even for a server with a fair amount of load – Charleh Mar 23 '15 at 13:14

2 Answers2

1

Generally BLOB is preferred for Objects that are being stored are, on average, larger than 1 MB.

I think you should be good with keeping them in same database. 100 rows are not much for a database.

Also, what is the usecase of keeping the original as well as transformed JSON. If original JSON is not going to be used as part of normal processing and is just needed to keep for references, I would suggest keep a separate table and dump original JSON there with a reference key and use original only when needed.

Guanxi
  • 3,103
  • 21
  • 38
  • The use case is the tech posts some JSON that gets transformed and then decides he needs to edit it. Instead of keeping copies of the JSON in notepad he'd like to just be able to pull it up and modify the original. – micahhoover Mar 23 '15 at 13:09
  • In that case, you should be fine with keeping them in separate table, and make them available only when tech need one of them. – Guanxi Mar 23 '15 at 13:48
1

Your use case doesn't sound to have too much demand. 4-6KB and less than 100 or even 1000 rows for that matter is still pretty light. Though I know expected use case almost never ends up being actual use case. If people use the table for things other than the JSON field you might not want them pulling back the JSON because of the potential size and unnecessary bloat.

Good thing SQL has some other lesser complex options to help us out. https://msdn.microsoft.com/en-us/library/ms173530(v=sql.100).aspx

I would suggest looking at the table option of Large Value Types out of Row as it is future compatible and the text in row option is deprecated. Essentially these options store those large text fields off of the primary page, allowing the correct data to live where it needs to live and the extra STUFF to have a different home.

Brad D
  • 752
  • 4
  • 8