SELECT COUNT(*) Taking a Very Long Time

Question

Post reply

SELECT COUNT(*) Taking a Very Long Time

homebrew01

SSC Guru

Points: 55549

April 6, 2019 at 11:07 pm

#2946476

Why would SELECT COUNT(*) take 24 minutes to run ?

Running on a quiet DEV server.

No Blocking.

Table has 280,000 rows.


select count(*)
from MyTable
where MyTable_text IS NULL

Table Structure


CREATE TABLE [dbo].[MyTable](
[MyTable_ID] [int] NOT NULL,
[MyTable_Content] [text] NULL,
[MyTable_Html] [text] NULL,
[MyTable_Text] [text] NULL,
[MyTable_filepath] [varchar](5000) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[MyTable_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--
--
CREATE FULLTEXT INDEX ON [dbo].[MyTable](
[MyTable_Content] LANGUAGE 'English')
KEY INDEX [PK_MyTable]ON ([MyTable_Search], FILEGROUP [ftfg_MyTable_Search])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

This topic was modified 5 years, 10 months ago by homebrew01.
This topic was modified 5 years, 10 months ago by homebrew01.
This topic was modified 5 years, 10 months ago by homebrew01.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 1

Because you've asked for all of the data including 3 Text columns (which have also been deprecated for years now) and file paths that can also be quite large. My question is, why on Earth are you returning 280,000 rows of anything to the screen???

--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)

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

Nevermind

This reply was modified 5 years, 10 months ago by Thom A.

Thom~

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

homebrew01 SSC Guru Points: 55549 More actions · Answer 3

Bad habit of (*) perhaps. Thinking "It's just a count"

This reply was modified 5 years, 10 months ago by homebrew01.
This reply was modified 5 years, 10 months ago by homebrew01.

ScottPletcher SSC Guru Points: 101147 More actions · Answer 4

Add a bit column to indicate null status of [MyTable_Text], then index on that column.

ALTER TABLE [dbo].[MyTable] ADD is_MyTable_Text_NULL AS CAST(CASE WHEN MyTable_Text IS NULL THEN 1 ELSE 0 END AS bit) PERSISTED

CREATE NONCLUSTERED INDEX IX_MyTable_Text_NULL ON dbo.MyTable ( is_MyTable_Text_NULL ) WITH ( FILLFACTOR = 100 );

Ok, it's not a selective index, but that's not the point there. The point is to be able to do a very fast count of NULL / NOT NULL.

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".

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 5

homebrew01 wrote:

Bad habit of (*) perhaps. Thinking "It's just a count"

Crud... that's what I get for reading a post prior to a quart of coffee. When I first read your original post, all I saw was SELECT *.

Be really careful if you add a calculated column and index it as has been suggested. I managed to bring Expedia.com right to its knees (several years ago) for several minutes with such an index. The only way it was fixed was when I dropped the index. The table was really active for both INSERTs and UPDATEs and the page splits that occurred on the new index due to the large amount of data movement due to low cardinality was the culprit.

--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)

ScottPletcher SSC Guru Points: 101147 More actions · Answer 6

Hopefully SQL's split algorithm isn't that poor for bit columns.

But, if it turns out to be, create an index on:

( MyTable_ID, is_MyTable_Text_NULL ).

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".

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

True dat! I have seen many time where it will choose a more narrow index to scan (much more quickly) even when it's the second column being used for criteria. I believe a filtered index here would help a lot especially if the are comparatively few items that are NULL compared to those that are not.

In this case, replacing the * with the second column name in the index (the criteria being used) would also help SQL Server make the choice. If it doesn't make that choice and index hint (directive, actually) may be necessary.

--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)