execution plan for a SP with transaction differs from one without transaction

Hi.. i'm trying with a simple stored procedure that bulk inserts in a
temp table and that write into another table from the temp one..
the execution plan is the same.. (except begin and rollback tran, that
indicate new plan rows..)
Try to post sp code, plz.. maybe we can see if there's a particular
case..

Nedo wrote:
> hi
>
> i have this sql statement:
> USE myDB
> --begin tran t1
> exec mySP 'parameters'
> --rollback tran t1
>
> (info: i want to rollback the transaction because i just want to test
> the SP and see the execution plan)
>
> the execution plan with the two transaction lines commented differs
> completely from the execution plan if i uncomment the two lines
>
> (info about the SP: it is creating a temporary table, doing a bulk
> insert into it, and then doing an insert in a normal table from the
> temporary table - simplified said)
>
> i dont understand why the transactioning is affecting the execution
> plan so extremly
>
> thanks
>
> regards
> nedo

thank you for your answer

i just tested it again and i don't have the problem anymore
i think there are two possible reasons why i had the problem yesterday:
- it was a problem of too less memory, because i use a "local temporary
table" in the SP:
CREATE TABLE #BulkLoadTable
(
....
)

- it was because i use this code for insertion:
EXEC sp_ExecuteSQL 'BULK INSERT #BulkLoadTable FROM ...'



here is the complete code of the SP:



---------- CODE ----------
CREATE PROCEDURE [dbo].[CRM_BulkLoadEMail]
@i_iCampaignExtensionId integer,
@i_iEntryType integer,
@i_vchLoadFile nvarchar(255),
@i_vchRowTerminator nvarchar(50),
@i_vchFieldTerminator nvarchar(50),
@i_tiGetParameterLength tinyint,
@o_iLoadedRows integer OUTPUT,
@i_dtQueueTime datetime = Null
AS
/*
** ObjectName: CRM_BulkLoadEMail
**
** Description: This stored procedure performs a bulk insert for an
email
** outbound operation.
*/

BEGIN
DECLARE @iReturnCode integer,
@iError integer

DECLARE @vchBatchSize nvarchar(15)
DECLARE @vchSQLBulkInsert nvarchar(4000)
DECLARE @chC4EntityType char(1)
Declare @iPermissionCode integer
Declare @iResponseYes integer
Declare @iSubSurveyId integer

SET NOCOUNT ON

SET @iReturnCode = 0

-- try to get the BatchSize (Commit Frequency for the Bulk Insert)
SELECT
@vchBatchSize = itemValue
FROM
CRMConfig
WHERE
serviceName = 'CRMFeeder' AND
moduleName = 'OutboundEMail' AND
itemName = 'BatchSize' AND
itemIdx = 0

-- try to get the PermissionCode
Select
@iPermissionCode = CAST( vchParameter As integer )
From
CRMCampaign_CampaignExtensionParameter
Where
iCampaignExtensionId = @i_iCampaignExtensionId And
vchParameterDesc = 'PermissionCode'

-- try to get the Yes Response Id
Select
@iResponseYes = CAST( itemValue As integer )
From
CRMConfig
Where
serviceName = 'PermissionMarketing' And
moduleName = 'SurveyHandling' And
itemName = 'Yes ResponseId' And
itemIdx = 0

-- try to get the Subscription Survey Id for Permissions
Select
@iSubSurveyId = CAST( itemValue As integer )
From
CRMConfig
Where
serviceName = 'PermissionMarketing' And
moduleName = 'SurveyHandling' And
itemName = 'SubscriptionSurvey_Id' And
itemIdx = 0

-- create a private temp table, this is necessary to be avoid
conflicts
-- if more than one load operation is performed concurrently.
CREATE TABLE #BulkLoadTable
(
entryId integer PRIMARY KEY,
msisdn varchar(255),
PARA0 varchar(255),
PARA1 varchar(255),
PARA2 varchar(255),
PARA3 varchar(255),
PARA4 varchar(255),
PARA5 varchar(255),
PARA6 varchar(255),
PARA7 varchar(255),
PARA8 varchar(255),
PARA9 varchar(255),
PARA10 varchar(255),
PARA11 varchar(255),
PARA12 varchar(255),
PARA13 varchar(255),
PARA14 varchar(255),
PARA15 varchar(255),
PARA16 varchar(255),
PARA17 varchar(255),
PARA18 varchar(255),
PARA19 varchar(255)
)

-- create an index for checking the email address (column msisdn)
-- for CR and LF
CREATE NONCLUSTERED INDEX BulkLoadTable_MSISDN ON #BulkLoadTable
( msisdn )

