CODEWITHSUNDEEP
sqlsql-servert-sqloptimization
Mehdi
Mehdi

Reputation: 2398

Count number of a value of a field in big table

I need to count the number of lines which have a specific value in one of its fields.

Lets say :

SELECT COUNT(1) FROM MyTable WHERE MyField = 'MyValue'

Problem is that there more than 1.000.000 rows and the execution time is too long (more than 60s)

What can I do in this specefic field to make this Request run faster?

Upvotes: 2

Views: 258

Answers (4)

Martin Smith
Martin Smith

Reputation: 452988

You say the NCI speeds things up to less than a second and that the table has 1,000,000 rows with 6 distinct values so that still means on average 167,000 index rows need to be counted to answer the query.

Additionally a non covering index on such a non selective column is unlikely to be useful to satisfy additional queries.

An indexed view with pre-calculated counts would allow the SELECT to be more or less instant and have less space requirement and maintenance overhead (no need to store 167,000 rows with MyValue1 followed by another 167,000 for MyValue2 and so on)

CREATE VIEW dbo.MyView
WITH SCHEMABINDING 
AS
SELECT COUNT_BIG(*) AS Count,
       MyField
FROM  dbo.MyTable    
GROUP BY MyField

GO

CREATE UNIQUE CLUSTERED INDEX IX ON dbo.MyView(MyField)

And then 

SELECT Count
FROM   MyView WITH ( NOEXPAND)
WHERE  MyField = 'MyValue'

Upvotes: 3

Ahmed
Ahmed

Reputation: 450

You can try this :

SELECT COUNT(*) FROM YOUR TABLE_NAME WHERE COLUMN_NAME LIKE 'MYFIELD%'

Upvotes: -3

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

If you search only exact value of column ('MyValue'), you should create an index on the MyField column.

CREATE NONCLUSTERED INDEX IX_MyTable_MyField ON MyTable (MyField ASC)

If you want to search word in phrase from column - create Full Text Search index (as in answer of Mari).

Upvotes: 5

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Creating Full Text Search

For text based columns, full text search is always required to be performed under several times. In such situations full text index is used.

Syntax:

CREATE FULLTEXT INDEX ON table_name (column_name1 […], column_name2 […]) …

This will increase your performance

Upvotes: 0

Related Questions