Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Using Temporary Table instead of querying multiple times

    Hello,

    I am writing a procedure, and i am using same query 4 times to update/ insert in different tables. Is it better to dumb data in temp table and then query temp table or querying again and again to get same result set
    e,g

    1. merge into table1 using ( select x,y,z from tab2 left join tab3 ....)
    2. merge into table2 using ( select x,y,z from tab2 left join tab3 ....)
    3. Insert into table3 (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)

    Or
    1 Insert into temptab (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)
    2 merge into table1 using ( select x,y,z from temptab)
    3 merge into table2 using ( select x,y,z from temptab)
    4 Insert into table3 (x,y, p) ( select x,y,z*3 from temptab)

    >>> tab2 & tab3 are big tables 12000000 rows and growing
    >>> temptab may be 1000 at most

    Is there a better way

    Thanks in advance

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by phil72
    Hello,

    I am writing a procedure, and i am using same query 4 times to update/ insert in different tables. Is it better to dumb data in temp table and then query temp table or querying again and again to get same result set
    e,g

    1. merge into table1 using ( select x,y,z from tab2 left join tab3 ....)
    2. merge into table2 using ( select x,y,z from tab2 left join tab3 ....)
    3. Insert into table3 (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)

    Or
    1 Insert into temptab (x,y, p) ( select x,y,z*3 from tab2 left join tab3 ....)
    2 merge into table1 using ( select x,y,z from temptab)
    3 merge into table2 using ( select x,y,z from temptab)
    4 Insert into table3 (x,y, p) ( select x,y,z*3 from temptab)

    >>> tab2 & tab3 are big tables 12000000 rows and growing
    >>> temptab may be 1000 at most

    Is there a better way

    Thanks in advance

    You can use 'global temp table' for the purpose (i.e the 2nd method)

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    As long as you have the proper indexes to optimize the queries in the first scenario, then I would go with that. There will be one less DB access (3 steps instead of 4).

    If you do not have the indexes, then scenario 2 would be better because the major cost would be the big query on tab2, tab3, ..., and you would only do it once in the second scenario whereas you would do it twice in the first.

    Andy

  4. #4
    Join Date
    May 2003
    Posts
    113
    3nd option,

    1) use 'cross load' utility to unload the query result set
    2) use 'load' utility to load the result set back to table 1
    3) use 'load' utility to load the result set back to table 2
    4) use 'load' utility to load the result set back to table 3
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •