Hacker News Reader

Comment by Pxtl | Hacker News Reader

Here's my SQL Database Optimization Guide:

Ignore all the advice about "rules". Create a realistic dataset and run performance tests before you release anything into production. Check your query-plans to find the pain-points. The database is frequently treated black box surrounded by superstition and cargo-cult "rules" that are either out of date or just educated guesses.

Wrap anything weird in a view so you can rebuild it as you see fit.

Avoid getting fancy as much as possible until the simple, normalized, naive implementation fails.

View on HN

> Create a realistic dataset and run performance tests before you release anything into production.

This is so true.

Depending on your write patterns and your queries, a realistic dataset can mean:

1. Same distribution of keys as production

2. Same rate of writes as production

3. Same order of writes as production

4. Same volume of data at rest as production

( edited for formatting )

This IS the answer for all databases. Start simple, do straight forward queries, and then measure, measure, measure.

Unfortunately query plan builders can do odd things. Is IN faster than EXISTS? Sometimes yes, sometimes no. The only way to know is to test, and this is true across the board.

Not only is it sometimes faster, and sometimes slower, but the answer can change, for the same schema, for the same query, depending on the volume and distribution of data.

Can you please provide an example of answer changing?

Yes. Every day. The developers here write SQL that runs acceptably for a given schema at a given point in time. Unfortunately, the code pattern isn't scalable (which is generally why people say that RDBMS' can't "scale" anyway, but that's a different rant) and performance changes, sometimes dramatically. Where a LEFT OUT JOIN ... WHERE outer.value is NULL worked great for an anti-semijoin earlier, NOT EXISTS may be better. What jpitz is talking about are statistics that are tracked by the engine. They are a key part of how SQL Server's CBO determines the best plan to access the data. As data grows, chances are you're going to get skew on the keys that you've defined. If your data is such that the counts and distinct counts of your keys are guaranteed to remain consistent, then you have a lower chance of plan changes as data scales.

It's been while since I have spent time with MSSQL, but I remember seeing this with sprocs and cardinality. The sproc would compile with 1 plan that dealt with tables with little data. The plan would not always get updated as the tables fill with data.

The underlying reason is that as data changes the plan changes. For example, on a small table, it is faster/more efficient to do a table scan than go to the index and then to the table for data. At some threshold this changes. If table size can change the plan, then the query itself could also need changing as the table grows, indexes grow, and differing index cardinality emerges.

Offhand, I don't remember the specifics - I observed this a decade ago. Sorry. I don't have a SQL Server around to try to craft it either.

The gist of it is, the query planner for some database engines ( pg and mssql ) often takes cardinality into account when selecting plans. Therefore those plans change with the size of the dataset.

EDIT: It occurs to me to wonder if you're asking if the resultset changed. No, I've not seen that answer change.

The very fact that IN can produce different query plans than an equivalent EXISTS is a very nasty smell. As a developer, I shouldn't have to care. The database is supposed to figure that out. That's why we're paying no small sum of money for this.

Agreed, fancy and clever in optimization/design is super dangerous especially since you don't know what will/won't work until you measure. Also if you do "Wrap anything weird in a view" (which is an acceptable approach) realize that any and all of that part of what you've built could be the problem.

The best query plan can look good even on a representative data set but your customers don't always have representative data sets and they don't really care if it works on the bench with 10 million records if their 10 million record data set does not work.

Reply on news.ycombinator.com