count query performance from 15 million of records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

count query performance from 15 million of records

Author

Topic

waqasde
Starting Member

2 Posts

Posted - 2012-06-19 : 08:24:17

hi all,

following my query is taking approx 2.5 minutes from 15 millions of records i have applied clustered index on contactid which is very usefull in other searches and non clustered cover index on contact table companyid,,city,state,country,firstname ,lastname

so this my query

--------------------------------
select COUNT(*) from CompanyContacts as contact inner join Companies as
CompanyDB on Contact.CompanyID=CompanyDB.companyID where (
lower(Contact.Country) ='usa' ) and ( ( lower(Contact.[Level])
='c-level' ) or ( lower(Contact.[Level]) ='vp-level12' ) or
( lower(Contact.[Level]) ='director-level12' ) or (
lower(Contact.[Level]) ='manager-level12' ) or (
lower(Contact.[Level]) ='staff12' ) ) and ( (
CAST(CompanyDB.employee AS INT) >=0 and CAST(CompanyDB.employee AS
INT)<=25 ) or ( CAST(CompanyDB.employee AS INT) >25 and
CAST(CompanyDB.employee AS INT)<=100 ) or ( CAST(CompanyDB.employee
AS INT) >100 and CAST(CompanyDB.employee AS INT)<=250 ) or (
CAST(CompanyDB.employee AS INT) >250 and CAST(CompanyDB.employee AS
INT)<=1000 ) or ( CAST(CompanyDB.employee AS INT) >1000 and
CAST(CompanyDB.employee AS INT)<=10000 ) or (
CAST(CompanyDB.employee AS INT) >10000 and CAST(CompanyDB.employee AS
INT)<=50000 ) or ( CAST(CompanyDB.employee AS INT) >50000 and
CAST(CompanyDB.employee AS INT)<=100000 ) or (
CAST(CompanyDB.employee AS INT) >100000 ) ) and ( (
lower(CompanyDB.[organitype])) ='public' ) or ( lower(CompanyDB.[organitype])
='private' ) or ( lower(CompanyDB.[organitype])) ='organization' )
or ( lower(CompanyDB.[organitype])) ='government' ) or (
lower(CompanyDB.[organitype])) ='educational' ) or (
lower(CompanyDB.[organitype])) ='other' ) ) and (
CAST(Contact.status AS INT) >=1 and CAST(Contact.status AS
INT)<=8 ) and Contact.IsDelete=0

---------------

this is taking 2.5 minutes to fetch all count please help me how can improve count performance with where clause

thanks
i hope you guys will not ignore my question

thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-06-20 : 19:03:24

In order to speed this up you are going to have to make sure it is either
a) Using a very selective index
b) Doing a full table scan so it does not use an index.
As it stands, I would hope an index is not being used as that would incur too much IO.
It cannot usefully use an index because of all your formulae and your columns are not in a useful order but sometimes the optimiser can be a bit index-happy so check the plan.
An index on Contact might work so long as you get rid of the lower() stuff which is not required if your collation order is right. Same for employee, same for oranitype. You need to find a column that's really selective but basically though, if you are going to return more than about 5% of your table this is as fast as it's going to go. You can save a bit of CPU by removing all the casts.
If you do find a selective column then fix your types so ints are stored as ints, then you can usefully index them.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-21 : 14:11:53

What does your Exceution Plan say?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-21 : 15:38:35

I take it that query was generated by some code generator? Humans don't tend to write code like that....

here's the code formatted a bit


select
	COUNT(*)
from
	CompanyContacts as contact
	inner join Companies as CompanyDB on Contact.CompanyID=CompanyDB.companyID
