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_PER IOD
and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PER IOD
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
.
.
.
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
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_PER
and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_CHAN GE 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_OR G 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_PER IOD
and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PER IOD
-- ***** 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_AUTHORIZ ATION da, CORMIS_PROD..USER_GROUP_DE F 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.
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_CHAN
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
into #dvc
from CORMIS_PROD..DVC d, CORMIS_PROD..INSTALL_ID_OR
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_PER
and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PER
-- ***** 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_AUTHORIZ
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
increase points to be split
ASKER
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.
I accepted the answer from gletiecq, as he answered the original question.
You can do this:
select d.ORG_ID, d.REPORTING_TYPE_ID, d.REPORTING_PERIOD, d.FINANCIAL_PERIOD,
#incremental.CREATION_ID, #incremental.CREATION_DATE
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
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_PER
and d.FINANCIAL_PERIOD = #incremental.FINANCIAL_PER
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.