Re: 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

.

Relevant Pages

  • Re: index bloat?
    ... execution plan is, how can I get the query optimizer to generate it - *without the hints*? ... Because some users of the DB are using report designing software that will just generate the join query with no hints, and I don't want them sitting there for hours when they don't have to. ... Does the optimizer not choose the merge join because it requires a bookmark lookup? ...
    (comp.databases.ms-sqlserver)
  • Re: Frustrating Execution Plan Analysis
    ... >I've always been under the impression that Query cost is directly related to ... But if the execution plan isn't making sense, ... SQL Server will make a plan based on assumptions about what will ...
    (microsoft.public.sqlserver.programming)
  • Re: Execution plan reuse
    ... Is there way to execute SPs without actually sustaining the affects of it? ... response time of my SPs by keeping the execution plans in cache before the ... > An execution plan for a stored procedure can be reused by any connection ... >>I want to confirm my understanding of execution plan reusage. ...
    (microsoft.public.sqlserver.programming)
  • Re: Very slow after applying sp3!
    ... Dear Andrew ... I tried generating the execution plan but failed to do so because the ... I went a step further by diagnosting the store procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Indented Bill of Materials
    ... have no parent are inserted in a temp table, ... execution of the WHILE statement, the last executed statement is the SET ... In the first iteration, @lev will be increased to 1. ...
    (microsoft.public.sqlserver.programming)