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: 63509 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: 99133 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: 246985 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%'

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pietlinden SSC Guru Points: 63509 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: 101122 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90304 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: 1004406 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: 1004406 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: 1004406 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?