where
	(lower(Contact.Country) ='usa' )
	and (
		(lower(Contact.[Level]) ='c-level' )
		or (lower(Contact.[Level]) ='vp-level12' )
		or (lower(Contact.[Level]) ='director-level12' )
		or (lower(Contact.[Level]) ='manager-level12' )
		or (lower(Contact.[Level]) ='staff12' )
		)
	and (
			(
			CAST(CompanyDB.employee AS INT) >= 0
			and CAST(CompanyDB.employee AS INT) <=25
			)
		or (
			CAST(CompanyDB.employee AS INT) >25
			and CAST(CompanyDB.employee AS INT)<=100
			)
		or (
			CAST(CompanyDB.employee AS INT) >100
			and CAST(CompanyDB.employee AS INT)<=250
			)
		or (
			CAST(CompanyDB.employee AS INT) >250
			and CAST(CompanyDB.employee AS INT)<=1000
			)
		or (
			CAST(CompanyDB.employee AS INT) >1000
			and CAST(CompanyDB.employee AS INT)<=10000
			)
		or (
			CAST(CompanyDB.employee AS INT) >10000
			and CAST(CompanyDB.employee AS INT)<=50000
			)
		or (
			CAST(CompanyDB.employee AS INT) >50000
			and CAST(CompanyDB.employee AS INT)<=100000
			)
		or (
			CAST(CompanyDB.employee AS INT) >100000
			)
		)
		
	and (
		(lower(CompanyDB.[organitype])) ='public' )
		or ( lower(CompanyDB.[organitype]) ='private' )
		or ( lower(CompanyDB.[organitype])) ='organization' )
		or ( lower(CompanyDB.[organitype])) ='government' )
		or (lower(CompanyDB.[organitype])) ='educational' )
		or (lower(CompanyDB.[organitype])) ='other' )
		)
		
	and (
		CAST(Contact.status AS INT) >=1
		and CAST(Contact.status AS INT)<=8
		)
		
	and Contact.IsDelete=0

The only sargeable predicate there is Contact.IsDelete = 0 All the rest of that predicate mess cannot use any index on any of those columns.

I don't understand *any* of the checks that look like


	(
	CAST(CompanyDB.employee AS INT) >= 0
	and CAST(CompanyDB.employee AS INT) <=25
	)

Looking at that chunk doesn't it all decompose down into one predicate that states:


CompanyDB.employee >= 0

in fact. Isn't this the same query? Just written in a declarative, sane, readable, sargable fashion?


SELECT
	COUNT(*)
FROM
	CompanyContacts As cc
	JOIN Companies AS cd ON cd.[CompanyID] = cc.[CompanyID]
WHERE
	cc.[Country] = 'usa'
	AND cd.[employee] >= 0
	AND cc.[status] BETWEEN 1 AND 8
	AND cc.[IsDelete] = 0
	AND cc.[level] IN (
		'c-level'
		, 'vp-level12'
		, 'director-level12'
		, 'manager-level12'
		, 'staff12'
		)
	AND cd.[organitype] IN (
		'public'
		, 'private'
		, 'organization'
		, 'government'
		, 'educational'
		, 'other'
		)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-22 : 01:53:30

waqasde, based on the original statement - it will be difficult for the Optimizer to make cardinality estimate. Ask the question "can the predicates exploit the indexes?". If there are to many functions on the predicates - then the Optimizer has to calculate for every row.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

waqasde
Starting Member

2 Posts

Posted - 2012-06-25 : 13:33:18

@Transact Charlie and @jackv and @LoztInSpace thank you so much for replying and guiding me @Transact Charlie you query way is good and compact but query is still taking 5.5 minutes is there any way to make this query fast as i have already applied clustered index on contactid ,non clustered index on contact companyid and one other field as collectively in search query around 16 fields are coming so its difficult to apply index on all column which is definitely not a good practice as user can search by many criterias don't you Guru Guys know any method of fine tune this query to run fast from more then 15 millions of records. i wonder there should be some way indexing on all search criteria columns is not a solution as its approx a full or half table scan.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-06-26 : 03:18:43

What is the likely cardinality of Country, employee, status, IsDelete, level and organitype?
If you've got more than a few thousand in each then a scan would be the way to go. You certainly would *not* want to use an index.
Depending on how many values/rows are not being included in your query, you could consider partitioning so you're only looking at rows of interest (by some measure) right from the start. Careful though - a badly considered partition can make other stuff really bad.

smalik
Starting Member

1 Post

Posted - 2012-07-10 : 00:29:37

Use this one:

select COUNT(*) from CompanyContacts as contact
inner join Companies as CompanyDB on Contact.CompanyID=CompanyDB.companyID
where Contact.Country='usa' and Contact.Level in ('c-level','vp-level12','director-level12','manager-level12','staff12')
and (CAST(CompanyDB.employee AS INT) between 0 and 100000 )
and CompanyDB.organitype in ('public','private','organization','government','educational','other')
and (CAST(Contact.status AS INT) between 1 and 8 ) and Contact.IsDelete=0

Andywin
Starting Member

3 Posts

Posted - 2013-04-05 : 04:44:08

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources