Thursday, May 11, 2017

Don't Cast Numerics to Varchars In SQL Joins

Had a performance issue with a stored procedure recently and discovered why the thing was choking, taking minutes to run. It had a most unfortunate join factor among the several joins in a complex query. The join was a varchar field matched to an integer field. For whatever reason long lost in time, the writer decided to cast the integer as a varchar to map it against the crazy varchar field, whose values could be anything from numbers to symbols. That field's data content was out of our control, coming from an outside source and possibly needing to be preserved as is for auditing. The only values in that field however that meant anything to us were the numeric ones. What fixed the ridiculous runtime of this proc was to reverse the casting. I changed the join to instead cast the varchar as an int to match against the real int. To do that I had to make sure the varchar was numeric inside the cast. join dbo.StupidTableWithIdioticVarcharField s on cast(case when isnumeric(s.StupidField) = 1 then s.StupidField else 0 end as int) = NormalTable.NormalIntField The speedup was breathtaking. It would've been more breathtaking if the original data was designed, or at least redesigned differently, but sometimes you just gotta work with what you got.

No comments:

Post a Comment