Select Count from a big table

Question

Post reply

Select Count from a big table

Sharon-328725

SSC-Addicted

Points: 424
More actions
January 12, 2012 at 3:28 am

#249507

How to do Select Count with critria from a big table (40,000,000 records)?
This is very very slow !!
SELECT
Count(TBL_TOC.ID_TOC)
FROM
TBL_TOC
WHERE
(CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))

Viewing 11 posts - 1 through 10 (of 10 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

The base query to find the number of records in a table is :-

select OBJECT_NAME(object_id) as TblName, rows as NumberofRecords from sys.partitions

where OBJECT_NAME(object_id) like 'TBL_TOC%'

Then you can further fine tune it by filtering all the other obejcts which are not owned by a specific schema.

----------
Ashish

Basit Farooq Ten Centuries Points: 1151 More actions · Answer 2

Here is the other flavor of the query to get quick row counts:

select OBJECT_NAME(object_id) as Table

,rows as RowCount from sys.partitions

where object_id = (select object_id from sys.objects

where name like '%your table%')

and type = 'U') --and index_id in (0, 1)

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Sharon-328725 SSC-Addicted Points: 424 More actions · Answer 3

Thanks, but I need to do a count with a criteria:

WHERE

(CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Do you have a fulltext index for TEXT? That would speed up things a lot.

-- Gianluca Sartori

Sharon-328725 SSC-Addicted Points: 424 More actions · Answer 5

Yes it has full text indes, but this query:

SELECT Count(TBL_TOC.ID_TOC)

FROM TBL_TOC

WHERE (CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))

takes 10 seconds to run...

It's a very big table with 40,000,000 records!!

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Then, I think there's not much you can do.

-- Gianluca Sartori

cherish.j.wang Right there with Babe Points: 758 More actions · Answer 7

the most high performance way is using system procedure named sp_spaceused:

exec sp_spaceused 'tablename'

thank you

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

Sharon-328725 (1/12/2012)
Yes it has full text indes, but this query:
SELECT Count(TBL_TOC.ID_TOC)
FROM TBL_TOC
WHERE (CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))
takes 10 seconds to run...
It's a very big table with 40,000,000 records!!

Ten seconds ain't so bad.

Can you filter on anything else or does it have to be the whole table?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 9

If you always need to filter against CONTAINS(BL_TBL_TOC.TEXT, '"test*"') it might be an option to add an indexed computed persisted column that would set a flag depending on the condition above. A count based on this column should be faster.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 10

Sharon-328725 (1/12/2012)
How to do Select Count with critria from a big table (40,000,000 records)?
This is very very slow !!
SELECT
Count(TBL_TOC.ID_TOC)
FROM
TBL_TOC
WHERE
(CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))

40 million rows is not that much for full-text search, even on SQL Server 2005. The problem is often a lack of memory set aside for FTS to work in (it is separate from SQL Server pre-2008). Follow the advice in the TechNet Full-Text Search Performance Tuning and Optimization guide:

http://technet.microsoft.com/en-us/library/ms142560(v=SQL.90).aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi