Roadblock Cleared – Technical Junk Within…

road-signs-construction-1-1503521The project hit a roadblock of sorts, but don’t worry it has been knocked down! As I was planning the trucks I kept going into more and more depth and I started realizing the colossal amount of objects I’d be producing. With so many objects for the server to access, the time it took to load a single truck started to become a concern.

18 Wheel Empire is not the first application I’ve designed and developed, but it is certainly the first one of this magnitude. I have simply never encountered a situation where I’d likely need to access millions of rows of data in a database on a regular interval in order to dig out just 10 or 18 rows. But that was exactly what I was up against as I started sending messages to a developer friend of mine. Thankfully, when presented with a problem, he likes to play with it and help come up with a solution.

It took maybe two hours of him writing tests and playing around before he came to the same realization I did. We’re screwed if we dump all those tires in one table! The plan for the application is to allow for players to have/manage multiple drivers. At the same time, I want to track tire information down to the specific tire. Rather than having tires randomly wear out based on strictly chance, our tires will wear over miles traveled. That wear will be affected by speed, driver ability, current air inflation rate, in addition to a whole host of other issues.

One tire might get replaced because of a random blow-out while another has 150,000 miles on it. Still another tire may rot long before it’s tread is used up because it sat for months or years on a trailer in some drop yard. But wear and rot aren’t the only reason for a change. A company might decide to pull the tires off their trucks at a certain tread depth and move them to the trailers in order to get more miles out of them before retreading them. Whatever the reason for a tire being replaced on a truck or trailer, we need to track the individual tire, and the stats that belong to it, so we can change it out independent of other tires.

Tracking tires like this requires me to store the data on each one of them. The question was how, and where? Ultimately we have trucks, engines, engine components, axles, brakes, tires, and items the driver might want in or on the truck. All of this adds up quickly. With only 10,000 accounts it’s not unimaginable to run into 3 or 4 million different objects. Sorting through that much stuff resulted in some really long access times, even with good table indexing.

Ultimately we came up with some non-standard tricks to tuck away objects and get the job done. We went from almost a full second to pull a single truck out of the database to pulling a group of 30 out in less than half a second. Exactly the kind of access times I need if we’re going to try to update over 200,000 trucks often enough to make this work!


Leave a Reply