We have a very slow stored procedure. I worked on optimizing its code. I ran multiple tests of the code of the procedure against production (not as a procedure, but using code inside) and I got my results in 2 minutes as the worst result.
Today we're running this as a procedure and it has been running the long and complex query forever (I added lots of logging, so I know which particular query is slow). It originally used 2 scalar UDFs - one for the constant and one for the parsing of the varchar column to get its 7th part and use as part of the where clause. This was slow in my tests (~30+ sec.) but not as bad as seeing it now. I removed the scalar UDF and replaced with string_split and OUTER APPLY. There are also lots of tables joined in the process but they are supposed to be joined with one temp table which only has 100 rows.
Anyway, why would I see such drastic difference in results and what would be the best way to optimize this query? It looks like some stale plan for that one is still in effect even though the query is re-written.
I'll double check if all indexes are in place for the JOIN conditions.
Any ideas of how to speed up this monster?
Thanks in advance.