iterate through temporary table ..

Dear Experts,

I am new to db queries.  

I have a situation where i have to iterate through each row of a temp table in sybase and update another table based on the temptables value.

I am able to do this through cursors (fetch the values of temptable and update the second table), but it is taking too long to execute.

Is there a way that i can iterate through the temptable using a while loop or similar.  

scenario:

declare cursor temp_cur for
select colA, colB, colC from tableA where colA = 'something'

open cursor temp_cur

fetch into .. .

if @@sqlstatus = 0
begin
   <i have some statements for every value of colA here>
end

can i just loop through the temp table instead ?

something like

select cola, colb, colC into #temptable1 where colA = 'something'

while #temptable1.eof .. :) (plz forgive this poor ASP programmer)

If yes, how do i do it ?



thanks,

SadagopankAsked:
Who is Participating?
 
grant300Commented:
You can easily do this in a single query with no cursors (which are bad anyway) and no per-record temp tables.  The trick is to structure it as a union of the three sections you have in the existing procedure and get rid of the cursor by including the equip_type table in each of the select statements as a JOIN.

I will show you the pattern for the query but I don't have time to rewrite your whole procedure for you.

Here are the things the query will do:
1) Add the EQUIP_TYPE table to each of your three SELECT statements making them JOINS
2) Add dummy columns with Zeros (and matching column names) in each query so that you have the same set of
    columns from all three queries.
3) calculate the individual columns in each section based on the bandwidth column using CASE statements
4) Connect all three queries with a UNION ALL so you get one result set back
5) Change the query (first select in the string of three) to be INTO a temporary table

So at this point you have something like
  SELECT no, equipment, model, CLLI,
              case(bandwidth = 'ds1' then ds1wired else 0 end) ds1wired,
              case(bandwidth = 'ds3' then ds3wired else 0 end) ds3wired,
              case(bandwidth = 'oc12' then oc12wired else 0 end) oc12wired,
              case(bandwidth = 'oc3' then oc3wired else 0 end) oc3wired,
              0 ds1equipped, 0 ds3equipped, 0 oc12equipped, 0 oc3equipped,
              0 ds1inuse, 0 ds3inuse, 0 oc12inuse, 0 oc3inuse
    INTO #temp1
     FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ...............
UNION ALL
  SELECT no, equipment, model, CLLI,
              0, 0, 0, 0,
              case(bandwidth = 'ds1' then ds1equipped else 0 end) ds1equpped,
              case(bandwidth = 'ds3' then ds3equipped else 0 end) ds3equipped,
              case(bandwidth = 'oc12' then oc12equipped else 0 end) oc12equipped,
              case(bandwidth = 'oc3' then oc3equipped else 0 end) oc3equipped,
              0, 0, 0, 0
    FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ..............
UNION ALL
  SELECT no, equipment, model, CLLI,
              0, 0, 0, 0,
              0, 0, 0, 0,
              case(bandwidth = 'ds1' then ds1inuse else 0 end) ds1inuse,
              case(bandwidth = 'ds3' then ds3inuse else 0 end) ds3inuse,
              case(bandwidth = 'oc12' then oc12inuse else 0 end) oc12inuse,
              case(bandwidth = 'oc3' then oc3inuse else 0 end) oc3inuse
     FROM  EQUIP, EQUIP_TYPE, .......
   WHERE ..............

Your result set is returned with the following query:

SELECT no, equipment, model, CLLI,
           sum(ds1wired), sum(ds3wired), sum(oc12wired), sum(oc3wired),
           sum(ds1equipped), sum(ds3equipped), sum(oc12equipped), sum(oc3equipped),
           sum(ds1inuse), sum(ds3inuse), sum(oc12inuse), sum(oc3inuse)
  FROM #temp1
GROUP BY no, equipement, model, CLLI

This will be much faster

Best of luck.
Bill
0
 
Jan FranekCommented:
Well, it quite depends, what kind of statements are in <i have some statements for every value of colA here>. May be it can be done in one or several SQL statements, may be not. Can you specify these statements or explain required function of the code ?
0
 
SadagopankAuthor Commented:
thanks jan for the response,

the statements inside the begin .. end of the fetch are something like this

fetch temp_cur into @no

if @@sqlstatus = 0
begin
    select col1, col2, col3 into #temptable1 from tableA, tableb where tableA.id= tableB.id and tableA.no = @no

    update #temptable5 set col1=(select colA from #temptable1 where bandwidth='ds1') where no=@no
   update #temptable5 set col2=(select colB from #temptable1 where bandwidth='ds2') where no=@no
       update #temptable5 set col3=(select colC from #temptable1 where bandwidth='ds3') where no=@no
end


thanks
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jan FranekCommented:
Well, it's a little confusing

In the original problem you wrote, that you declare your cursor this way:

declare cursor temp_cur for
select colA, colB, colC from tableA where colA = 'something'

And now you do fetch temp_cur into @no ? And where did bandwith column in table #temptable1 come from ? From your code it seems like #temptable1 has columns col1, col2 and col3.

