none
Information about creating Index in a table

    Question

  • I have a table : [Carbon].[SampleResult] .Information about the table is mentioned above. There is a clustered index and a non clustered index present for this table. However, This table takes 14 seconds to load the 129484 rows  . I would like to reduce it to 0 seconds. Whats the best solution to achieve this. Please help.

    Thanks in advance

    Saturday, May 10, 2014 6:39 AM

Answers

  • Thanks Kalman. I have executed the query directly on the server and it took me 2 seconds. However, Is there any way where I can make it to 0 seconds. I mean adding few more non clustered indexes on the table.

    No. An index helps you to locate the data you are searching for faster. It's like an index of a book. Someone gives you a thick book about the history of the US. All you want to know is how West Virginia became a separate state. So you look up West Virginia in the index in the book.

    But if you need to real the whole book what use do you have for the index? The index is not going to help you read the book faster.

    Going back to the database, what may help a little bit is to rebuild the index on the table, because it is may be fragmented, either by pages not coming in the right order on the disk, or pages being only half-full.

    However, since you run this query from SSMS, I can tell you were the major bottleneck is: rendering the grid in SSMS. But try this from a command-line window.

    BCP yourdb.Carbon.SampleResult out data.bcp -T -S Server -n


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 10, 2014 12:15 PM

All replies

  • What is the query?

    The 14 seconds may be due to the client sluggishness to accept the result set from the server.

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



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Saturday, May 10, 2014 6:49 AM
    Moderator
  • THanks Kalman. Query is given below. I am doing just a select * from the table.

    select * from [Carbon].[SampleResult]  --This query takes 14 seconds to load. I want to reduce it to 0 seconds for the same query.

    regards

    -pep

    Saturday, May 10, 2014 6:52 AM
  • Oops! Google Chrome could not connect to www.sqlusa.com

    Try reloading: www.­sqlusa.­com/­articles/­query-­optimization/­

    Saturday, May 10, 2014 6:55 AM
  • When I execute:

    SELECT * FROM Sales.SalesOrderDetail;

    It takes 1 second because the SSMS client on the same computer as the server is fast.

    Can you try the same query on a fast client?

    What I am saying, the server is fast, the client or the client communications is slow.



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Saturday, May 10, 2014 6:57 AM
    Moderator
  • Thanks Kalman. I have executed the query directly on the server and it took me 2 seconds. However, Is there any way where I can make it to 0 seconds. I mean adding few more non clustered indexes on the table.
    Saturday, May 10, 2014 7:23 AM
  • Nope. If you do select * without any where clause it would not use nonclustered index as you're effecitively selecting all rows from the table. So optimizer would determine its easier to retrieve data by traversing the table data itself which is what you see by clustered scan as clustered index is built on the table data.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, May 10, 2014 7:40 AM
  • Thanks Kalman. I have executed the query directly on the server and it took me 2 seconds. However, Is there any way where I can make it to 0 seconds. I mean adding few more non clustered indexes on the table.
    To send 130K rows of data just takes some time. Index will not help. It is about communications between server and client.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Saturday, May 10, 2014 7:50 AM
    Moderator
  • Thanks Kalman. I have executed the query directly on the server and it took me 2 seconds. However, Is there any way where I can make it to 0 seconds. I mean adding few more non clustered indexes on the table.

    No. An index helps you to locate the data you are searching for faster. It's like an index of a book. Someone gives you a thick book about the history of the US. All you want to know is how West Virginia became a separate state. So you look up West Virginia in the index in the book.

    But if you need to real the whole book what use do you have for the index? The index is not going to help you read the book faster.

    Going back to the database, what may help a little bit is to rebuild the index on the table, because it is may be fragmented, either by pages not coming in the right order on the disk, or pages being only half-full.

    However, since you run this query from SSMS, I can tell you were the major bottleneck is: rendering the grid in SSMS. But try this from a command-line window.

    BCP yourdb.Carbon.SampleResult out data.bcp -T -S Server -n


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 10, 2014 12:15 PM
  • Thanks Erland. You have explained it in a nice way! 
    Saturday, May 10, 2014 2:52 PM