The product page listed the same crop 36 times

You open a product in the dealer app to check which crops it is for, and the list of crops runs on and on: the same name, then the same name again, dozens of times, before the next one. The screen is slow to open. You suspect duplicated data, and you are right, though not in the way it looks.

Measured rather than eyeballed, one product’s detail request returned 260 kilobytes and took about two and a half seconds. Ninety-nine per cent of that was 972 rows of crop information carrying 27 distinct crop names, each repeated 36 times. The names themselves, deduplicated, would have been about 300 bytes. The category listing behind the product grid was pulling 128,304 of those rows across 133 products.

What was actually going on

The crops for a product are held in a table that links a product to a crop in a state. The company sells in 36 states. So each crop came back once per state, 36 times, and the screen printed every row it was given. One screen elsewhere in the app took only the first row, so it showed one crop and never the rest, which is the same fault in the opposite direction.

Behind that was the finding that mattered. The linking table holds 232,308 rows, and 239 products multiplied by 27 crops multiplied by 36 states is exactly 232,308. Every product is mapped to every crop in every state. Somebody had filled in every combination. So the question the screen exists to answer, “which crops is this product for”, currently answers “all 27” for every product in the range. Removing the repeats would fix what the screen shows and would not make the answer mean anything.

Nothing prevents it getting worse, either: the table has no rule forbidding the same product, crop and state twice. There are no exact duplicates today, and nothing stopping them tomorrow.

What we changed

The repeats are removed on the server, one row kept per crop, so every screen in the app benefits at once and no app release was needed, which mattered with a mobile release already in flight. The row’s shape was left exactly as it was rather than trimmed to a bare list of names, because nothing downstream reads the state off those rows and an unchanged shape is what makes it safe to ship without touching the app.

The change that did more than the de-duplication was to fetch the crop rows in their own query rather than inside the main one. Joined into the main query, the 972 crop rows had been multiplying against every other list on the product: its images, its pack sizes, its tags, its related products. Pulled out, the multiplication stops.

The product list query was deliberately left alone. It already filtered crops by the viewer’s state, so it was never repeating, and the same change there would have broken that filter.

Before and after on production data: 260 kilobytes and 2,457 milliseconds became 9.4 kilobytes and 294 milliseconds, 972 crop rows became 27 with no duplicates, and the category listing dropped from 128,304 rows to 3,564. The nested case, a product whose related product carried its own 972 rows, came back at 27. The check was made against the live production API with a short-lived token rather than against the build: 200, 9.4 kilobytes, 240 milliseconds, 27 rows, 27 distinct crops.

What it did not fix

The mapping. We said this to the client in plain terms: every product still legitimately lists all 27 crops, and will until somebody decides what the real crop-to-product mapping is. That is a decision about the products, and it belongs to the people who make them. The product query also takes no state, so it cannot be narrowed to the viewer’s state, and because of the full mapping it would return all 27 crops even if it could.

The mechanism

A linking table that had been filled with every combination, joined inside a query that fanned out across four other one-to-many relations. Fetching the relation separately mattered more than removing the repeats; the repeats were the symptom the client could see.

Where this ends up

The product detail a dealer reads before ordering is one of the screens in Sazinga Field, and the fix above is the reason it opens in a quarter of a second rather than two and a half.