Get faster row Count with Where Clause in Sql Server 2008 R2

Asked Dec 26 '13 at 17:03

Active Dec 26 '13 at 17:23

Viewed 430 times

I have a SQL query to get the count of matching input. When I run the query, it takes 28 seconds to get the count. Is there any faster way to get the matching count than simply using COUNT(Key).

Query

select 
    count(id) 
from 
    bibcontents
where 
    tagno = '245' 
    and normvalue like 'china %'
    or normvalue like '% china%'

Any help to this will be highly appreciated.

Thanks

edited Dec 26 '13 at 17:23

marc_s

732,580
175
1,330
1,459

asked Dec 26 '13 at 17:03

DonMax

1

Why not a single LIKE '% china %' – Mihai Dec 26 '13 at 17:07
1

I don't see how COUNT() could be the problem, how big is your table? – Vland Dec 26 '13 at 17:07
Right now, The table having 35 lakhs of records. This query count is for autocomplete with pagination. – DonMax Dec 26 '13 at 17:10
@Mihai: `'% china %'` wont work when china is the starting word of a string. – DonMax Dec 26 '13 at 17:15
The Likes with or are quite expensive.Maybe try http://msdn.microsoft.com/en-us/library/ms142571.aspx – Mihai Dec 26 '13 at 17:42
http://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries – Mihai Dec 26 '13 at 17:53

Get faster row Count with Where Clause in Sql Server 2008 R2

0 Answers0