sp_executesql running very slowly

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

Transact-SQL (2000)

sp_executesql running very slowly

Author

Topic

Welcom Jam
Starting Member

3 Posts

Posted - 2008-12-12 : 06:28:27

Hello, I'm hoping that someone can help. I'm doing some optimisation work on an inherited website and noticed that there is a large amount of activity generated by sp_executesql calls in the database. I have, what seems on the surface, an innocuous stored procedure:

==========================

CREATE PROCEDURE dbo.ctlg_GetPrimaryParentCategory
(
@fVirtualCatalog bit,
@CatalogName nvarchar(85),
@BCName nvarchar(85),
@Oid int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CatalogView_tmp sysname
DECLARE @Query_tmp nvarchar(4000)

SET @CatalogView_tmp = dbo.fn_GetCatalogObjectName(N'CNV',@CatalogName,NULL)

if (@fVirtualCatalog =0 )
BEGIN
-- Get the PrimaryParent category
SET @Query_tmp = N'SELECT ISNULL(PrimaryParentCategory, N'''') AS PrimaryParentCategory
From '+@CatalogView_tmp+N' WHERE oid = @oid AND BaseCatalogName = @BCName'
END
ELSE
-- For a virtual catalog do a join with the vc view so that if the primary parent category is not included
-- in the virtual catalog it will not be returned
BEGIN

SET @Query_tmp = N'SELECT ISNULL(P2.CategoryName, N'''') As PrimaryParentCategory
FROM '+@CatalogView_tmp + N'P1 LEFT OUTER JOIN ' + @CatalogView_tmp + N' P2
ON P1.PrimaryParentCategory = P2.CategoryName
WHERE P1.BaseCatalogName = @BCName AND P1.oid = @oid '
END
EXEC sp_executesql @Query_tmp, N'@BCName nvarchar(85), @oid int', @BCName, @oid
END
GO
==========================

Now what I've done is break down the dynamic sql into the actual query that is being executed:

==========================
DECLARE @BCName nvarchar(85)
DECLARE @Oid int

SELECT @Oid = 46891, @BCName = 'BaseCatalog'

SELECT ISNULL(P2.CategoryName, N'''') As PrimaryParentCategory
FROM VirtualCatalog_View P1 LEFT OUTER JOIN VirtualCatalog_View P2
ON P1.PrimaryParentCategory = P2.CategoryName
WHERE P1.BaseCatalogName = @BCName AND P1.oid = @oid

==========================

The stored procedure takes a few seconds to run, while the query I created to fetch the same data is virtually immediate.

How can I get the execution time of the stored procedure down? All the dynamic sql is doing is joining on two views. All the tables are correctly indexed. Any ideas or help is appreciated.

Thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-12 : 06:38:56

you can log into a table, insert system date at different stages throught your sp. You might also want to look on parameter sniffing in your case.

Welcom Jam
Starting Member

3 Posts

Posted - 2008-12-12 : 07:11:34

Thanks for the quick reply. After reading up on parameter sniffing I don't feel that this is the problem. The stored procedure is equally bad when I run the t-sql through analyser.

Here are some stats from profiler.

Stored procedure ctlg_GetPrimaryParentCategory
==============================================
CPU: 453, Reads: 1659, Duration: 903

T-SQL query retrieving exactly the same data
============================================
CPU: 0, Reads: 219, Duration: 16

Any other ideas? It's only one join and returns only one field of data. It's not an obscenely big view, only 40,000 records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources