3

So let's say you're writing software for some company. Best practices, as I understand them, would dictate that for dev purposes, you populate your DB with fake data. There are a number of benefits to this.

  1. If you're going to be using, say, Vagrant to manage your dev environment, most of the pre-built images have HDD's of a limited size. Like let's say production has 100's of GB of data. Your Vagrant box isn't likely going to be that big. Also, if you're doing continuous integration testing, you're probably not gonna want to do it with a production size DB.

  2. In theory, developers ought not have personally identifiable info of real world customers and this facilitates that.

One big problem I see with this, however, is... let's say your dev DB is, all together, maybe 1MB in size, whilst production is 100's of GB in size. A developer might write a query that JOINs tables together on unindexed columns. Maybe with 1MB of data it works great but with 100's of GB of data?

How is one supposed to deal with this problem?

(for that matter, there can sometimes be an excessive amount of red tape to cut through to get production data to accurately reproduce an issue specific to a customer, but that's an organizational problem - not a technical one)

Improve this question
5
  • 1
    How is one supposed to deal with this problem? -- By following sensible indexing and querying practices, optionally verified by executing performance tests on large data sets. Note that you can still get a pretty good performance indicator when testing with smaller databases.
    – Robert Harvey
    Jan 27, 2017 at 3:46
  • @RobertHarvey - and what if a multi-column index would be beneficial? As ORM is becoming increasingly popular I'm finding that devs are becoming increasingly weak at SQL. Personally, I'd much rather a dev identify slow queries in the course of developing their code rather than those issues to reveal themselves on prod. Or maybe one of their peers could make that something they check for when doing code reviews, but even that requires a dev who's good at SQL.
    – neubert
    Jan 27, 2017 at 3:53
  • @RobertHarvey - best practices, these days, dictate, for example, that prepared statements be used over escaping parameters. The idea being that prepared statements foster a mindset wherein mistakes are hard to make. It's easy to forget to sanitize a variable - it's harder to forget to bind a parameter to a query. And yet we expect devs to just magically know when to index things when testing on their dummy databases wouldn't reveal any issues anyway?
    – neubert
    Jan 27, 2017 at 3:55
  • 3
    I suggest you clarify your question, then. I had to guess that, when you used the term "works," you meant "adequate performance with large data sets." I didn't realize you were trying to start a discussion about the entire subject area of SQL data access. ORM is really only meant to be used in a CRUD context anyway, where "exotic" joins and indexes aren't going to matter, and the apparent answer to the question you seem to be asking is better education and experience.
    – Robert Harvey
    Jan 27, 2017 at 4:19
  • Use an anonimized copy of your production database for profiling.
    – GolezTrol
    Jan 29, 2017 at 3:09

5 Answers 5

Reset to default
5

This question has nothing in common with "unit testing", it is not caused by unit testing, cannot be detected and obviously not solved by "unit testing".

Having applications facing bigger data sets in production than expected during development is a really old problem (I guess >50 years old). It is in no way restricted to databases and database queries, and the same is true for the tactics to deal with it:

  • Make proper planning and estimation for the data you expect in production.

  • Profile with smaller datasets and extrapolate for the bigger ones you expect (and make sure you do not extrapolate in a linear fashion when the order of growth is quadratic or worse).

  • Test properly and make sure your program can give you also profiling information when it runs in production.

  • Make sure you do not pick an architecture which brings you into a dead end for the expected amount of data.

  • Optimize as needed - start with optimizing when you have indicators for performance problems, but not earlier. Make sure your devs know what "premature optimization" means and that they do not optimize the wrong parts of the code "just in case".

  • Make sure you have educated, experienced devs and/or db experts working on the critical parts of the application. Writing good software is not a beginners game.

Improve this answer
4
  • Quoting my original post, "if you're doing continuous integration testing, you're probably not gonna want to do it with a production size DB". Unit testing isn't the solution nor do I think that can be inferred from my post. Rather, I'd say that unit testing is part of the problem. Or more specifically, unit testable code involving DB queries is part of the problem.
    – neubert
    Jan 27, 2017 at 14:32
  • @neubert: I don't see why the problem should be a different one if the organization in stake uses unit tests or no unit tests. If the test data size in the dev environment is much smaller than in production, you might face the problem you described - unit testing or no unit testing.
    – Doc Brown
    Jan 27, 2017 at 15:01
  • That's very true. But what would an alternative be the title? "optimizing database queries" seems misleading. Maybe "optimizing database queries in instances where the dev db is smaller than the prod db" would have been more accurate albeit less succinct.
    – neubert
    Jan 27, 2017 at 17:09
  • @neubert: Beeing succinct is fine as long as it does not change the meaning so much that a question will be misunderstood. However, I would try not to think in terms of databases exclusively (giving databases as a typical example in the question text is ok, but the question title could be more general). Something like "Preventing scaling problems when production datasets are much bigger than test datasets"? And I would also remove the "unit-testing" tag from your question (the other two tags are IMHO not a good fit, too).
    – Doc Brown
    Jan 28, 2017 at 7:02
1

In an ideal world:

You let database administrators deal with that. They have the tools and the background to make the right decisions about what and what not to index.

In a less then ideal world there are a few things you can do, among those:

1: Be sensible. Don't use a "select *" and if you do a "select personid from persons where somecondition = x", you may find an index on somecondition good, unless of course if you do 10.000 insert/second on the table.

2: Let your application do the profiling. SQL server for instance has some nice ways to find missing indexes and database performance in general.

SQL server for instance has built in functionality to find missing indexes and it's on by default: Find missing indexes in SQL server.

Improve this answer
1

How is one supposed to deal with this problem?

Build out a performance test environment. Populate a database with 100s of GBs of test data. Use bulk load techniques to populate the database quickly. Then performance test your application. Verify the indexes and queries are performing correctly with this amount of data. If not, add or re-organize your indexes appropriately.

Always take a look at how much data your system will have [N] years from now. Develop a purge/pruning strategy. Test your queries against your planned high water marks to avoid potential performance problems in the future.

Improve this answer
0

  1. Build Tests Ideally, you shouldn't be pushing out code live if it hasn't passed some type of build test, and the build test should be smart enough to detect massive changes in performance.

  2. Get a DB Admin Database Administrators will know how to make smart indexes on tables.

  3. Good Code Practices Companies with large datasets should be implementing some type of code standards or practices for exactly this type of reason.

Improve this answer
-1

Stored Procedures

If you are at a stage where you have a critical db which can be taken out by bad queries then you need to get a bit more professional than just putting the sql into the code.

Having all DB queries use SProcs gives you that extra layer of abstraction which is required to allow you to tweak your queries for performance as your database evolves.

Improve this answer
1
  • There are advantages and drawbacks in using stored procedures, it's not a golden hammer. And also, there's no problem that can't be solved by another layer of abstraction, except too many layers of abstraction.
    – Pieter B
    Jan 27, 2017 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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