1

I have a "classic" SQL Server three table many-to-many in the form

PC -< [PCHasSoftware] >- Software

I am having issues with data volumes and performance in a very large database scenario, so am exploring a further redesign to improve performance and cut storage, etc.

Many PCs will have the same software titles. So can we cut storage by somehow reorganizing that into groups of titles. Then instead of saying

"this PC has these 16 titles" - needing 16 rows per PC

we have instead:

"this PC has this group of 16 titles" needing 16 rows somewhere and only 1 row per PC.

So if 10,000 PCs have these 16 software titles, I'm storing 10,016 rows instead of 160,000 rows.

Two initial thoughts on that idea.

  1. Build software to identify the 8 most commonly occurring groups, and then retain 8 bit flags on the PC table, and have a template PC which holds what those titles are in the same structure

  2. Organize into groups

    PC -< [PCHasSoftwareGroup] >- [Software Group] -< Software
    

or (edit)

    PC -< [PCHasSoftwareGroup] >- [Software Group] -< GroupHasSoftware >- Software

The difficulties with these models I perceive are:

  • How to identify which groups are most popular. Probably a chunk of SQL operations that runs in a batch job overnight to "groupize" the standard mode. I wondered if any clever "dba+maths" people have come up with any algorithms that could do this

  • When one of those 16 titles is moved off a PC, you suddenly have to remove the group relationships and insert the 15 rows as non-grouped detail row, so potentially a lot of traffic for one update

  • How to combine this with software which does not fall into any logical grouping. I'm not a fan of using union because "SQL" only seems guaranteed to generate good query plans when you keep things like that out, but that doesn't mean I won't use it if its the best solution!

  • There will be some data that is specific to this PC, like DateInstalled, DateLastSeen etc etc. So we'll still have to store some data at the PC-software level, but this could still reduce data volumes compared to the data model described at the top of this post

This may have other applications, and I imagine it would have been done before. e.g. in an order entry system, if its very common for a certain combination of products to appear on orders, you could represent that as a group as a single order line in the database and expand it during querying.

I'm just kicking around ideas here and would appreciate all thoughts. I wasn't even sure what to google for but I've tried for the last week to find other examples of this and failed.

  • 1
    I believe you are over thinking it. The complexity you are adding isn't worth the bytes you are saving. – Anthony Genovese Sep 14 '17 at 14:20
  • Well this is one of about 10 things I am looking at to fix the problems we have, but the idea that we can replace thousands of records with very few records is a compelling one, and worthy of investigation IMHO – user2728841 Sep 14 '17 at 14:27
  • From here. "At my last job, the system administrator kept sending me automated spam complaining that I was using more than … get this … 220 megabytes of hard drive space on the server. I pointed out that given the price of hard drives these days, the cost of this space was significantly less than the cost of the toilet paper I used. Spending even 10 minutes cleaning up my directory would be a fabulous waste of productivity." Dbugging for scheme to use bitflags take?Your many-to-many table is your best bet here! – Vérace Sep 14 '17 at 15:58
  • This sql database has recently grown to 300GB. It is well normalised in TNF terms but we do have unrelated data in the same lookup tables so splitting that will help a lot since sql is having to scan over records that would never be relevant to the current query, and we're having to have bigger indexes due to the column that delineates lookup types. We're also going to do a bit of 6NF stuff where it makes sense from a performance and storage POV - I.e. take off data thats mostly null into subtables.... – user2728841 Sep 14 '17 at 16:34
  • BTW its not just about storage, we are having some very slow queries which affect end users. In terms of the above post, I think I have a model that will make sense and work well, so I am just doing some calculations on how much space it will save. – user2728841 Sep 14 '17 at 16:36
1

It may be worth looking at things from the other direction.

I suspect that you have a large number of PCs that are set up with exactly (or almost exactly) the same set of software. You may want to consider establishing a set of PC "profiles": a set of software that's going to be standard for hundreds (or even thousands) of PCs.

This has most of the same benefits and drawbacks as your current plan, but might impact more rows more easily. And, it might make certain kinds of changes easier as well: in many cases, a new/updated piece of software will ultimately be deployed to all the machines; you can create a new profile, and move PCs to it as the software change is applied.

Functionally, this would look much the same as what you're talking about; I simply think the perspective helps nail tings down a bit.

If you are going to create application groups, I would not try to generate groups based on what's on PCs today directly. It's useful information, but should be looked at carefully, to identify meaningful groupings, and not just "accidental" ones. It's better to create four different groups where each one actually contains software that has a logical reason to all be on the same machine together, than to create just two groups, each of which contains some things that may be found together, but not on purpose.

Finally, note that this sort of thing always involves a trade-off. You may get better performance because you're dealing with smaller tables, but your queries will involve more tables and/or more joins, and be more complicated. The end result might actually be worse performance.

  • Yes this is one of the considerations, and we've had the concept of "template PCs" for some time, but not for data model efficiency just for user functionality. The question is how many PCs will actually ALL have that set of titles so you can exclude them all. If its a lot then this is a massive win. We're also looking at grouping the titles by OS, but actually there seems to be a lot of titles which are always present. So thanks for this and its definitely a possible answer. – user2728841 Sep 14 '17 at 18:54

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.