locked
Suggestion to get the result fast RRS feed

  • Question

  • Hi Experts,

    I have a table with approx 10 Million records. I need to execute "Select * from tablename". The query takes eternity to complete.

    What have i done ?

    1. There is clustered index on Id column (which is a identity column)
    2. There is a Covering index created with all the columns include clause (another non clustered index)

    Still, when i execute "SELECT * FROM TableName" , it takes eternity, to return the result. Any pointers would be much appreciated

    Sunday, August 2, 2020 4:00 PM

Answers

  • The redundant non-clustered index with all columns and no where clause will not provide a performance benefit. A full scan will be required regardless if the table is organized as a heap, clustered index, or non-clustered index to cover the query.

    Assuming there is no blocking during the scan, performance will be limited by storage, network bandwidth, and client processing time. You'll need to identify which of these are limiting factor to determine where to focus your attention.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Laxmidhar sahoo Sunday, August 2, 2020 5:49 PM
    • Marked as answer by SQLLearnerr Sunday, August 2, 2020 6:19 PM
    Sunday, August 2, 2020 4:45 PM

All replies

  • The redundant non-clustered index with all columns and no where clause will not provide a performance benefit. A full scan will be required regardless if the table is organized as a heap, clustered index, or non-clustered index to cover the query.

    Assuming there is no blocking during the scan, performance will be limited by storage, network bandwidth, and client processing time. You'll need to identify which of these are limiting factor to determine where to focus your attention.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Laxmidhar sahoo Sunday, August 2, 2020 5:49 PM
    • Marked as answer by SQLLearnerr Sunday, August 2, 2020 6:19 PM
    Sunday, August 2, 2020 4:45 PM
  • Hi

    You also may looking your logical memory location for sql server .

     Buffer memory should be of a size that should enough to maintain active data of your database

    Thanks and regards

    Sunday, August 2, 2020 5:51 PM
  • This should help

    Destin -MCPD: SharePoint Developer 2010, MCTS:SharePoint 2007 Application Development

    Sunday, August 2, 2020 5:54 PM
  • OK It seems there isnn't much that can be done at this point of time. Thanks Dan,Laxmi & Destin.
    Sunday, August 2, 2020 6:19 PM
  • Do you really need to return all 10 mln rows? What is the client application to receive them?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, August 2, 2020 6:30 PM