how much time it will take to get the records from a large table

  • Hi,

    I got a table with 30000000 records

    how much time it will take to retrive that records if i use

    Select * from tablename

  • It depends.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • neliii (5/13/2010)


    Hi,

    I got a table with 30000000 records

    how much time it will take to retrive that records if i use

    Select * from tablename

    Why would you want to do that though? Retrieving 30 million rows doesn't make much sense for most applications. What do you expect to do with the results?

  • @david-2

    When i'm trying to perform a join operation on that table with another table with 400 records based on some condition its taking more that 20 mins and its still executing...

  • That's different to your original question. Take a look at the execution plan. See if your query is taking advantage of indexes on your tables and consider creating an index if not. Avoid using SELECT * in queries unless you absolutely need all the columns. Even if you do need every column, in any persistent code it's better to list the columns individually rather than use *.

  • neliii (5/13/2010)


    @david

    When i'm trying to perform a join operation on that table with another table with 400 records based on some condition its taking more that 20 mins and its still executing...

    Heh... you haven't told us what the table structure is, what the indexes are, what the execution plan is, or what the query is. Please see the article at the second link in my signature below for how to do that properly and for how to get the best help for a performance problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Also you probably want to "Page" that resultset 🙂


    * Noel

  • Nowadays 30,000,000 is only somewhat big.

    If you join a table that large, you'll want to return only the columns you need, and insure that lat least there is an index covering the joined columns. If there is an index which covers the key columns and also includes those columns returned in the result, and you're returning only a few thousand rows, then it could potentially take only a few seconds.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • neliii (5/13/2010)


    Select * from tablename

    question is... why would somebody do "select *" on a 30 million rows table?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • David Portas (5/13/2010)


    That's different to your original question. Take a look at the execution plan. See if your query is taking advantage of indexes on your tables and consider creating an index if not. Avoid using SELECT * in queries unless you absolutely need all the columns. [font="Arial Black"]Even if you do need every column, in any persistent code it's better to list the columns individually rather than use *.[/font]

    If you're going to say such a thing, you should provide the URL to the Article that says why so it turns into a learning event. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David Portas (5/13/2010)


    That's different to your original question. Take a look at the execution plan. See if your query is taking advantage of indexes on your tables and consider creating an index if not. Avoid using SELECT * in queries unless you absolutely need all the columns. Even if you do need every column, in any persistent code it's better to list the columns individually rather than use *.

    Using "SELECT *" is like walking into a Subway sandwich shop and saying "Give me a foot long with everything on it", because the options are always the same, and it's all good anyhow. Then one day they update their menu and you get a mouthfull of jalapeno pepper.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Given that the question is a beginner's question, I think its safe to assume that reading execution plans isn't something Neliii is prepared to do yet.

    Nelliii, please read Jeff's article about how to post questions to get good answers. The "it depends" answer is honest, not sarcastic. The more information you provide up front, the quicker the people trying to help you can get to the heart of the matter.

    How much time it takes to read 30 million rows depends on at least the following variables, and probably more besides.

    1. How many columns are in each row.

    2. The datatypes of each column.

    3. If any columns are of variable length, the data in those columns.

    4. The hardware

    5. The workload from other jobs running on that hardware

    6. The query that's reading the rows

    7. Where is the output going? Is it summarized?

    Pulling 400 rows out of 30 million rows adds even more complexity.

    1. How is the table indexed?

    2. Is the table partitioned or not

    3. Again, the query

    With a proper design (table schema, indexing, and query), I would expect to be able to pull 400 rows from 30 million in less than a second. Obviously this isn't your situation, so help us out and post your actual query and the table schema for all tables involved. Thanks.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply