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.

Naming Temp Tables in Stored Procedures called by Others

Beware the temp table names you use when naming such tables in a stored procedure that in turn gets executed inside other stored procedures. If both the executing and executed procedures have temp tables with the same name, you might be in for a nightmarish episode of tearing your hair roots out. If using the same #TableName in both procedures, they must have the same exact column structure. You later add a column to the executor and don't do the same to the executed, you will end up tempted to execute yourself before you figure out why you keep getting insert errors in the child proc. Bottom line, keep temp table names different in procs called by other procs.