Recompilation in stored procedure

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 2005 Forums

Transact-SQL (2005)

Recompilation in stored procedure

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-24 : 02:57:46

Hi, this is my proc

ALTER procedure [dbo].[Pr_Search]
(
@ProviderName VARCHAR(100) = NULL,
@PatientUserID BIGINT = NULL,
@OrganizationName VARCHAR(100) = NULL,
@TestName VARCHAR(100) = NULL,
@TestType VARCHAR(100) = NULL,
@OrderDate VARCHAR (100) = NULL,
@Status VARCHAR(15) = NULL
)
AS
BEGIN
/*********************************************************************************
Name: Pr_Search

Author:

Purpose: Gets List Based on Search Criteria

Project:

Module:

Notes:

CAUTION: Dynamic Stored Procedure. Sql Injection.

Perf: Avoids Recompilation Due to Temp Table
Avoids Complile Locks

Called by: End User

Date Initials Description Modified By
----------------------------------------------------------------------------
2010-03024 MFU Performance
2010-03-22 MFU Created
*********************************************************************************/
SET NOCOUNT ON

DECLARE @MainDynSql NVARCHAR(2000)
DECLARE @CondDynSql NVARCHAR(1000) = 'L.PatientId=@ParPatientUserID' + SPACE(1)
DECLARE @MaxCount INT
DECLARE @ParmDefinition NVARCHAR(500) = '@ParPatientUserID BIGINT, @ParProviderName VARCHAR(100),@ParOrganizationName VARCHAR(100),
@ParTestName VARCHAR(100), @ParTestType VARCHAR(100), @ParOrderDate VARCHAR(100),
@ParStatus VARCHAR(15)'

CREATE TABLE #TMPOrderMisc
(
[LabId] BIGINT,
[TypeName]VARCHAR(150),
[CreatedDate] DATETIME,
[OrderedBy] VARCHAR(150),
[Type] VARCHAR(150),
[PatientId] BIGINT,
[IsAttached] BIT
)

SET @MainDynSql =
'SELECT L.LabTestId,L.TestType,L.OrderDate,docUsr.FirstName,''Lab Test'',L.PatientId,[IsAttached]=0
FROM
dbo.USHR_OrderLabTest L
JOIN dbo.USHR_Users docUsr on L.OrderingDoctor = docUsr.UserId
WHERE '

--Test Type Search
IF(@TestType IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + 'L.TestType LIKE @ParTestType + ''%'' '
END

--Test Location Search
IF(@TestName IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + 'L.TestName LIKE @ParTestName + ''%'' '
END

--Organization Search
IF(@OrganizationName IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + 'L.Institution = @ParOrganizationName '
END

--Ordering Doctor Firstname , LastName search Search
IF(@ProviderName IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + '(docUsr.FirstName LIKE @ParProviderName + ''%''
OR docUsr.LastName LIKE @ParProviderName + ''%'') '
END

--Date Range Desc Search
IF(@Status IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + 'L.Status LIKE @ParStatus + ''%'' '
END

--Order Date Range Search
IF(@OrderDate IS NOT NULL)
BEGIN
SET @CondDynSql = @CondDynSql + 'AND ' + '(L.OrderDate >= @ParOrderDate AND L.OrderDate < DATEADD(DAY, 1, @ParOrderDate))' + SPACE(1)
END

SET @MainDynSql = @MainDynSql + @CondDynSql

INSERT INTO #TMPOrderMisc
EXECUTE SP_EXECUTESQL @MainDynSql, @ParmDefinition,
@ParPatientUserID = @PatientUserID, @ParProviderName = @ProviderName,@ParOrganizationName = @OrganizationName,
@ParTestName = @TestName, @ParTestType = @TestType, @ParOrderDate = @OrderDate,
@ParStatus = @Status

CREATE CLUSTERED INDEX IND_TMP_LabId ON #TMPOrderMisc(LabId)

SELECT T.LabId,T.TypeName,T.CreatedDate,T.OrderedBy,T.Type ,T.PatientId,[IsAttached]=CAST(CASE WHEN Att.MiscTestId IS NULL THEN 0 ELSE 1 END AS BIT)
FROM #TMPOrderMisc T
LEFT OUTER JOIN
(SELECT MiscTestId,[PartitionId]=ROW_NUMBER() OVER (PARTITION BY MiscTestId ORDER BY MiscTestId) FROM dbo.USHR_OrderMiscAttachments) Att
ON T.LabId = Att.MiscTestId AND Att.PartitionId = 1

END

i have used sp_executesql and schema to avoid recompilation. anything i have to do more to avoid recompilation

Iam a slow walker but i never walk back

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 03:53:56

I wonder if you need to get the ROWNUMBER() OVER code into the dynamic SQL too?

Are you seeing recompilation on this Sproc?

Personally I would define the Clustered Index on the empty table, and then ORDER BY the dynamic SQL in that order - so it inserts in Clustered Index sequence. But I have no idea if it would make any difference! just my personal preference.

I would be inclined to define @MainDynSql (and probably the other working variables too) as NVarchar(4000) - i.e. the maximum (without resorting to Nvarchar(MAX) which may have performance implications) just to shield yourself from some condition (in future maintenance perhaps) from breaking that

I would also put a Collation on the VARCHAR column definitions in the TEMP table to that they match those in the database - in case the Server Collation changes in the future. If this is on a single machine that you have control over that is probably irrelevant.

If you were able to upgrade to SQL 2008 (SP1 and latest Cumulative Update as I type this, but SP2 will include that) then you could avoid using dynamic SQL for this.

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-24 : 05:29:55

quote:
Originally posted by Kristen

I wonder if you need to get the ROWNUMBER() OVER code into the dynamic SQL too?

Are you seeing recompilation on this Sproc?

Personally I would define the Clustered Index on the empty table, and then ORDER BY the dynamic SQL in that order - so it inserts in Clustered Index sequence. But I have no idea if it would make any difference! just my personal preference.

I would be inclined to define @MainDynSql (and probably the other working variables too) as NVarchar(4000) - i.e. the maximum (without resorting to Nvarchar(MAX) which may have performance implications) just to shield yourself from some condition (in future maintenance perhaps) from breaking that

I would also put a Collation on the VARCHAR column definitions in the TEMP table to that they match those in the database - in case the Server Collation changes in the future. If this is on a single machine that you have control over that is probably irrelevant.

If you were able to upgrade to SQL 2008 (SP1 and latest Cumulative Update as I type this, but SP2 will include that) then you could avoid using dynamic SQL for this.

Thanks for reply. ok fine i would do order by indexed column of temp table. how to implement collation for varchar column in temp table.
Right now my sp is not getting recompiled. i checked with this query

SELECT TOP 10
qs.plan_generation_num,
qs.execution_count,
DB_NAME(st.dbid) AS DbName,
st.objectid,
st.TEXT
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
ORDER BY plan_generation_num DESC

Iam a slow walker but i never walk back

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 05:42:12

[code]
CREATE TABLE #TMPOrderMisc
(
[LabId] BIGINT,
[TypeName]VARCHAR(150) COLLATE Your Collation Name Here,
[CreatedDate] DATETIME,
[OrderedBy] VARCHAR(150) COLLATE Your Collation Name Here,
[Type] VARCHAR(150) COLLATE Your Collation Name Here,
[PatientId] BIGINT,
[IsAttached] BIT
)[/code]

Find our what the collation is on your normal database tables (scripting them should show you)

If any of these columns can NOT be NULL then I recommend adding "NOT NULL" to the definition - it may help the query optimiser.

" Right now my sp is not getting recompiled"

That sounds good then

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources