Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Cut down Updation time 4 Updating 3 Lacs row's 10 columns with diff.-2 functions

    Dear All,

    We have 3,00,000 rows in a table. This table has a Primary Key named In_Id.

    To update this table 10 columns, we have separate-separate 10 scalar functions with different-different definitions. In these functions we have to pass Primary Key of this table & get the output. It works fine when we work with small-small bunch of rows. But, when we try to update these 10 columns for all 3,00,000 rows then it takes 45-60 minutes to update the table.

    The frequency of updating these 3,00,000 rows is depends on user requirement, means as & when user generates the report then before showing the results this updation process needs to execute.

    How do we cut down this time?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you're passing the primary key value to functions which then run queries based on that primary key in order to produce their output, then that is extremely inefficient.

    You're going to need to eliminate those functions and incorporate their logic into the query statement, using set-based processing.

    The exact method of doing this will depend on the logic that you are converting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Dump your functions. They are executing 300,000 times. Adopt set based processing. It is what Relational Database Management Systems are built to handle. 300,0000 rows is notthing. 3 million rows is nothing these days.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    First of all, are we talking about 3,000,000 records, or 300,000 records, because your repeated reference to 3,00,000 baffles me.

    Let's take a worst case and say that we are talking about 3,000,000 records.

    Let me get this straight . . . I assume that the reason you are passing the primary key to each of these 10 functions is because you are using the primary key to look up, in that same table, additional information.

    So, for each of the 3,000,000 records, you are doing 10 additional scans against the same table ? ? ?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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