T-SQL code in script run faster than it does as a stored procedure

Next>>         SQL Server tutorial  

SQL Server - T-SQL code in script run faster than stored procedure - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

T-SQL code in script run faster than it does as a stored procedure. Why?

This happens when query optimizer is not able o come up with consistent query plans because the index statistics for the table/s affected by code are not up to date.

Reindexing the tables and ensuring settings Auto create Statistics and Auto update Statistics are set to on will resolve the issue.

SQL Server - T-SQL code in script run faster than stored procedure - May 05, 2009 at 22:00 PM by Rajmeet Ghai

T-SQL code in script run faster than it does as a stored procedure. Why? 

This usually happens when index statistics are not up-to-date. They can be by reindexing them and using "Auto Create Statistics" and "Auto Update Statistics" options from database settings. These options should be turned ON. if the index statistics are not up-to-date different query plans are produced each time the query is executed. 

SQL Server - T-SQL code in script run faster than stored procedure - June 21, 2009 at 09:00 AM by Amit Satpute

T-SQL code in script run faster than it does as a stored procedure. Why?

This could happen in case where the Query Optimizer cannot come up with consistent query plans which could further be an effect of not updated index statistics for the table.

Steps to follow:

• Re-indexing the tables
• The database settings should be on: Auto Create Statistics and Auto Update Statistics.

This ensures the Query Analyzer select an optimum query plan to make a code perform well.

Next>>

Also read

SQL BETWEEN

SQL BETWEEN: The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.............. 

SQL CREATE VIEW

SQL CREATE VIEW: A view is a virtual table. A view contains rows and columns, just like a real table..............

SQL GROUP BY

GROUP BY: It is used with aggregate functions to group the result-set by one or more columns................

What are the advantages of using Stored Procedures?

Answer - Stored procedures provide performance benefits through local storage, precompiling the code, and caching......

What are the ways to code efficient transactions?

Answer - Database users can have permission to execute a stored procedure without being....

What are the differences among batches, stored procedures, and triggers?

Answer - A batch is a group of one or more SQL statements. SQL Server compiles the statements......

What security features are available for stored procedures?

Answer - Database users can have permission to execute a stored procedure without being......