March 3, 2003 at 11:51 am
My favorite stored proc takes about 10 seconds to run. It fills and uses temp tables during processing.
However, when I run it by calling
insert my_table exec my_favorite_sp param1, param2, param3
then it takes about 120 seconds to run. Compared to a call without "insert", the results are the same.
I found out that the slowdown is caused by a single step inside, while working on a temp table. So it's got nothing to do with initialising or cleaning up. And even worse: Query analyzer does not show the SPs query plan when I use insert-exec, so I can't check what's going on.
Any ideas ??
March 3, 2003 at 8:34 pm
Not sure I follow - you're saying overall its slower, or you've narrowed it to one line in the proc? For your temp tables do you explicitly create (good) or use select into (bad)?
Andy
March 4, 2003 at 1:47 am
> you're saying overall its slower, or
> you've narrowed it to one line in the proc?
I put various 'print getdate' statements into the proc and found out that a single 'insert #mytemptable select ... from #othertemptable' causes the problem.
There are about six or seven insert..select statements of this kind in the stored proc, but this single one takes about 100 times longer to execute when the stored proc is called vie insert..exec instead of exec alone.
> For your temp tables do you explicitly
> create (good) or use select into (bad)?
I'm trying to be a good SQL programmer and use explicit creates.
Does SQL Server use different query plans depending on whether you use 'exec' or 'insert exec' ?
March 4, 2003 at 9:49 am
Oh no !
It's the implict transaction for insert..exec, it makes SQL Server use a different query plan. Apparently statistics for temp tables are missing when the SP is executed within a transaction.
Does anybody know why ???
March 4, 2003 at 12:47 pm
I do not - hopefully someone else does. While possibly there is another fix, could you just make it a permanent table instead?
Andy
March 4, 2003 at 2:22 pm
I am also having a problem with Insert Exec. Last week we had one server with SQL 7.0 SP4. The local server is SQL2K SP2.When I ran insert exec of a sp_execute on the local(sql2k), it worked as fast as if I had just had the result set populate the QA window. Now I try to run the same insert exec from sql2k but the remote is now a sql2k SP2 box and the query never finishes. Works fine if I just display the results in QA instead of insert into.
I have tried the temp table/permanent table scenario and receive the same problem both ways. I have tried limiting the insert to only 1 record instead of the 1566 recs I need to no avail.
I have read everything Microsoft has written on linked servers and played with settings suggested in an August 2002 webcast they produced and it didn't work.
Michelle
Michelle
March 5, 2003 at 1:08 am
More news: Inside a transaction (and also inside an implicit insert..exec transaction) the SP does not have proper statistics on the tables. At least this is what the query plan in QueryAnalyzer shows.
When executed outside a transaction, the SP does have a table statistics.
So I tried an UPDATE STATISTICS inside the stored proc and guess what...
WITHOUT a transaction wrapping the SP, things work fine.
WITH a transaction, I get Error Message 226:
"UPDATE STATISTICS command not allowed within multi-statement transaction."
Why on earth is this ?????
March 6, 2003 at 2:46 pm
I think the statistics are still there but not being used. I experienced what you are seeing and this is my explanation.
If you are creating the result table whether permanent or temporary just prior to the call to the stored procedure, sql server will recompile the stored procedure and may be altering the execution plan in an undesirable way probably because it is a new table without statistics.
Try this. Create the result table that you want to execute into. Run the stored procedure without inserting the results into it. Run the stored procedure again inserting the results into the permanent table (this shouldn't cause recompilation and execution plan to change). See if execution times are comparable.
On another note.
I have seen where temporary tables were used to pass information to a stored procedure because SQL 7 didn't allow to pass tables as parameters. This worked well except for the fact that every time the stored procedure is called it is recompiled first.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply