Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issue Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 5:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:03 PM
Points: 60, Visits: 253
Hi,

I have two tables.
treatment_plan table having 8 fields like txt_instruct_Description_1,txt_instruct_Description_2....
As per requirement txt_instruct_Description_4,txt_instruct_Description_5,txt_instruct_Description_6,txt_instruct_Description_7,
txt_instruct_Description_8 field values should be moved to another table custom_plan.

custom_plan table having ,txt_addition_description_1,txt_addition_description_2,txt_addition_description_3,
txt_addition_description_4,txt_addition_description_5,txt_addition_description_6,txt_addition_description_7,
txt_addition_description_8 fields.

I want to move treatment_plan table 5 fields value to custom_plan 8 fields.

I have written the following the query.
But this query taking more time to execute.

Is there any other way to improve the performance or changing the code to execute fast. Please let me know.


-------------------------------------------------


SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM SYSOBJECTS SO WHERE SO.NAME = 'custom_plan')
IF EXISTS (SELECT 1 FROM SYSOBJECTS SO WHERE SO.NAME = 'treatment_plan')
BEGIN
declare @num_source_field int
declare @num_destination_field int
declare @source_field varchar(100)
declare @destination_field varchar(100)
declare @src_value varchar(75)
declare @dest_value varchar(75)

declare @strsql varchar(1000)


SET @num_source_field = 4
SET @num_destination_field = 1


select *, id = IDENTITY(INT,1,1) into #temp from treatment_plan



Declare @mx int
declare @mn int
Declare @encid varchar(45)
select @mx=max(id),@mn=min(id) from #temp

create table #tbl(col1 varchar(45))


while(@mn<=@mx)
BEGIN
select @encid= enc_id from #temp where id=@mn
SET @num_source_field=4
while(@num_source_field <= 8)
BEGIN

select @source_field = 'txt_instruct_description_'+cast(@num_source_field as varchar(2))

SET @num_destination_field = 1

while(@num_destination_field <= 8)
BEGIN
select @destination_field = 'txt_additional_description_'+cast(@num_destination_field as varchar(2))
truncate table #tbl
SET @strsql='insert into #tbl select '+@source_field+' from treatment_plan where enc_id='+''''+@encid +''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
select @src_value= col1 from #tbl

truncate table #tbl
SET @strsql='insert into #tbl select '+@destination_field+' from custom_plan where enc_id='+''''+@encid +''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
select @dest_value= col1 from #tbl

if(@dest_value is null)
begin
SET @strsql='update custom_plan SET '+@destination_field+'='+''''+@src_value+''''+' where enc_id='+''''+@encid+''''
--EXECUTE sp_executesql @strsql
exec(@strsql)
break
end

SET @num_destination_field=@num_destination_field+1
END

SET @num_source_field=@num_source_field+1
END
SET @mn=@mn+1
END
drop table #tbl
drop table #temp
END




Print '----------End----------'

SET NOCOUNT OFF

GO
--------------------------------------------------------

Thanks,
Tony
Post #1433152
Posted Wednesday, March 20, 2013 7:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
Solution is doing it row-by-row, no surprise that's painfull.
Why not design a set based solution? A single insert into select from statement should do it.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1433205
Posted Wednesday, March 20, 2013 10:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 27, 2014 10:03 PM
Points: 60, Visits: 253
Thanks for your comments.

If am going with While loop option, client is rejecting because they need good performance query.

How can i set this query like set based solution please let me know.
How can i make this query to use a single insert into select from statement, Could you Please suggest me in detail.

thanks,
Antony
Post #1433579
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse