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)

Posted: 04 Oct 2013 11:25 PM PDT

Hi all,What all agents are available in SQL Server 2012 and is there any agent added / deprecated from earlier versions !!!Thanks.

Determine the license of an existing SQL Server 2012 installation

Posted: 27 Nov 2012 11:56 PM PST

Out product is based on SQL Server 2012 and comes with a core license or with a server+CAL license (based on the customers order). We want to offer our customers the possibility to exchange the SQL Server 2012 instance on which our product is built up (uninstall server+CAL licensed instance and install core licensed instance OR using their own existing SQL Server instance).Therefore I have to find out a way of determining wether an existing SQL Server 2012 installation uses a core license or a server+CAL license. This information is needed for correct user management in our extensions of SQL Server 2012, so the mechanism for getting the information needs to be automatable.Is there any way of getting that information (apart from asking the cutomers IT guy)? Thanks a lot!

Sql samples, sql tips

Saturday, October 5, 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.

No comments:

Post a Comment

Search This Blog