locked
Retrieve data from table which contains 600000000 records RRS feed

  • Question

  • from 60 crore table i would like to retrieve  a record but it is taking huge time to retrieve a record and  the table contains a clustered index .
    Saturday, December 8, 2012 8:20 AM

Answers

  • Columns do not contain indexes, but indexes contain columns. If column3 is not the leading column of the index, it's useless.

    Overall, if you want more useful help, you should your post CREATE TABLE and CREATE INDEX statements for your table, as well as the exact query.

    Of course, you may also be victim of blocking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Monday, December 17, 2012 6:54 AM
    Saturday, December 8, 2012 4:22 PM

All replies

  • Share your select statement you are executing for getting the single record.

    Also tell which column has the clustered index built on.


    Please mark it as an answer/helpful if you find it as useful. Thanks, Satya Prakash Jugran

    Saturday, December 8, 2012 8:31 AM
  • Is the clustered index the only index? If not, what are the other indexes? What does your WHERE clause look like? We cannot tell you which extra index might help if you do not give us more information. It sounds like your query plan contains a table scan.
    Saturday, December 8, 2012 8:59 AM
  • Hi,

    To retrieve large quantity of data you need to think below things :

    1. table partition

    2. Indexing

    3. Database file grouping

    4.Query optimization

    Is data source and destination is on same server or different ?


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Proposed as answer by MdAlmamun Tuesday, December 18, 2012 11:59 AM
    Saturday, December 8, 2012 9:04 AM
  • use index with SARGability 

    without  SARGability index not work. and without index no way to get speed up in data.

     refer below link for it (Sargable)

    thank you


    NILESH MAKAVANA

    Saturday, December 8, 2012 10:23 AM
  • Just post the DDL for the table, and your query that is taking too long, so we have enough information to sensibly even think about your question.

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    Saturday, December 8, 2012 10:44 AM
  •  I am just performing a simple select like select column1,column2 from table where column3='somedata' in this column3 contains clustered index and the table contains 60 crore data.
    Saturday, December 8, 2012 12:40 PM
  • I am just performing a simple select like select column1,column2 from table where column3='somedata' in this column3 contains clustered index and the table contains 60 crore data and can you give me few links where i can refere to create partition,database file groups.

    Saturday, December 8, 2012 12:45 PM
  • If it is actually that simple, why doesn't your SQL work?

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.


    Saturday, December 8, 2012 1:16 PM
  • Columns do not contain indexes, but indexes contain columns. If column3 is not the leading column of the index, it's useless.

    Overall, if you want more useful help, you should your post CREATE TABLE and CREATE INDEX statements for your table, as well as the exact query.

    Of course, you may also be victim of blocking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Monday, December 17, 2012 6:54 AM
    Saturday, December 8, 2012 4:22 PM
  • Can you use Western numbering? We don't know what is "crore"?

    >column1,column2 from table where column3='somedata'

    How many rows do you anticipate? Is there blocking?

    Do you rebuild indexes every weekend?

    Do you update statistics every night?

    As noted above, for quick assistance, you have to provide us with meaningful information.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Sunday, December 16, 2012 12:54 AM
    Sunday, December 16, 2012 12:53 AM
  • Can you use Western numbering? We don't know what is "crore"?

    I was about to make that remark earlier, but I looked it up my "professional" English-Swedish dictionary, and "crore" is actually listed. Yes, it's marked "ind.", but nevertheless.

    No, I had not seen "crore" before, but following these forums has learnt me what "lakh" means.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 16, 2012 11:29 AM