Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    49

    Unanswered: High CPU Performance Issue with BIGINT column comparison SQL code

    I recently converted a column that was once an int to an bigint on one of my tables. The modified column provided a generic row id information and there are duplicates within this column. I am trying to perform a self join via the following:

    SELECT a.row_id FROM test_db a INNER JOIN test_db b ON b.row_id < a.row_id.

    This code use to work when the column was an int but now I am getting high CPU issues since I converted to bigint. I am unsure on why the change to bigint will cause such an issue. The OS/SQL is 64BIT.

    Thanks for the help in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This will result in massive CPU use. Do you know what that query does? What do you hope to achieve with the query?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i think this will return multiple copies of every id except for the first one and if you are using a bigint you must have a massive table of billions of rows. why else use a bigint? not to mention what this must be doing to the memory. I mean loading that many rows of such a large data type would certainly peak out some of my machines. I can really think of no practical application for something like this.
    “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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is actually a "half-cartesian" join. Each row is returned in the result set paired with every row with a smaller id. For a cartesian join you get n * n rows, for the "half-cartesian" you get n (n - 1) which is practically as bad.

    Can you describe what you really want to do in English, because I'm pretty sure that what you are doing now is NOT what you wanted!

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's a theta join, and i believe it's n&#178;/2, not n(n-1)

    but pat is right, that query looks whack, why would you want the same row_id returned umpteen times?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    perhaps OP is trying to create a DOS attack on the server?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let me guess

    You're a consultant getting paid by the hour and no one around you knows sql
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you got me.

    although this place is the opposite of most of my experience. nothing would get done here without us.
    “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.

Posting Permissions

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