Execution plan changes if inside/outside transaction

Hi,

let me introduce you the scenario of my problem:

I have a temporary table (#export_news). I insert some id's into this table. Then I call stored procedure which assumes existence of this temporary table. This stored procedure consists from one select statement in which is this temporary table inner joined (temporary table is used to restrict the output just to record I am interested in).

The problem is following:
If I create temporary table+insert some ids inside+call stored procedure !!!inside explicit transaction!!!, I got correct execution plan. (the query takes about 125ms).
If I am not in transaction, I will get incorrect execution plan. (than the query takes about 17s).

I have no clue have can execution inside transaction (in my case) affect execution plan. Any ideas?

Thanks for any suggestion...

If you have any question about environment/query/tables, please, let my know. I will be glad to provide them.? A couple of questions to clarify: Is the temp table created inside of the stored procedure, or before you call the stored procedure? Is the transaction started/committed inside of the stored procedure? Is the temp table created within the scope of the transaction? This sounds to me like a late binding problem, although I'm not 100% sure how that's handled in terms of statistics (lack of which would result in a poor execution plan). I'd be interested in hearing what would happen if you: A) Use a table variable instead of a temp table (assuming, of course, that the table is created in the procedure) B) If it's not created in the procedure, what happens if you move it in? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <onokles@.discussions.microsoft.com> wrote in message news:4a1ced38-217a-4c14-b02a-dc306a0c2b65_WBRev4_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi,let me introduce you the scenario of my problem:I have a temporary table (#export_news). I insert some id's into this table. Then I call stored procedure which assumes existence of this temporary table. This stored procedure consists from one select statement in which is this temporary table inner joined (temporary table is used to restrict the output just to record I am interested in). The problem is following: If I create temporary table+insert some ids inside+call stored procedure !!!inside explicit transaction!!!, I got correct execution plan. (the query takes about 125ms). If I am not in transaction, I will get incorrect execution plan. (than the query takes about 17s). I have no clue have can execution inside transaction (in my case) affect execution plan. Any ideas?Thanks for any suggestion...If you have any question about environment/query/tables, please, let my know. I will be glad to provide them.|||Thanks for answer. I did some deeper investigation and here is the result:

The execution plan depends on one factor:
Weather last insert into temp table is inside the same transaction in which is store procedure executed or not.

I am adding some pseudocode to make myself clear:

1)--execution plan is WRONG

CREATE TABLE #tmp
INSERT INTO #tmp VALUES(some_ids)

BEGIN TRAN
EXEC stored_procedure
COMMIT TRAN

2)--execution plan is OK (because of insert into #tmp inside the transaction)

CREATE TABLE #tmp
INSERT INTO #tmp VALUES(some_ids)

BEGIN TRAN
INSERT INTO #tmp VALUES(some_other_ids)
EXEC stored_procedure
COMMIT TRAN

3)--execution plan is WRONG (I don’t know why, but when I delete from #tmp ID inside transaction and than add the same ID again, the execution plan is still wrong)

CREATE TABLE #tmp
INSERT INTO #tmp VALUES(some_ids)
INSERT INTO #tmp VALUES(id_X)

BEGIN TRAN
DELETE FROM #tmp WHERE row_id = id_X
INSERT INTO #tmp (id_X)
EXEC stored_procedure
COMMIT TRAN

It doesn’t matter, weather #tmp is temporary table or regular table. The behavior is the same. There are no explicit transaction inside stored_procedure.

Any clue? I am getting desperate(an of course curious :-) )