simple select * from table takes around 15 secons

Question

simple select * from table takes around 15 secons

csltech

SSC Enthusiast

Points: 191
More actions
May 17, 2018 at 11:18 am

#334823

I have a table with like 115+ columns and it got the data around 76000 records
I ran the query, Select * from table where lastname like '%S%'
ITs taking 15 seconds or more sometime.
There is clustered index on Lastname.
The auto statistics are on .
Any idea why ?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63435 More actions · Answer 1

if you look in a phone book for everybody with "S" as the second letter of their last name, an index is meaningless, because a phone book is organized alphabetically. The same is true of indexes. Check the execution plan to see what's actually going on, then you'll have a much better idea of why your query is slow.

Thom A SSC Guru Points: 99126 More actions · Answer 2

pietlinden - Thursday, May 17, 2018 11:22 AM
if you look in a phone book for everybody with "S" as the second letter of their last name, an index is meaningless, because a phone book is organized alphabetically. The same is true of indexes.

It's worse than that, if we're honest, it more look through the phone book where the person's surname as an S in it!

If this is a type of query you do often, you're likely going to need to consider full text indexing.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246716 More actions · Answer 3

To expand on the above responses, you should see the following amended version of your query return results very quickly, as it will use the CI:

Select * from table where lastname like 'S%'

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pietlinden SSC Guru Points: 63435 More actions · Answer 4

Thom A - Thursday, May 17, 2018 11:24 AM
pietlinden - Thursday, May 17, 2018 11:22 AM
if you look in a phone book for everybody with "S" as the second letter of their last name, an index is meaningless, because a phone book is organized alphabetically. The same is true of indexes.
It's worse than that, if we're honest, it more look through the phone book where the person's surname as an S in it!
If this is a type of query you do often, you're likely going to need to consider full text indexing.

Yes, true! It IS worse than I thought. Yipes.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 5

Still seems too long for only 76K rows.

1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 6

ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).

With 115 columns - all of them being returned - 15 seconds seems reasonable to me. I would bet that most of that time is network IO moving the data from the server to the client.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).

The only time fragmentation matters is if the data doesn't exist in memory and has to read from the disk. Once it's in memory, logical fragmentation just doesn't matter and you have to have some really low "natural fill factors" in play for physical fragmentation to matter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

Jeffrey Williams 3188 - Friday, May 18, 2018 11:48 AM
ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).
With 115 columns - all of them being returned - 15 seconds seems reasonable to me. I would bet that most of that time is network IO moving the data from the server to the client.

I agree. And I'll also ask who in the hell returns all 115 columns to the screen anyway? It's not practical for anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

csltech SSC Enthusiast Points: 191 More actions · Answer 9

ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).

csltech - Thursday, May 17, 2018 11:18 AM
I have a table with like 115+ columns and it got the data around 76000 records
I ran the query ,
[ Select * from table where lastname like 'S%' ]
Its taking 15 seconds or more sometime.
There is clustered index on Lastname.
The auto statistics are on .
The query was executed on the server, (using the RDC, directly on the server) [from my local machine it takes around 1 min)
Any idea why ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

csltech - Monday, May 21, 2018 11:55 AM
ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).
csltech - Thursday, May 17, 2018 11:18 AM
I have a table with like 115+ columns and it got the data around 76000 records
I ran the query ,
[ Select * from table where lastname like 'S%' ]
Its taking 15 seconds or more sometime.
There is clustered index on Lastname.
The auto statistics are on .
The query was executed on the server, (using the RDC, directly on the server) [from my local machine it takes around 1 min)
Any idea why ?

It is the time taken to display the data. From your local machine it also has to move over the network.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 11

Lynn Pettis - Monday, May 21, 2018 12:58 PM
csltech - Monday, May 21, 2018 11:55 AM
ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).
csltech - Thursday, May 17, 2018 11:18 AM
I have a table with like 115+ columns and it got the data around 76000 records
I ran the query ,
[ Select * from table where lastname like 'S%' ]
Its taking 15 seconds or more sometime.
There is clustered index on Lastname.
The auto statistics are on .
The query was executed on the server, (using the RDC, directly on the server) [from my local machine it takes around 1 min)
Any idea why ?
It is the time taken to display the data. From your local machine it also has to move over the network.

To wit, I have to ask... why would anyone need to display all 115 columns of data for 76,000 rows to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Jeff Moden - Tuesday, May 22, 2018 5:37 AM
Lynn Pettis - Monday, May 21, 2018 12:58 PM
csltech - Monday, May 21, 2018 11:55 AM
ScottPletcher - Friday, May 18, 2018 11:09 AM
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the table if you can (if your version / edition of SQL supports it).
csltech - Thursday, May 17, 2018 11:18 AM
I have a table with like 115+ columns and it got the data around 76000 records
I ran the query ,
[ Select * from table where lastname like 'S%' ]
Its taking 15 seconds or more sometime.
There is clustered index on Lastname.
The auto statistics are on .
The query was executed on the server, (using the RDC, directly on the server) [from my local machine it takes around 1 min)
Any idea why ?
It is the time taken to display the data. From your local machine it also has to move over the network.
To wit, I have to ask... why would anyone need to display all 115 columns of data for 76,000 rows to begin with?

The sarcastic answer - Because I can. 😀

And I agree, why are you returning all 115 columns of data for 76,000 rows?