-3

We currently have a Users table in the database, this has quite a few columns in it. Around 50% of the columns are barely used in the system (only on one or two pages). We've been discussing amongst ourselves whether to split this table into two tables e.g. Users and UserSettings. Our thinking is also that we could then split the C# objects up as well to follow suit.

We trying to think about db index size and things like caching as the user objects are cached in Redis. I know in some cases we'd need a couple of db queries to the get the data instead of a single one but as this would be the exception the saving everywhere else would be of a bigger benefit.

  • How many records are we talking about? – Dan Pichelman Feb 14 '18 at 20:26
  • @DanPichelman In the db as a whole we could be talking 100's of thousands but these would be grouped per client where you'd be looking at a max of 10k ish – Gaz Feb 14 '18 at 20:28
  • 1
    What is your specific question? – Robert Harvey Feb 14 '18 at 21:13
  • @RobertHarvey Whether it's beneficial to split a large object (from yours or anyones previous experience) in 2 from both a app performance and database performance point of view. Looking for people who've had similar dilemmas and how they have handled it – Gaz Feb 14 '18 at 21:15
  • 4
    Any such answer would inevitably be platform-specific, software-specific and requirements-specific, but sure, I understand your question. I think ultimately you'll have to run some tests in your own environment and see for yourself if it's worth it. What would "beneficial" mean in your specific context? Are you trying to reduce the page load time to a specific number? – Robert Harvey Feb 14 '18 at 21:19
3

From experience with much bigger Oracle databases (but I think most of that reasoning also applies to other DBMSes):

If splitting the Users class makes your application code cleaner, do so. But I wouldn't expect preformance or storage space problems.

VARCHAR columns occupy space only according to the actual content's length, and not the declared maximum, so mostly-empty columns don't waste a significant amount of storage space. The same applies to indexes on these columns.

Make sure your table fits into the DBMS RAM, so queries can be served without disk I/O (after some initial warmup). With some 100k lines of maybe 1 kByte each, that would mean less than 1 GByte of RAM, so that shouldn't be a problem nowadays.

3

We currently have a Users table in the database, this has quite a few columns in it.

So? Your queries should only be retrieving the columns that they require, so having extra ones lying around in the table is irrelevant.

We trying to think about db index size ...

Unless you have a user base as large as FacePlant or Twaddle then your user-settings data should be dwarfed by the "real" stuff (i.e. don't worry about it).

I know in some cases we'd need a couple of db queries to the get the data instead of a single one

Nonsense.
You would have two physical tables, both keyed by and indexed on the same [user] identifier and where you need data from both tables, you would join between the two. Absolutely no need for multiple queries.

  • 1
    Thanks for the answer, would have been better received without the sarcasm. Belittling users for asking questions puts people off from asking more. Not everyone on here is a seasoned veteran. – Gaz Feb 15 '18 at 13:03
0

Fist, have you actually identified an actual problem? Can you show that having a wide table causes some problem for the system in terms of throughput, resource consumption, latency or somesuch? Splitting the table will cause maintenance overhead so you'd better be sure it's justified.

We [sic] trying to think about db index size

The index size is determined by the key column(s). Since the key won't change after the split the index size will be the same. Specifically the index depth will remain the same, and this is where a lot of the overhead comes in. Moreover, I'd imagine both tables will have the same key (UserId?) so twice as much disk will be used to store the index.

Some RDBMS implement BTree indexes where the leaf is the full row. For these, splitting the table will allow more rows to be held in a given amount of RAM, improving performance. You will not see improvement, however, if the DB server is not under memory pressure, or the user table is touched so frequently it is never evicted from memory currently.

and things like caching as the user objects are cached in Redis.

Do you serialized and cache the whole, wide object? Well, doing less work is likely to be faster than doing more work. So splitting is likely to be beneficial. This does not require the split to be performed in the RDBMS, however, only in the Redis cache handling code.

I know in some cases we'd need a couple of db queries to the get the data instead of a single one

Well, maybe. A single query can join many tables & return a single resultset. A stored procedure can return multiple resultsets. A view could be defined which joins the tables and the view can be referenced from the application.

I get the impression an ORM is involved. Your options here may be dictated by it.

One major drawback to splitting is that you now have two objects that absolutely, definitely must be in sync. This will require very careful application code or, more likely, database triggers. This complicates the reasoning about the system. Would it ever be acceptable to have a User without corresponding UserSettings? Under what circumstances can either be removed? How to enforce the 'exactly one' relationship constraint between the two tables? This is achievable but requires planning.

-1

According to me, I would suggest that normalize tables as much as you can. It will surely affect the performance.

-2

In most (if not all) databases a null value takes very little room.

A separate table has the overhead of a foreign key.

protected by gnat May 8 at 5:21

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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