May 13, 2010 at 10:57 am
Hi,
I got a table with 30000000 records
how much time it will take to retrive that records if i use
Select * from tablename
May 13, 2010 at 10:59 am
May 13, 2010 at 11:06 am
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?
May 13, 2010 at 11:22 am
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 *.
May 13, 2010 at 11:52 am
neliii (5/13/2010)
@davidWhen 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
May 13, 2010 at 12:35 pm
Also you probably want to "Page" that resultset 🙂
* Noel
May 13, 2010 at 12:46 pm
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
May 13, 2010 at 12:58 pm
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.May 13, 2010 at 2:16 pm
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
May 13, 2010 at 2:53 pm
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
May 13, 2010 at 9:33 pm
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