-- fill the private temp Bulk load table
SET @vchSQLBulkInsert = 'BULK INSERT #BulkLoadTable FROM ''' +
@i_vchLoadFile + ''' WITH ( ROWTERMINATOR = ''' + @i_vchRowTerminator +
''', FIELDTERMINATOR = ''' + @i_vchFieldTerminator + ''', KEEPNULLS,
CODEPAGE = ''ACP'''
If NOT @vchBatchSize IS NULL
begin
SET @vchSQLBulkInsert = @vchSQLBulkInsert + ', BATCHSIZE = ' +
@vchBatchSize + ' )'
end
else
begin
SET @vchSQLBulkInsert = @vchSQLBulkInsert + ' )'
end

EXEC sp_ExecuteSQL @vchSQLBulkInsert

-- remove all entries where CRs or LFs are found in the email address
-- (msisdn column), because this entries wont be processed correct
DELETE FROM
#BulkLoadTable
WHERE
msisdn LIKE '%' + CHAR(10) + '%' OR
msisdn LIKE '%' + CHAR(13) + '%'

-- retrieve entry-type
declare @vchParameterDesc nvarchar(50)
select @vchParameterDesc = vchParameterDesc
from referencedefinition
where
iparameterid = @i_iEntryType
and tirecordstatus = 1

-- set the C4EntityType
if @vchParameterDesc = 'C4 Account Id'
begin
SET @chC4EntityType = 'A'
end -- else if @vchParameterDesc = 'C4 Account Id'
else if @vchParameterDesc = 'C4 Customer Id'
begin
SET @chC4EntityType = 'C'
end -- else if @vchParameterDesc = 'C4 Customer Id'
else if @vchParameterDesc = 'C4 Subscriber Id'
begin
SET @chC4EntityType = 'S'
end -- else if @vchParameterDesc = 'C4 Subscriber Id'
else
begin
SET @chC4EntityType = 'O'
end -- if @vchParameterDesc = 'C4 Account Id'

-- generate the LoaderQueue entries for the loaded email items
-- check if the queue time is got
If @i_dtQueueTime Is Null
Begin
Set @i_dtQueueTime = GetDate()
End -- If @i_dtQueueTime Is Null

-- determine if the PermissionCode is to be used or not
If IsNull( @iPermissionCode, 0 ) = 0
Begin
-- no Permission Code is used
-- join to onyx-tables to avoid load of unknown entries
INSERT INTO CRMCampaign_LoaderQueue
( iCampaignExtensionId,
iEntryType,
entryId,
msisdn,
statusCode,
queueTime,
lastChanged )
SELECT
@i_iCampaignExtensionId as iCampaignExtensionId,
@i_iEntryType AS iEntryType,
entryId,
CAST( msisdn AS varchar(50) ) AS 'msisdn',
'D' as statusCode,
@i_dtQueueTime AS 'queueTime',
GetDate() AS lastChanged
FROM
#BulkLoadTable blt
JOIN
(
SELECT iCompanyId AS iOwnerId FROM Company WHERE tiRecordStatus <>
0
UNION
SELECT iIndividualId AS iOwnerId FROM Individual WHERE
tiRecordStatus <> 0
) Owner ON (Owner.iOwnerid = dbo.CRM_GetOnyxIdFromC4Id(
@chC4EntityType, blt.EntryId ) )
End -- If IsNull( @iPermissionCode, 0 ) = 0
Else
Begin
-- Permission Code is used, this means extra joins to survey system
-- join to onyx-tables to avoid load of unknown entries
INSERT INTO CRMCampaign_LoaderQueue
( iCampaignExtensionId,
iEntryType,
entryId,
msisdn,
statusCode,
queueTime,
lastChanged )
SELECT
@i_iCampaignExtensionId as iCampaignExtensionId,
@i_iEntryType AS iEntryType,
entryId,
CAST( msisdn AS varchar(50) ) AS 'msisdn',
'D' as statusCode,
@i_dtQueueTime AS 'queueTime',
GetDate() AS lastChanged
FROM
#BulkLoadTable blt
JOIN
(
SELECT iCompanyId AS iOwnerId FROM Company WHERE tiRecordStatus <>
0
UNION
SELECT iIndividualId AS iOwnerId FROM Individual WHERE
tiRecordStatus <> 0
) Owner ON (Owner.iOwnerid = dbo.CRM_GetOnyxIdFromC4Id(
@chC4EntityType, blt.EntryId ) )
JOIN CustomerProfile CP WITH ( NoLock, Index(
NNXCustomerProfile_ownerid ) ) ON
CP.iOwnerId = Owner.iOwnerId And
CP.iSurveyId = @iSubSurveyId And
CP.tiRecordStatus = 1
JOIN CustomerProfileQuestion CPQ With ( NoLock ) ON
CPQ.iProfileId = CP.iProfileId And
CPQ.iQuestionId = @iPermissionCode And
CPQ.iResponseId = @iResponseYes
End -- else of: If IsNull( @iPermissionCode, 0 ) = 0

