Query optimization

Question

I have an stored procedure of this format

if(condition 1)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end
if(condition 2)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end

now when the above stored procedure is execute it shows me an error that:

"There is already an object named '#temp1' in the database."

When I modify the stored procedure like,

if(condition 1)
begin
(
  -----
  -----
  select into #temp1
  ----
  drop #temp1
)
end
if(condition 2)
begin
(
  -----
  -----
  select into #temp2
  ----
  drop #temp2
)
end

It works good.But I want to optimize this because of creating too many temporary tables.

Can anybody help me on this?

Can you not do a `insert if not exists into ....`, What sql engine / server are you using ? — RobertPitt, Sep 15 '10 at 10:29
I presume condition 1 and condition 2 aren't mutually exclusive? Do `#temp1` and `#temp2` have the same structure? — Martin Smith, Sep 15 '10 at 10:57
can you confirm whether you are using SQLServer, and if so, which version? — , Sep 15 '10 at 14:39

score 0 · Answer 1 · answered Sep 15 '10 at 10:28

0

You can drop the table at the start of the procedure. It's a bit tricky, but you can check for the existence of a temporary table like:

if object_id('tempdb..#temp1') is not null
    drop table #temp1

answered Sep 15 '10 at 10:28

Andomar

232,371
49
380
404

score 0 · Answer 2 · answered Sep 15 '10 at 10:50

0

I'd create/drop the temp table outside of the conditional statement like this:

create table #temp ...

if(condition 1)
begin
(
  -----
  -----
  select into @temp
  ----
)
end
if(condition 2)
begin
(
  -----
  -----
  select into @temp2
  ----
)
end

drop table #temp

Assuming your version of SQL Server supports them, and you are aware of the differences in terms of logging and transactional rollback, it might be better to use a table variable. That way you don't have to worry about it not being dropped once it goes out of scope.

answered Sep 15 '10 at 10:50

MLT

524
7
19

Surely temporary tables should always be dropped at the end of the procedure, anyway? – Sep 15 '10 at 14:38
yes, but if it's the last thing the procedure does anyway then why write it twice? – MLT Sep 15 '10 at 16:17

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

You could try adding TRUNCATE TABLE #temp1 immediately before if(condition 2) in the first layout, but this question and accepted answer implies that a table variable will perform better.

edited May 23 '17 at 12:26

Community

1
1

answered Sep 15 '10 at 14:46

Query optimization

3 Answers3