Simple select * from table taking 13 mins to execute

Question

I have a table around 100 columns. This table contains some 4 million rows. I have created a clustered index on primary key

CREATE TABLE [dbo].[Table1](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [int] NOT NULL,
[Col3] [int] NOT NULL,
[Col4] [int] NOT NULL,
[Col5] [int] NULL,
[Col6] [int] NULL,
[Col7] [varchar](1) NULL,
[Col8] [varchar](1) NULL,
[Col9] [int] NULL,
[Col10] [varchar](100) NULL,
[Col11] [int] NULL,
[Col12] [varchar](100) NULL,
[Col13] [varchar](20) NULL,
[Col14] [int] NULL,
[Col15] [int] NULL,
[Col16] [varchar](80) NULL,
[Col17] [int] NULL,
[Col18] [varchar](50) NULL,
[Col19] [int] NULL,
[Col20] [varchar](50) NULL,
[Col21] [float] NULL,
[Col22] [float] NULL,
[Col23] [float] NULL,
[Col24] [float] NULL,
[Col25] [float] NULL,
[Col26] [float] NULL,
[Col27] [float] NULL,
[Col28] [float] NULL,
[Col29] [float] NULL,
[Col30] [float] NULL,
[Col31] [float] NULL,
[Col32] [float] NULL,
[Col33] [int] NULL,
[Col34] [varchar](50) NULL,
[Col35] [int] NULL,
[Col36] [datetime] NULL,
[Col37] [int] NULL,
[Col38] [datetime] NULL,
[Col39] [int] NULL,
[Col40] [datetime] NULL,
[Col41] [datetime] NULL,
[Col42] [int] NULL,
[Col43] [datetime] NULL,
[Col44] [datetime] NULL,
[Col45] [varchar](1) NULL,
[Col46] [int] NULL,
[Col47] [varchar](50) NULL,
[Col48] [int] NULL,
[Col49] [varchar](100) NULL,
[Col50] [int] NULL,
[Col51] [varchar](100) NULL,
[Col52] [int] NULL,
[Col53] [varchar](120) NULL,
[Col54] [varchar](255) NULL,
[Col55] [varchar](255) NULL,
[Col56] [datetime] NULL,
[Col57] [varchar](20) NULL,
[Col58] [varchar](1) NULL,
[Col59] [varchar](255) NULL,
[Col60] [varchar](255) NULL,
[Col61] [varchar](80) NULL,
[Col62] [varchar](50) NULL,
[Col63] [varchar](1) NULL,
[Col64] [varchar](50) NULL,
[Col65] [varchar](1) NULL,
[Col66] [varchar](4) NULL,
[Col67] [varchar](60) NULL,
[Col68] [varchar](8000) NULL,
[Col69] [int] NULL,
[Col70] [varchar](80) NULL,
[Col71] [varchar](1) NULL,
[Col72] [datetime] NULL,
[Col73] [int] NULL,
[Col74] [varchar](50) NULL,
[Col75] [varchar](255) NULL,
[Col76] [varchar](255) NULL,
[Col77] [varchar](255) NULL,
[Col78] [varchar](255) NULL,
[Col79] [varchar](255) NULL,
[Col80] [varchar](1) NULL,
[Col81] [varchar](1) NULL,
[Col82] [varchar](50) NULL,
[Col83] [varchar](200) NULL,
[Col84] [int] NULL,
[Col85] [varchar](120) NULL,
[Col86] [int] NULL,
[Col87] [varchar](120) NULL,
[Col88] [varchar](255) NULL,
[Col89] [varchar](1) NULL,
[Col90] [varchar](80) NULL,
[Col91] [varchar](255) NULL,
[Col92] [varchar](20) NULL,
[Col93] [varchar](255) NULL,
[Col94] [varchar](8000) NULL,
[Col95] [varchar](255) NULL,
[Col96] [varchar](255) NULL,
[Col97] [varchar](255) NULL,
[Col98] [char](1) NULL,
[Col99] [varchar](255) NULL,
[Col100] [datetime] NULL,
[Col101] [int] NULL,
[Col102] [float] NULL,
PRIMARY KEY CLUSTERED 
(
    [Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

When i select * from this table, it takes more than 12 mins to execute. I have used NoLock as well.

What am i missing in this? What non clustered index should i create to make it work faster?

Thanks in advance.

Can you post the code you are using to retrieve data from this table? — Raj More, Feb 27 '17 at 16:03
This doesn't have anything to do with an index. You're pulling back 4 million records 100 columns wide - it takes time to download all of that data from the server. It's not an index issue, it's a data transfer issue. And short of getting a better network pipe, there's not much you can do to fix that. — Siyual, Feb 27 '17 at 16:03
Do you really need the data to be INT, or FLOAT? maybe you could use a data type that occupies a smaller size like tinyint or smallint, and instead of float use DECIMAL with the actual size you need. If the table is smaller, it should be easier to read. On the other hand, I would really question you again on this: do you really need ALL the data from the table at once? Sending 4 million rows with 100 columns to an application server or to a client machine is a lot of RAM taken in that server too. Is going to make the application slower too. — asemprini87, Feb 27 '17 at 16:23
Slightly off-topic, there are some very good reasons to avoid NoLock. Take a look at this [artical](https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/). TL;DR: Among other issues NoLock can return duplicates. — David Rushton, Feb 27 '17 at 16:23
For the future, also never use float if you plan to do any math calculations, you will get rounding errors. — HLGEM, Feb 27 '17 at 16:35
A bit of self-promotion: try http://stackoverflow.com/questions/6386587/how-to-execute-sql-query-without-displaying-results/29187087#29187087 and see how much of those 12 minutes are lost client-side. Getting them from disk or cache "inside" the server WILL take some time, but likely most is lost on your client machine while it's being swamped with the resultset. — deroby, Feb 28 '17 at 18:10

score 1 · Answer 1 · answered Feb 27 '17 at 16:06

1

Basically there is a golden rule to be followed while doing select on a table that has huge number of rows

1) Query only the rows that you need

2) Query only the columns that you need

But applying select * on this huge table will obviously consume time.

answered Feb 27 '17 at 16:06

Arockia Nirmal

737
1
6
20

Yes this! SELECT * is a poor technique in any event and should not be used in production code. Querying millions of records and returning them in production code is a strange thing to do unless you are creating an output file. No one is ever going to look at 4 million records through a user interface. – HLGEM Feb 27 '17 at 16:33

Thanga · Answer 2 · 2017-02-28T11:16:28.810

0

Index will be of use if you put primary key in where condition. Otherwise it will fetch every record at once and there are infrastructure fixes to be done

edited Feb 28 '17 at 11:16

answered Feb 27 '17 at 16:01

Thanga

7,811
3
19
38

There is no where condition. What infrastructure fixes do you suggest? Paging? – Json Brent Feb 27 '17 at 16:05
1

@PrateekBhardwaj There is nothing you can do to fix it if you don't have a `WHERE` clause. The question you should be asking is: do you *really* need *everything* from the table? – Siyual Feb 27 '17 at 16:09

Simple select * from table taking 13 mins to execute

2 Answers2