Solved

iterate through temporary table ..

Posted on 2004-11-01
2,360 Views
Last Modified: 2012-06-22
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,

0
Question by:Sadagopank
    6 Comments
     
    LVL 14

    Expert Comment

    by:Jan_Franek
    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
     

    Author Comment

    by:Sadagopank
    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
     
    LVL 14

    Expert Comment

    by:Jan_Franek
    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
     

    Author Comment

    by:Sadagopank
    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
     
    LVL 19

    Accepted Solution

    by:
    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
     

    Author Comment

    by:Sadagopank
    thanks jan and grant !

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

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Phishing is at the top of most security top 10 efforts you should be pursuing in 2016 and beyond. If you don't have phishing incorporated into your Security Awareness Program yet, now is the time. Phishers, and the scams they use, are only going to …
    Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now