-- fill the email processing table
INSERT INTO CRMCampaign_BulkLoaderQueueParameter
( iCampaignExtensionId,
iLoaderQueueId,
msisdn,
PARA0,
PARA1,
PARA2,
PARA3,
PARA4,
PARA5,
PARA6,
PARA7,
PARA8,
PARA9,
PARA10,
PARA11,
PARA12,
PARA13,
PARA14,
PARA15,
PARA16,
PARA17,
PARA18,
PARA19 )
SELECT
@i_iCampaignExtensionId AS iCampaignExtensionId,
lq.iLoaderQueueId AS iLoaderQueueId,
CAST( blt.msisdn AS varchar(50)) AS msisdn,
blt.PARA0,
blt.PARA1,
blt.PARA2,
blt.PARA3,
blt.PARA4,
blt.PARA5,
blt.PARA6,
blt.PARA7,
blt.PARA8,
blt.PARA9,
blt.PARA10,
blt.PARA11,
blt.PARA12,
blt.PARA13,
blt.PARA14,
blt.PARA15,
blt.PARA16,
blt.PARA17,
blt.PARA18,
blt.PARA19
FROM
#BulkLoadTable blt
JOIN CRMCampaign_LoaderQueue lq ON
lq.iCampaignExtensionId = @i_iCampaignExtensionId AND
lq.iLoaderQueueId = dbo.CRM_GetiLoaderQueueIdFromEntryId(
@i_iCampaignExtensionId, blt.entryId )

-- get the number of loaded rows
SET @o_iLoadedRows = @@ROWCOUNT

-- check if the max length of the parameters are to calculate
If @i_tiGetParameterLength = 1
BEGIN
SELECT
ISNULL( MAX(Len(PARA0)), 0 ) AS PARA0_Len,
ISNULL( MAX(Len(PARA1)), 0 ) AS PARA1_Len,
ISNULL( MAX(Len(PARA2)), 0 ) AS PARA2_Len,
ISNULL( MAX(Len(PARA3)), 0 ) AS PARA3_Len,
ISNULL( MAX(Len(PARA4)), 0 ) AS PARA4_Len,
ISNULL( MAX(Len(PARA5)), 0 ) AS PARA5_Len,
ISNULL( MAX(Len(PARA6)), 0 ) AS PARA6_Len,
ISNULL( MAX(Len(PARA7)), 0 ) AS PARA7_Len,
ISNULL( MAX(Len(PARA8)), 0 ) AS PARA8_Len,
ISNULL( MAX(Len(PARA9)), 0 ) AS PARA9_Len,
ISNULL( MAX(Len(PARA10)), 0 ) AS PARA10_Len,
ISNULL( MAX(Len(PARA11)), 0 ) AS PARA11_Len,
ISNULL( MAX(Len(PARA12)), 0 ) AS PARA12_Len,
ISNULL( MAX(Len(PARA13)), 0 ) AS PARA13_Len,
ISNULL( MAX(Len(PARA14)), 0 ) AS PARA14_Len,
ISNULL( MAX(Len(PARA15)), 0 ) AS PARA15_Len,
ISNULL( MAX(Len(PARA16)), 0 ) AS PARA16_Len,
ISNULL( MAX(Len(PARA17)), 0 ) AS PARA17_Len,
ISNULL( MAX(Len(PARA18)), 0 ) AS PARA18_Len,
ISNULL( MAX(Len(PARA19)), 0 ) AS PARA19_Len
FROM
CRMCampaign_BulkLoaderQueueParameter
WHERE
iCampaignExtensionId = @i_iCampaignExtensionId
END -- If @i_tiGetParameterLength = 1

DROP TABLE #BulkLoadTable

-- Error Checking
Set @iError = @@Error
if @iError <> 0
begin
exec @iReturnCode = ospCheckError N'I', @iError
end -- if @iError <> 0

RETURN @iReturnCode
END
---------- CODE ----------




regards
nedo






sux_stellino schrieb:

> Hi.. i'm trying with a simple stored procedure that bulk inserts in a
> temp table and that write into another table from the temp one..
> the execution plan is the same.. (except begin and rollback tran, that
> indicate new plan rows..)
> Try to post sp code, plz.. maybe we can see if there's a particular
> case..
>
> Nedo wrote:
> > hi
> >
> > i have this sql statement:
> > USE myDB
> > --begin tran t1
> > exec mySP 'parameters'
> > --rollback tran t1
> >
> > (info: i want to rollback the transaction because i just want to test
> > the SP and see the execution plan)
> >
> > the execution plan with the two transaction lines commented differs
> > completely from the execution plan if i uncomment the two lines
> >
> > (info about the SP: it is creating a temporary table, doing a bulk
> > insert into it, and then doing an insert in a normal table from the
> > temporary table - simplified said)
> >
> > i dont understand why the transactioning is affecting the execution
> > plan so extremly
> >
> > thanks
> >
> > regards
> > nedo