Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2008
    Posts
    19

    Unanswered: Table contains trillion of data got stucked

    Hi,

    This question was in interview

    " A table has the trilions of data, and has proper indexes as well,
    when we query the table, it got stucks.

    then how to get the records from that table ? "


    Please reply as soon as possible

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    First thing is to take a look at the execution plan.

    If "trilions of data" means Terabytes or trillions of rows then you ought to have some partition scheme for the table as well as indexing.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Got to say, I think the question is dumb. Surely it should be more like "What are the steps to identify the problem, and what possible resolutions are there?" There could be loads of reasons the query got stucks, many nothing to do with the information provided.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Assuming the hardware is sufficient - the first thing I would try is run a statistics update

  5. #5
    Join Date
    Dec 2008
    Posts
    19
    It has 10 fields, all of them are strings.

    How the partition will help ?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sagitarius
    It has 10 fields, all of them are strings.

    How the partition will help ?
    It "may" help - we don't know the problem!

    Partitioning helps by reducing the number of layers in the index B-Tree(s). Partitions can also be distributed across drives, increasing the number of spindles in the event of a scan.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    This reminds me of an interview question from back in my HW\SW days:

    Interviewer: "Computer won't boot. What do you do?"
    Me: "That depends on what you mean by 'Won't boot'. Is it not powering on, or is there some kind of error message?"
    Interviewer: "Never mind..."

    I refused the offer.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  8. #8
    Join Date
    Dec 2008
    Posts
    19
    any other solution for it ?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Walk up to the server and give it a kick. If the problem persists go kick the interviewer until they give you more information to enable you to solve the problem.

    Alternatively WITH (NOLOCK) might be worth a look
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check that the expressions are sargable.
    Use OPTION(FAST n) or WITH (FASTFIRSTROW).
    Account for parameter sniffing, especially with regard to inappropriate lookups.
    Check for a where predicate.
    Run perfmon on the server, accounting for network, io, memory, cpu.
    Run profiler; ensure SET OPTIONS are as expected.
    Check DMVs and account deadlocks & exclusive table & range locks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Heck, if all you want is a few rows, just run this:
    Code:
    select top 10 *
    from hugetable
    Might not be the rows you want, but no conditions have been set on that, yet, have they?

  12. #12
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    It "may" help - we don't know the problem!

    Partitioning helps by reducing the number of layers in the index B-Tree(s). Partitions can also be distributed across drives, increasing the number of spindles in the event of a scan.
    The only benefit I ever got out of partitioning was related to ETL procedures and switching out and in partitions and rebuilding indexes at the partition level, with the correct indexing schemes I have not noticed a difference in performance.

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Jack Vamvas
    Assuming the hardware is sufficient - the first thing I would try is run a statistics update
    and re-org and rebuild your indexes regularly.

Posting Permissions

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