I think that you probably don't need cursor at all, but it's really hard to guess what you are trying to do from pieces of code you posted. Can you post complete code that works for you (but is slow) ?
0
 
SadagopankAuthor Commented:
jan, here is the entire procedure.  I might be doing something wrong in there using so many update statements.  feed back is greatly appreciated.  


CREATE PROC someproc(@type varchar(20))
as

declare equip_crs cursor for
select ee.equip_name EQUIPMENT,
ee.equip_no No,
model MODEL,
equip_clli CLLI
from equip ee,
equip_type eet
where ee.equip_type = eet.equip_type
and equip_class = @type
and ee.equip_status_code != 'di'
order by equip_name
for read only

go

create table #temptable2
(no int, Equipment varchar(30), Model varchar(30), CLLI varchar(30), ds1wired int null, ds3wired int null, oc12wired int null, oc3wired int null,ds1equipped int null, ds3equipped int null, oc12equipped int null, oc3equipped int null,
ds1inuse int null, ds3inuse int null, oc12inuse int null, oc3inuse int null,
ds1fill numeric(4,0) null, ds3fill numeric(4,0) null, oc12fill numeric(4,0), oc3fill numeric(4,0))

declare @equipment varchar(20),
        @no int,
        @model varchar(20),
        @clli varchar(20),
        @proc_err int


open equip_crs
fetch equip_crs into @equipment,  
        @no,
        @model,
        @clli

while @@sqlstatus = 0
begin

   
      select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable1 from card ec,
        equip ee,port_templ_group eptg,port_template ept,slot ees
        where ec.equip_no=ee.equip_no
        and eptg.id=ec.port_templ_group_id
        and ept.id=eptg.port_templ_id
        and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
        and ec.card_status_code!='di'
        and ec.card_no=ees.card_id
        and ees.work_or_prot='w'
        and ept.logical_flag='n'
        group by ee.top_level_equip,ept.bandwidth,num_items
       
         insert into #temptable2 (no,Equipment, Model, CLLI) values(@no,@equipment,@model,@clli)

        update #temptable2 set ds1wired=(select sum(cnt) from #temptable1 where bandwidth='ds1'),
        ds3wired=(select sum(cnt) from #temptable1 where bandwidth='ds3'),
        oc12wired=(select sum(cnt) from #temptable1 where bandwidth='oc12'),
        oc3wired=(select sum(cnt) from #temptable1 where bandwidth='oc3')
        where no=@no
     
        drop table #temptable1
       
          select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable3 from card ec,
        equip ee,port_templ_group eptg,port_template ept,slot ees
        where ec.equip_no=ee.equip_no
        and eptg.id=ec.port_templ_group_id
        and ept.id=eptg.port_templ_id
        and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
        and ec.card_status_code in ('is','pe')
        and ec.card_no=ees.card_id
        and ees.work_or_prot='w'
        and ept.logical_flag='n'
        group by ee.top_level_equip,ept.bandwidth,num_items

        update #temptable2 set ds1equipped=(select sum(cnt) from #temptable3 where bandwidth='ds1'),
        ds3equipped=(select sum(cnt) from #temptable3 where bandwidth='ds3'),
        oc12equipped=(select sum(cnt) from #temptable3 where bandwidth='oc12'),
        oc3equipped=(select sum(cnt) from #temptable3 where bandwidth='oc3')
        where no=@no
       
        drop table #temptable3
       
        select ee.top_level_equip,ept.bandwidth,num_items,(count(*)*num_items) Cnt into #temptable4 from card ec,
        equip ee,port_templ_group eptg,port_template ept,slot ees
        where ec.equip_no=ee.equip_no
        and eptg.id=ec.port_templ_group_id
        and ept.id=eptg.port_templ_id
        and ee.top_level_equip = @no
            and ec.port_templ_group_id!=null
        and ec.card_status_code in ('is','pe')
        and ec.card_no=ees.card_id
        and ees.work_or_prot='w'
        and ept.logical_flag='n'
        and ec.card_no in
            (select card_no
                  from eon_port
                  where current_path_no is not null )
        group by ee.top_level_equip,ept.bandwidth,num_items

        update #temptable2 set ds1inuse=(select sum(cnt) from #temptable4 where bandwidth='ds1'),
        ds3inuse=(select sum(cnt) from #temptable4 where bandwidth='ds3'),
        oc12inuse=(select sum(cnt) from #temptable4 where bandwidth='oc12'),
        oc3inuse=(select sum(cnt) from #temptable4 where bandwidth='oc3')
        where no=@no
       
        drop table #temptable4
       
       
    fetch equip_crs into @equipment, @no, @model, @clli
   
end

select * from #temptable2

close equip_crs
deallocate cursor equip_crs

drop table #temptable2

return

go
0
 
SadagopankAuthor Commented:
thanks jan and grant !

grant you put  me in the right track, yeah i did it with your temp tables advise.  much faster :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.