January 12, 2012 at 3:28 am
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*"'))
January 12, 2012 at 3:36 am
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
January 12, 2012 at 3:45 am
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.comJanuary 12, 2012 at 3:50 am
Thanks, but I need to do a count with a criteria:
WHERE
(CONTAINS(BL_TBL_TOC.TEXT, '"test*"'))
January 12, 2012 at 3:51 am
Do you have a fulltext index for TEXT? That would speed up things a lot.
-- Gianluca Sartori
January 12, 2012 at 3:56 am
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!!
January 12, 2012 at 4:10 am
Then, I think there's not much you can do.
-- Gianluca Sartori
January 14, 2012 at 7:18 am
the most high performance way is using system procedure named sp_spaceused:
exec sp_spaceused 'tablename'
thank you
January 14, 2012 at 9:40 am
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?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 14, 2012 at 10:15 am
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.
January 14, 2012 at 11:08 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply