Link to home
Start Free TrialLog in
Avatar of VB-tinkerer
VB-tinkerer

asked on

Problems with stored procedure and SELECT INTO

In a stored procedure I try to use two conditional SELECT INTO statements which select into the same temporary table, but they create an error.

.
.
.
if @inst_type_id = '1'
begin
  select d.ORG_ID, d.REPORTING_TYPE_ID, d.REPORTING_PERIOD, d.FINANCIAL_PERIOD,
         #incremental.CREATION_ID, #incremental.CREATION_DATE, EXTRACT = 0
    into #dvc
    from MAIN..DVC d, MAIN..INSTALL_ID_ORG io, MAIN..INSTALLATION_ID i, #auth, #incremental
    where i.ME_IND = 'Y'
      and i.INSTALLATION_ID = io.INSTALLATION_ID
      and io.ORG_ID = d.ORG_ID
      and io.ORG_ID = #auth.ORG_ID
      and d.ORG_ID = #incremental.ORG_ID
      and d.REPORTING_PERIOD = #incremental.REPORTING_PERIOD
      and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PERIOD
end

if @inst_type_id = '2'
begin
  select ORG_ID, REPORTING_TYPE_ID, REPORTING_PERIOD, FINANCIAL_PERIOD,
         CREATION_ID = null, CREATION_DATE = null, EXTRACT = 1
    into #dvc
    from MAIN..ORDER_ITEM_DVC
    where ORACLE_STATUS = -1
end
..
.


When I create the procedure I get the error message:
There is already an object named '#dvc' in the database.

I know I can use a create table #dvc ... first and then insert the records, but I understand from a performance point of view, it is better to use SELECT  .. INTO

Can this be achieved in a stored procedure?

We use Sybase ASE 12.5

ASKER CERTIFIED SOLUTION
Avatar of gletiecq
gletiecq

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rajesh009
rajesh009

Hi,

   You can do this:

 select d.ORG_ID, d.REPORTING_TYPE_ID, d.REPORTING_PERIOD, d.FINANCIAL_PERIOD,
         #incremental.CREATION_ID, #incremental.CREATION_DATE, EXTRACT = 0
   into #dvc
   from MAIN..DVC d, MAIN..INSTALL_ID_ORG io, MAIN..INSTALLATION_ID i, #auth, #incremental
where 0 = 1

if @inst_type_id = '1'
begin
Insert into #dvc
select d.ORG_ID, d.REPORTING_TYPE_ID, d.REPORTING_PERIOD, d.FINANCIAL_PERIOD,
         #incremental.CREATION_ID, #incremental.CREATION_DATE, EXTRACT = 0
from MAIN..DVC d, MAIN..INSTALL_ID_ORG io, MAIN..INSTALLATION_ID i, #auth, #incremental
   where i.ME_IND = 'Y'
     and i.INSTALLATION_ID = io.INSTALLATION_ID
     and io.ORG_ID = d.ORG_ID
     and io.ORG_ID = #auth.ORG_ID
     and d.ORG_ID = #incremental.ORG_ID
    and d.REPORTING_PERIOD = #incremental.REPORTING_PERIOD
     and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PERIOD
end

if @inst_type_id = '2'
begin
Insert into #dvc
 select ORG_ID, REPORTING_TYPE_ID, REPORTING_PERIOD, FINANCIAL_PERIOD,
         CREATION_ID = null, CREATION_DATE = null, EXTRACT = 1
     from MAIN..ORDER_ITEM_DVC
   where ORACLE_STATUS = -1
end
.
.
.

Thanks,
Rajesh.

   

Avatar of VB-tinkerer

ASKER

As I mentioned before, this is mainly a performance question.  I need to make this as quick as possible.  Currently takes 10 seconds to execute.  I create the table first and then insert the records.

For testing purposes I have split up the stored procedures into seperate procedures to be executed depending on the @inst_type_id, but would have liked to keep it as one. I now know, that the select into statement is about 3 seconds faster - which makes a difference in our environment.


If you have any other suggestions how I could speed up this procedure.... here is the full procedure after seperating into 2 procedures.

Thanks
Erich
____________________
create procedure SP_GET_OFA_EXTRACT_DVC_BU
  (
  @xi_user_id       char(20) = null
  )
as

select dc.ORG_ID, dc.REPORTING_TYPE_ID, dc.REPORTING_PERIOD, dc.FINANCIAL_PERIOD, cc.CREATION_DATE
  into #incremental
  from CORMIS_PROD..DATA_CHANGE dc, CORMIS_PROD..CREATION_CHANGE cc
  where dc.CREATION_ID = cc.CREATION_ID
group by dc.ORG_ID, dc.REPORTING_TYPE_ID, dc.REPORTING_PERIOD, dc.FINANCIAL_PERIOD
having cc.CREATION_DATE = max(cc.CREATION_DATE)
-- ***** approximatelx 7 seconds to here
-- ***** selects approximately 12000 records

select d.ORG_ID, d.REPORTING_TYPE_ID, d.REPORTING_PERIOD, d.FINANCIAL_PERIOD,
          #incremental.CREATION_DATE, EXTRACT = 0
into #dvc
    from CORMIS_PROD..DVC d, CORMIS_PROD..INSTALL_ID_ORG io, CORMIS_PROD..INSTALLATION_ID i, #incremental
    where i.ME_IND = 'Y'
      and i.INSTALLATION_ID = io.INSTALLATION_ID
      and io.ORG_ID = d.ORG_ID
      and d.ORG_ID = #incremental.ORG_ID
      and d.REPORTING_PERIOD = #incremental.REPORTING_PERIOD
      and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PERIOD
-- ***** approximatelx 13 seconds to here
-- ***** selects approximately 11500 records


if @xi_user_id is not null and @xi_user_id <> ''
begin
  delete #dvc
  where ORG_ID not in
    (select distinct da.ORG_ID
     from CORMIS_PROD..DATA_AUTHORIZATION da, CORMIS_PROD..USER_GROUP_DEF ugd
     where da.USER_GROUP_ID = ugd.USER_GROUP_ID
     and ugd.CORMIS_USER_ID = @xi_user_id
    )
end

update #dvc
  set EXTRACT = 1
  where EXTRACT <> 1
    and CREATION_DATE is null

update #dvc
  set EXTRACT = 1
  from #dvc x, CORMIS_PROD..DVC d
  where x.EXTRACT <> 1
    and x.ORG_ID = d.ORG_ID
    and x.REPORTING_TYPE_ID = d.REPORTING_TYPE_ID
    and x.FINANCIAL_PERIOD = d.FINANCIAL_PERIOD
    and x.REPORTING_PERIOD = d.REPORTING_PERIOD
    and x.CREATION_DATE is not null
    and x.CREATION_DATE < d.CHANGE_TIME

-- *** approx 14 seconds to here
select ORG_ID, REPORTING_TYPE_ID, REPORTING_PERIOD, FINANCIAL_PERIOD
from #dvc
where EXTRACT = 1
order by REPORTING_PERIOD, ORG_ID, REPORTING_TYPE_ID, FINANCIAL_PERIOD

return
go
________________

As indicated by the timeings given, it is the 2 select statements that take the time.
There is little chance of changing the tables or fields to make this faster.
Any ideas you have, would be greatly appreciated.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
increase points to be split
Thank you all for the help you gave me.  I have used ideas from all posts in amending the procedure and I am now happy with the performance.

I accepted the answer from gletiecq, as he answered the original question.