Q: Why does my Embedded SQL run faster than my stored procedures? A: Re-compilation may be the reason why the stored procedure runs slowly. To confirm this, you need to perform some performance tests, such as checking the "showplan" situation of each type of queries and calling stored procedures, compare the high-speed cache hit rate and high-speed cache miss rate of the query plan. You can also write the object owner for the referenced tables, views, and procedures in the stored procedure. As shown in the following example: SELECT * FROMdbo. my Q: Why does my Embedded SQL or Embedded SQL run faster than my stored procedures?
A: Re-compilation may be the reason why the stored procedure runs slowly. To confirm this, you need to perform some performance tests, such as checking the "showplan" situation of each type of queries and calling stored procedures, compare the high-speed cache hit rate and high-speed cache miss rate of the query plan. You can also write the object owner for the referenced tables, views, and procedures in the stored procedure. As shown in the following example:
SELECT * FROM dbo. mytable
This helps you reuse the plan and prevent the lack of cache.