Comment by mooreds
4 years ago
My fav optimization story:
A few years back, I was doing some geographic calculations. Basically building a box of lat/lngs and getting all the points within that box.
It was slow. Weirdly slow. I made sure the lat and lng of the records were in the index, but it was still slow.
More testing revealed that the way I was passing the lat/lng into the query was causing those values to be converted to strings, which were converted back to numbers, but caused the index to not be used. This meant the database had to do a lot more work.
Converting the parameters to numbers made sure the index could be used, which lead to a nice speed up.
Edit: I thought I wrote this up, but I didn't. But it turns out there was some interesting sorting shenanigans that I did: https://www.mooreds.com/wordpress/archives/547
Sometimes it's worth peering down through those layers of abstraction!
At least that was an accidental conversion and an understandable mistake, I've seen the same with dates stored as strings from devs unaware that dates are just integers with a lot of maths to make them meaningful.
At once place our end of month billing was getting slower and slower over the course of months, from one hour out to about twelve and it had to baby sit and run in batches in order to not bring the whole database to it's knees. We couldn't change the mess of legacy classic asp of course, but adding a couple of actual date columns calculated from the string fields on insert/update bought the whole process down to seconds.
Whoa, that's an awesome story. Nuts that the slowdown happened over such a short period, must have been a fair amount of data running through that system. Lots of table scans <shiver>.