Sp_executeSQL Perform Faster Than Stored Procedure, Why?

Hi,

I Have a stored procedure named pPatientFindByID that return one patient record from the Client.Patient table. The stored procedure has one parameter @patientID uniqueidentifier. i have set the NOCOUNT ON before running the SQL statement and making it OFF after it finishes execution. The table has a primary key ( clustered ) on patientID. The table has 66840 records in it. The index IS 0 (ZERO) percent fragmented and 3 level depth.

The stored proc returnes null or 1 record maximum. My problem is that the stored procedure takes about 2268 microsecond (2 millisecond) on average. while i execute the same SQL using sp_executeSQL it takes about 710 microsecond (< 1 millisecond). That is 3 time faster than stored procedure.

The SET OPTION are same for both the statement.

To troubleshoot the problem i have enabled the trace and captured the events. there were no recompile for both the stored proc and sp_executeSQL and not even the statement level recompilation.

I am trying to figure out why my stored procedure is taking longer than sp_executesql. Both the (stored proc and sp_executeSQL) has the same execution plan.


This is affecting our application performance. Does anyone know about this or have experience the same problem ever? your help would be greatly appriciated.

Data


1. Stored Procedure


/****** Object: StoredProcedure [dbo].[pPatientFindByID] Script Date: 12/24/2007 11:19:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[pPatientFindByID]

@patientID uniqueidentifier

WITH EXECUTE AS OWNER

AS

BEGIN

SET NOCOUNT ON



SELECT

[patient].[AVINumber] AS 'AVINumber',

[patient].[BirthDate] AS 'BirthDate',

[patient].[BreedID] AS 'BreedID',

[patient].[ChangeDate] AS 'ChangeDate',

[patient].[ChangeWorkstationID] AS 'ChangeWorkstationID',

[patient].[ClientID] AS 'ClientID',

[patient].[Color] AS 'Color',

[patient].[County] AS 'County',

[patient].[CountyRabiesTagNumber] AS 'CountyRabiesTagNumber',

[patient].[CreateDate] AS 'CreateDate',

[patient].[CreateWorkstationID] AS 'CreateWorkstationID',

[patient].[DeathDate] AS 'DeathDate',

[patient].[DeathReasonCode] AS 'DeathReasonCode',

[patient].[FirstVisitDate] AS 'FirstVisitDate',

[patient].[HasAllergy] AS 'HasAllergy',

[patient].[HasBitesOrScratch] AS 'HasBitesOrScratch',

[patient].[HasChronicCondition] AS 'HasChronicCondition',

[patient].[HasDeclinedPreventiveCare] AS 'HasDeclinedPreventiveCare',

[patient].[HasMedicalAllergy] AS 'HasMedicalAllergy',

[patient].[HospitalID] AS 'HospitalID',

[patient].[IsBites] AS 'IsBites',

[patient].[IsBitesInFear] AS 'IsBitesInFear',

[patient].[IsClinicalStudy] AS 'IsClinicalStudy',

[patient].[IsLitters] AS 'IsLitters',

[patient].[IsMixBreed] AS 'IsMixBreed',

[patient].[IsScratches] AS 'IsScratches',

[patient].[LastVisitDate] AS 'LastVisitDate',

[patient].[ManufacturerNameOfAVIChip] AS 'ManufacturerNameOfAVIChip',

[patient].[ManufacturerNameOfMicrochip] AS 'ManufacturerNameOfMicrochip',

[patient].[MicrochipNumber] AS 'MicrochipNumber',

[patient].[Name] AS 'Name',

[patient].[Note] AS 'Note',

[patient].[PatientID] AS 'PatientID',

[patient].[RabiesTagNumber] AS 'RabiesTagNumber',

[patient].[SexCode] AS 'SexCode',

[patient].[SpeciesID] AS 'SpeciesID',

[patient].[Status] AS 'Status',

[patient].[SystemLog] AS 'SystemLog',

[patient].[Weight] AS 'Weight',

[patient].[WellnessplanLevel] AS 'WellnessplanLevel',

[patient].[WellnessplanValue] AS 'WellnessplanValue'

FROM

[Client].[Patient] [patient]

WHERE

[PatientID] = @patientID

SET NOCOUNT OFF

END


2. sp_executeSQL


set nocount on

execute

sp_executeSQL N'

SELECT [patient].[AVINumber]

,[patient].[BirthDate]

,[patient].[BreedID]

, [patient].[ChangeDate]

, [patient].[ChangeWorkstationID]

, [patient].[ClientID]

, [patient].[Color]

, [patient].[County]

, [patient].[CountyRabiesTagNumber]

, [patient].[CreateDate]

, [patient].[CreateWorkstationID]

, [patient].[DeathDate]

, [patient].[DeathReasonCode]

, [patient].[FirstVisitDate]

, [patient].[HasAllergy]

, [patient].[HasBitesOrScratch]

, [patient].[HasChronicCondition]

, [patient].[HasDeclinedPreventiveCare]

, [patient].[HasMedicalAllergy]

, [patient].[HospitalID]

, [patient].[IsBites]

, [patient].[IsBitesInFear]

, [patient].[IsClinicalStudy]

, [patient].[IsLitters]

, [patient].[IsMixBreed]

, [patient].[IsScratches]

, [patient].[LastVisitDate]

, [patient].[ManufacturerNameOfAVIChip]

, [patient].[ManufacturerNameOfMicrochip]

, [patient].[MicrochipNumber]

, [patient].[Name]

, [patient].[Note]

, [patient].[PatientID]

, [patient].[RabiesTagNumber]

, [patient].[SexCode]

, [patient].[SpeciesID]

, [patient].[Status]

, [patient].[SystemLog]

, [patient].[Weight]

, [patient].[WellnessplanLevel]

, [patient].[WellnessplanValue]

FROM [Client].[Patient] [patient] WHERE [PatientID] = @patientID

',N'@patientID uniqueidentifier'

,'B1ABC1EA-D4AB-DB11-921E-00087434402F'





set nocount off

3- Trace Data



Name Event TextData Duration
SQL:BatchCompleted 12 exec dbo.pPatientFindByID @patientID='B1ABC1EA-D4AB-DB11-921E- 00087434402F' 2268
SQL:BatchCompleted 12 set nocount on execute sp_executeSQL N' SELECT [patient].[AVINumber] 710





DBA