CODEWITHSUNDEEP
sqlsql-serversql-server-2008
neojakey
neojakey

Reputation: 1663

Faster SQL Counting

I have a page that it taking far too long to load because it has to count the number of members in each category. http://www.storeboard.com/counties/default.asp?cou=1196

Upon further investigation I stumbled upon this page: http://blogs.msdn.com/b/martijnh/archive/2010/07/15/sql-server-how-to-quickly-retrieve-accurate-row-count-for-table.aspx

The question I have is how do I change this:

SELECT COUNT(MemberID) AS MembersInCountyCat
FROM Member
WHERE NYKACountyID = @NYKACountyID
AND (
    NYKACatID = @NYKACatID 
 OR NYKACatIDExtra1 = @NYKACatID 
 OR NYKACatIDExtra2 = @NYKACatID 
 OR NYKACatIDExtra3 = @NYKACatID 
 OR NYKACatIDExtra4 = @NYKACatID 
 OR NYKACatIDExtra5 = @NYKACatID
 OR NYKACatIDExtra6 = @NYKACatID 
 OR NYKACatIDExtra7 = @NYKACatID 
 OR NYKACatIDExtra8 = @NYKACatID 
 OR NYKACatIDExtra9 = @NYKACatID 
 OR NYKACatIDExtra10 = @NYKACatID
)
AND ProfileTypeID <> 1

Into the suggestion in solution 4 of the page I quoted.

Any help you can provide would be greatly appreciated.

Many Thanks, Paul

Upvotes: 1

Views: 195

Answers (3)

Denis Valeev
Denis Valeev

Reputation: 6015

Something like this maybe to take advantage of indexes (if you have any):

select MembersInCountyCat = count(*) 
from (
SELECT MemberID
FROM Member
WHERE NYKACountyID = @NYKACountyID
AND NYKACatID = @NYKACatID
AND ProfileTypeID <> 1 
union
SELECT MemberID
FROM Member
WHERE NYKACountyID = @NYKACountyID
AND NYKACatIDExtra1 = @NYKACatID 
AND ProfileTypeID <> 1
union
...
union 
SELECT MemberID
FROM Member
WHERE NYKACountyID = @NYKACountyID
AND NYKACatIDExtra10 = @NYKACatID 
AND ProfileTypeID <> 1
) t

union will make those MemberIds unique

Upvotes: -1

Vitaliy Kalinin
Vitaliy Kalinin

Reputation: 1871

You have to normalize yours db, i.e. move NYKACatID, NYKACatIDExtra1 .. NYKACatIDExtra10 into separate table. Define proper index for that table and rewrite yours query using join.

Upvotes: 3

Vnuk
Vnuk

Reputation: 2703

If you really need to search all these fields then index them appropriately - use Profiler and database engine tuning advisor for a good starting point.

One alternative would be to extract those 10 NYKACatIDExtra fields to a separate table and arrange them as one-to-many relationship. Then use join to find categories for items and count should be much faster...

Upvotes: 2

Related Questions