Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Unanswered: Temp Table Issues *URGENT*

    Hi All!

    I've a big table table1 with more than 100 million records. The data is processed through a stored procedure and results returned to the user. The stored procedure is very slow to execute and because of that we are tuning the same.

    We got the data extracted from one big query of the stored procedure to a temp table and we are operating on the temp table.
    The performance gain what we got was 50%.

    But unforunately this works very well if the original table table1 is not fragmented. When i ran the same tests in another database where the table table1 is highly fragmented the results are worse than the original procedure.
    It takes a very long time to create the temp table!
    I have 2 dbspaces for tempdbs and the original table table1 is fragmented using round robin manner.
    OPTCOMPIND=0
    OPTGOAL=-1

    please help me as am doing fire fighting!
    Thanks a lot
    sateesh

  2. #2
    Join Date
    Apr 2004
    Posts
    3

    Re: Temp Table Issues *URGENT*

    What's your syntax for creating the temp table? Are you using WITH NO LOG?

    How many fragments does table1 in the fragmented db? If it has more than the 2 temp spaces you have, that could also slow you down.

    Hope some of this helps.


    Originally posted by gpssateesh
    Hi All!

    I've a big table table1 with more than 100 million records. The data is processed through a stored procedure and results returned to the user. The stored procedure is very slow to execute and because of that we are tuning the same.

    We got the data extracted from one big query of the stored procedure to a temp table and we are operating on the temp table.
    The performance gain what we got was 50%.

    But unforunately this works very well if the original table table1 is not fragmented. When i ran the same tests in another database where the table table1 is highly fragmented the results are worse than the original procedure.
    It takes a very long time to create the temp table!
    I have 2 dbspaces for tempdbs and the original table table1 is fragmented using round robin manner.
    OPTCOMPIND=0
    OPTGOAL=-1

    please help me as am doing fire fighting!
    Thanks a lot
    sateesh

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hi,

    Hav eyou tried using PDQ. Try setting PDQ with SET PDQPRIORITY 100. This will enable paralellism inside IDS. With test we've done we see that the number of fragments in the tabel is also the factor the process runs faster.

    Hope this helps,

    Rob Prop

Posting Permissions

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