Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Looking for a faster count than count(*) for my sp. Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 12:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
I have the following sp which is being slowed down by the following count statement:

-(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (

I have tried:

--(SELECT
-- Total_Rows= SUM(st.row_count)
--FROM
-- sys.dm_db_partition_stats st
--WHERE
-- object_name(object_id) = 'EMPLOYMENT_OPPORTUNITIES_CTE' AND (index_id < 2))

-- as TotalCount FROM (

which is very fast, but does not give me a TotalCount value.

I have also tried:

(SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID('EMPLOYMENT_OPPORTUNITIES_CTE')
AND (indid = 0 OR indid = 1))

as TotalCount FROM (

which is also very fast, but still no TotalCount value.

Am i stuck with count(*), or is there a faster way to do it, in this situation?

Thanks


USE [JobPortal9_10_13]
GO
/**********/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- Description: <Description,[Get All Jobs Searched Structured SQL],>
--
-- =============================================
ALTER Procedure [dbo].[zianGetAllJobsSearched10_1_13FAST]
-- Add the parameters for the stored procedure here

@Title varchar(250),
@Industry int,
@Industry2 int,
@Industry3 int,
@Date int,
@JobTitle int,
@JobType int,
@Experience int,
@Education int,
@State int,
@City int,
@Salary int,
@MaxSalary int,
@fromRec int,
@toRec int,
@SortType VARCHAR(50),
@SortOrder VARCHAR(10)
WITH RECOMPILE
AS


DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));
IF (@Title = '')
SET @Title = NULL;
WITH EMPLOYMENT_OPPORTUNITIES_CTE(id,title,contactperson,lastmodified,description,workexperiencetypeid,
workexperience,jobtypeid,AcademicExperienceTypeId,
industryid,industryid2,industryid3,salaryminid,salarymaxid,
--jobTitle,
city,state,
PostalCode,positions,lastmodified2)
--,deadline)
AS(
SELECT
e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid,

isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience,
isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype,
isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3,
salaryminid,salarymaxid,
--isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle,
isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city,
isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state,
isnull((select comp.[PostalCode] from Companys comp where comp.Id=c.id),'') as PostalCode,
positions,
substring(cast(e.LastModified as varchar(20)),1,12) as lastmodified2
--ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
from EmploymentOpportunities e With (nolock)
--left outer join companys c on e.officeid=c.id
--inner join companys c on e.officeid=c.id
inner join companys c on e.companyid=c.id

where (@Title IS NULL or title = @Title)
and (@Industry = 0 OR e.industryid = @Industry)
and (@Industry2 = 0 OR Industryid2 = @Industry2)
and (@Industry3 = 0 OR Industryid3 = @Industry3)
and (@Date = 0 OR lastmodified >= @Date)
and lastmodified is not null and lastmodified > @ActualDate
--and e.LastModified >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
--and (@JobTitle = 0 OR title = @JobTitle)
and (@JobType = 0 OR jobtypeid = @JobType)
and (@Experience = 0 OR workexperiencetypeid = @Experience)
and (@Education = 0 OR academicexperiencetypeid = @Education)
and (@State = 0 OR c.RegionId = @State)
and (@City = 0 OR c.CityId = @City)
and (@Salary = 0 OR SalaryMinID >= @Salary)
and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary)


)

SELECT
id,title as Title,contactperson as ContactPerson,lastmodified,description as Description,workexperience,
jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,
salaryminid as salary,salarymaxid as maxsalary,
--jobtitle as jobTitle,
city + ', ' + state + ', ' + PostalCode as Location,
positions as Positions,
--deadline,
rownumber as RowNumber,

--(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (


--(SELECT
-- Total_Rows= SUM(st.row_count)
--FROM
-- sys.dm_db_partition_stats st
--WHERE
-- object_name(object_id) = 'EMPLOYMENT_OPPORTUNITIES_CTE' AND (index_id < 2))

-- as TotalCount FROM (


(SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID('EMPLOYMENT_OPPORTUNITIES_CTE')
AND (indid = 0 OR indid = 1))

as TotalCount FROM (



SELECT
id,title,contactperson,lastmodified,description,workexperience,
jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,
--jobtitle,
city,state,PostalCode,
--Location,
positions,
--deadline,

ROW_NUMBER() OVER (ORDER BY LastModified DESC ) RowNumber
FROM EMPLOYMENT_OPPORTUNITIES_CTE
) p1

WHERE RowNumber BETWEEN @fromRec AND @toRec

ORDER BY
CASE WHEN @SortType = 'LastModified'
AND @SortOrder = 'DESC'
THEN LastModified END DESC,
CASE WHEN @SortType = 'LastModified'
AND @SortOrder != 'DESC'
THEN LastModified END,
CASE WHEN @SortType = 'City'
AND @SortOrder = 'DESC'
THEN City END DESC,
CASE WHEN @SortType = 'City'
AND @SortOrder != 'DESC'
THEN City END,
CASE WHEN @SortType = 'State'
AND @SortOrder = 'DESC'
THEN State END DESC,
CASE WHEN @SortType = 'State'
AND @SortOrder != 'DESC'
THEN State END,
CASE WHEN @SortType = 'Title'
AND @SortOrder = 'DESC'
THEN Title END DESC,
CASE WHEN @SortType = 'Title'
AND @SortOrder != 'DESC'
THEN Title END

OPTION(Maxdop 8)
Post #1501723
Posted Friday, October 4, 2013 12:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 43,016, Visits: 36,177
What are you trying to count? If all the rows in the table, then you can query sys.partitions or sys.dm_db_partition_stats (sysindexes is deprecated and should not be used). If you're trying to count a subset of the table, then you'll need to use COUNT(*)

If you're trying to count the rows selected from the CTE, try @@RowCount, though that CTE is going to perform terribly, count or no count. A CTE is not a table and hence doesn't appear in any of the catalog views.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501731
Posted Friday, October 4, 2013 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
isuckatsql (10/4/2013)
I have the following sp which is being slowed down by the following count statement:

-(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (



No offense but you have a lot more in that code than that single statement that is making it slow.

You have tons of correlated subqueries. These would be better as joins instead.

You are using a catch all type of query which can be horrible for performance. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Not a performance issue but do you understand all the ramification behind NOLOCK? If possible duplicate and/or missing rows is acceptable then this hint might be ok.

Why the MAXDOP?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501732
Posted Friday, October 4, 2013 12:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
Here is the OP's formatted so we can read it.



ALTER PROCEDURE [dbo].[zianGetAllJobsSearched10_1_13FAST]
-- Add the parameters for the stored procedure here
@Title VARCHAR(250)
,@Industry INT
,@Industry2 INT
,@Industry3 INT
,@Date INT
,@JobTitle INT
,@JobType INT
,@Experience INT
,@Education INT
,@State INT
,@City INT
,@Salary INT
,@MaxSalary INT
,@fromRec INT
,@toRec INT
,@SortType VARCHAR(50)
,@SortOrder VARCHAR(10)
WITH RECOMPILE
AS
DECLARE @ActualDate DATETIME = cast((
(
SELECT dateadd(d, @Date, GETDATE())
)
) AS VARCHAR(20));

IF (@Title = '')
SET @Title = NULL;

WITH EMPLOYMENT_OPPORTUNITIES_CTE (
id
,title
,contactperson
,lastmodified
,description
,workexperiencetypeid
,workexperience
,jobtypeid
,AcademicExperienceTypeId
,industryid
,industryid2
,industryid3
,salaryminid
,salarymaxid
,
--jobTitle,
city
,STATE
,PostalCode
,positions
,lastmodified2
)
--,deadline)
AS (
SELECT e.id
,title
,contactperson
,lastmodified
,e.description
,workexperiencetypeid
,isnull((
SELECT we.[Name]
FROM workexperiencetypes we
WHERE we.id = e.workexperiencetypeid
), '') AS workexperience
,isnull((
SELECT jot.[Name]
FROM jobtypes jot
WHERE jot.id = e.jobtypeid
), '') AS jobtype
,isnull((
SELECT edu.[Name]
FROM Degree edu
WHERE edu.Id = e.AcademicExperienceTypeId
), '') AS education
,isnull((
SELECT ind.[Name]
FROM industries ind
WHERE ind.id = e.industryid
), '') AS industryname
,isnull((
SELECT ind.[Name]
FROM industries ind
WHERE ind.id = e.industryid2
), '') AS industryname2
,isnull((
SELECT ind.[Name]
FROM industries ind
WHERE ind.id = e.industryid3
), '') AS industryname3
,salaryminid
,salarymaxid
,
--isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle,
isnull((
SELECT ci.[Name]
FROM cities ci
WHERE ci.id = c.cityid
), '') AS city
,isnull((
SELECT r.[AbbreviatedName]
FROM regions r
WHERE r.id = c.regionid
), '') AS STATE
,isnull((
SELECT comp.[PostalCode]
FROM Companys comp
WHERE comp.Id = c.id
), '') AS PostalCode
,positions
,substring(cast(e.LastModified AS VARCHAR(20)), 1, 12) AS lastmodified2
--ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
FROM EmploymentOpportunities e WITH (NOLOCK)
--left outer join companys c on e.officeid=c.id
--inner join companys c on e.officeid=c.id
INNER JOIN companys c ON e.companyid = c.id
WHERE (
@Title IS NULL
OR title = @Title
)
AND (
@Industry = 0
OR e.industryid = @Industry
)
AND (
@Industry2 = 0
OR Industryid2 = @Industry2
)
AND (
@Industry3 = 0
OR Industryid3 = @Industry3
)
AND (
@Date = 0
OR lastmodified >= @Date
)
AND lastmodified IS NOT NULL
AND lastmodified > @ActualDate
--and e.LastModified >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
--and (@JobTitle = 0 OR title = @JobTitle)
AND (
@JobType = 0
OR jobtypeid = @JobType
)
AND (
@Experience = 0
OR workexperiencetypeid = @Experience
)
AND (
@Education = 0
OR academicexperiencetypeid = @Education
)
AND (
@State = 0
OR c.RegionId = @State
)
AND (
@City = 0
OR c.CityId = @City
)
AND (
@Salary = 0
OR SalaryMinID >= @Salary
)
AND (
@MaxSalary = 0
OR SalaryMaxID <= @MaxSalary
)
)
SELECT id
,title AS Title
,contactperson AS ContactPerson
,lastmodified
,description AS Description
,workexperience
,jobtypeid AS jobtype
,academicexperiencetypeid AS education
,industryid AS industryname
,industryid2 AS industryname2
,industryid3 AS industryname3
,salaryminid AS salary
,salarymaxid AS maxsalary
,
--jobtitle as jobTitle,
city + ', ' + STATE + ', ' + PostalCode AS Location
,positions AS Positions
,
--deadline,
rownumber AS RowNumber
,
--(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (
--(SELECT
-- Total_Rows= SUM(st.row_count)
--FROM
-- sys.dm_db_partition_stats st
--WHERE
-- object_name(object_id) = 'EMPLOYMENT_OPPORTUNITIES_CTE' AND (index_id < 2))
-- as TotalCount FROM (
(
SELECT rowcnt
FROM sys.sysindexes
WHERE id = OBJECT_ID('EMPLOYMENT_OPPORTUNITIES_CTE')
AND (
indid = 0
OR indid = 1
)
) AS TotalCount
FROM (
SELECT id
,title
,contactperson
,lastmodified
,description
,workexperience
,jobtypeid
,academicexperiencetypeid
,industryid
,industryid2
,industryid3
,salaryminid
,salarymaxid
,
--jobtitle,
city
,STATE
,PostalCode
,
--Location,
positions
,
--deadline,
ROW_NUMBER() OVER (
ORDER BY LastModified DESC
) RowNumber
FROM EMPLOYMENT_OPPORTUNITIES_CTE
) p1
WHERE RowNumber BETWEEN @fromRec
AND @toRec
ORDER BY CASE
WHEN @SortType = 'LastModified'
AND @SortOrder = 'DESC'
THEN LastModified
END DESC
,CASE
WHEN @SortType = 'LastModified'
AND @SortOrder != 'DESC'
THEN LastModified
END
,CASE
WHEN @SortType = 'City'
AND @SortOrder = 'DESC'
THEN City
END DESC
,CASE
WHEN @SortType = 'City'
AND @SortOrder != 'DESC'
THEN City
END
,CASE
WHEN @SortType = 'State'
AND @SortOrder = 'DESC'
THEN STATE
END DESC
,CASE
WHEN @SortType = 'State'
AND @SortOrder != 'DESC'
THEN STATE
END
,CASE
WHEN @SortType = 'Title'
AND @SortOrder = 'DESC'
THEN Title
END DESC
,CASE
WHEN @SortType = 'Title'
AND @SortOrder != 'DESC'
THEN Title
END
OPTION (MAXDOP 8)




_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501736
Posted Friday, October 4, 2013 1:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 43,016, Visits: 36,177
Sean Lange (10/4/2013)
You have tons of correlated subqueries. These would be better as joins instead.


Not necessarily.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501741
Posted Friday, October 4, 2013 1:11 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
In testing the sub queries proved faster than Joins, as did the MAXDOP and NOLOCK.

The data returned, in before and after testing of the above hints, was the same.

This query with the count(*) included takes 300 ms to process 700k records.

Using the non count(*) methods, it takes 20 ms to process 700k records, but i lose the total count.

20ms is pretty fast IMHO :)

I have researched a ton of data on the internet regarding query performance, and hired a couple of very senior SQL DBA's to also help with query performance and optimization, and this is what i have ended up with.

Thanks for your feedback !
Post #1501745
Posted Friday, October 4, 2013 1:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 43,016, Visits: 36,177
isuckatsql (10/4/2013)
In testing the sub queries proved faster than Joins, as did the MAXDOP and NOLOCK.


So you don't mind if your results are inaccurate. Have you confirmed with the users that this report is for that they're also fine with their reports potentially being wrong?

I have researched a ton of data on the internet regarding query performance, and hired a couple of very senior SQL DBA's to also help with query performance and optimization, and this is what i have ended up with.


At risk of sounding rude and arrogant, if this is what those 'very senior' DBAs came up with, I would question just how senior they are, because the posted query has a some rather basic performance problems with it (the hints, the catch-all query), not to mention a demonstrated lack of understanding on what a CTE is (looking in the catalog views for a CTE).



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501746
Posted Friday, October 4, 2013 1:34 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Its a work in progress, so its the best i have so far.

What is interesting is that Microsoft DBA's could not even get the query quicker than 5 seconds !

The more i used normalization, the slower the query performed, so it has gradually been denormalized, to improve performance.

The senior DBA's had over 15 yrs experience, and worked for Fortune 500 companies, managing databases with many Terabytes of data, so they could not have been that bad.

Either way, thanks for your feedback :)
Post #1501752
Posted Friday, October 4, 2013 1:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 43,016, Visits: 36,177
isuckatsql (10/4/2013)
The more i used normalization, the slower the query performed, so it has gradually been denormalized, to improve performance.


Oh dear, that's really not going to help overall performance in the long run....
Denormalisation, hints, catch-all queries, you've got a lot of things that are very likely to cause you a lot of problems there.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501759
Posted Friday, October 4, 2013 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
isuckatsql (10/4/2013)
In testing the sub queries proved faster than Joins, as did the MAXDOP and NOLOCK.

The data returned, in before and after testing of the above hints, was the same.

This query with the count(*) included takes 300 ms to process 700k records.

Using the non count(*) methods, it takes 20 ms to process 700k records, but i lose the total count.

20ms is pretty fast IMHO :)

I have researched a ton of data on the internet regarding query performance, and hired a couple of very senior SQL DBA's to also help with query performance and optimization, and this is what i have ended up with.

Thanks for your feedback !


So you have spent thousands of dollars hiring consultants to help you with the query and what they came up with isn't fast enough so you turned to an online forum and continue to defend the code that you paid for? I am not a performance expert by any means but there is a LOT of low hanging fruit in that code for improvements. It seems to me that you turned to this forum because you need some help. We can help but you either have to listen to our ideas and move away from your consultant's efforts or you will be stuck trying to sort it out without our help.

I don't who your consultants were but I can safely say that I would listen very closely to anything Gail has to say when it comes to this type of thing. She can make this thing scream if you work with her.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1501769
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse