You would expect that dynamic sql, that is a string constructed in a stored procedure and then executed would be slower than the equivalent as a stored procedure. However I was surprised to find that an application become 7 times faster by removing this.
The dynamic sql allowed the database to be accessed to be specific at call time. However at any given time only one other database was being used. By replacing the appropriate stored procedures at change over time we obtain a substantial performance benefit.
