Optimization Required - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimization Required

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-14 : 06:43:14

I have a table that has 175,000 rows and 13 rows for each row in the child table i.e. the child table is holding 175,000 x 13 = 22,75000.

Now I want to make a single row for the parent table. So I have used the code below but its taking more than 6 hours which is not acceptable please guide me if the could can be optimized

if object_id('sp_export') is not null
drop proc sp_export
go
create proc sp_export
@sid int,
@from_date datetime,
@to_date datetime
as
begin
set nocount on

--Clean the Export table
--truncate table Export

declare @pid int, @qid int, @response varchar(750), @sql varchar(8000)

--Update crosstab table for each participant
declare cur_p cursor
fast_forward
for
select distinct r.pid
from re r join pall p on r.pid = p.pid
where r.sid = @sid and
p.stime between @from_date and @to_date
order by r.pid

open cur_p
fetch next from cur_p
into @pid

set @sql = ''
while @@fetch_status = 0
begin

declare cur_q cursor
fast_forward
for
select distinct qid
from resall r join partall p on r.pid = p.pid
where r.pid = @pid

open cur_q
fetch next from cur_q
into @qid

while @@fetch_status = 0
begin
select top 1 @response = isnull(response, '')
from resall r join partall p on r.pid = p.pid
where r.pid = @pid and qid = @qid

set @response = replace(@response, '''', '')

set @sql = @sql + '[' + cast(@qid as varchar) + '] = ''' + @response + ''','

fetch next from cur_q
into @qid
end
close cur_q
deallocate cur_q

set @sql = left(@sql,len(@sql)-1)
exec('update Export set ' + @sql + ' where pid = ' + @pid)
set @sql = ''

fetch next from cur_p
into @pid
end
close cur_p
deallocate cur_p
end

Kristen
Test

22859 Posts

Posted - 2005-11-14 : 22:53:44

I imagine that nested cursor is killing the server, plus the row-by-row dynamic SQL it is generating. Can you not just use an UPDATE?

If you want to make a concatenated list of values in a correlated child record, create a function that creates the list, and then the update can reference the returned list from the function.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-11-14 : 23:03:50

quote:

Now I want to make a single row for the parent table. So I have used the code below but its taking more than 6 hours which is not acceptable please guide me if the could can be optimized

Why are you doing this? I hope this is some crazy report you're putting together and you're not re"designing" the database this way. If you are, please stop and buy a good book on database design.

Can you give us some sample data of what you're trying to accomplish? That would help us out and enable us to give you a good answer quicker. Generally, if you are using dynamic SQL, using cursors, and flattening out databases you are doing something terribly wrong. There might be a good reason for it though, so sample DDL and DML would help greatly.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-11-14 : 23:14:28

"I hope this is some crazy report you're putting together ..."

I took heart from the embedded comment:

"--Update crosstab table for each participant"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources