Sunday, October 13, 2013

[SQL 2012] Looking for a faster count than count(*) for my sp.

[SQL 2012] Looking for a faster count than count(*) for my sp.

Looking for a faster count than count(*) for my sp.

Posted: 04 Oct 2013 05:09 AM PDT

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 rowcntFROM sys.sysindexesWHERE 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?ThanksUSE [JobPortal9_10_13]GO/**********/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ============================================= -- -- 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 rowcntFROM sys.sysindexesWHERE 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 ) p1WHERE 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)

Help! Granting user access to SSRS reports

Posted: 12 Oct 2013 11:24 AM PDT

I have a report server on SS2012.Granted users/(security group)for SSRS reports using Security -> New Role Assignment -> Browser.However, users get the following error: An error has occurred during report processing. (rsProcessingAborted) The permissions granted to user 'xxx' are insufficient for performing this operation. (rsAccessDenied) What's the issue here? Do I need to grant user/SG additional read access to the backend Database reports are referring to?Thanks in advance!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog