Long compilation time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Long compilation time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-11 : 08:46:24

Sai writes "Dear experts,

I would appriciate if any one of you could help me or direct me to any resourses on the web for tips or guide lines to tune the Sql within in a stored procedure for reducing the long compilation times.

I searched the web but could not get any resourses on this topic "Tuning the sql within a Stored Procedure to reduce the compilation times"

For example I have a stored procedure that has

Compilation Time: 15 sec
Execution Time : 1 sec

I would like to reduce the Compilation Time(15 sec)

I am wondering what kind of sql statements within a stored procedure can cause the long parsing and compiling time??

Thanks
Sai"

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-11 : 14:16:51

http://www.sql-server-performance.com/stored_procedures.asp
Notes that it is the query execution plan that takes time to evaluate against large tables.

They recommend breaking out a large sp into component sp's
[blue]
CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO
[\blue]
The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query.

I can see the advantage of changing an sp that is specific to the development change which may not apply to a larger more complex sp.

Check out http://www.sql-server-performance.com
They have very useful information on performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources