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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

Andywin
Starting Member

3 Posts

Posted - 2013-04-05 : 04:44:08
unspammed
Go to Top of Page
   

- Advertisement -