SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 count query performance from 15 million of records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waqasde
Starting Member

2 Posts

Posted - 06/19/2012 :  08:24:17  Show Profile  Reply with Quote
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

939 Posts

Posted - 06/20/2012 :  19:03:24  Show Profile  Reply with Quote
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.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 06/21/2012 :  14:11:53  Show Profile  Visit jackv's Homepage  Reply with Quote
What does your Exceution Plan say?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/21/2012 :  15:38:35  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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.

Edited by - Transact Charlie on 06/21/2012 15:40:22
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 06/22/2012 :  01:53:30  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

waqasde
Starting Member

2 Posts

Posted - 06/25/2012 :  13:33:18  Show Profile  Reply with Quote
@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.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 06/26/2012 :  03:18:43  Show Profile  Reply with Quote
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.
Go to Top of Page

smalik
Starting Member

India
1 Posts

Posted - 07/10/2012 :  00:29:37  Show Profile  Reply with Quote
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
Go to Top of Page

Andywin
Starting Member

USA
3 Posts

Posted - 04/05/2013 :  04:44:08  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000