January 29, 2009 at 7:54 am
Hi, can anyone suggest how I can improve this query? Are there any glaring bad bits of code in this. Many thanks in advance
DECLARE
@DateFrom datetime,
@DateTo datetime,
@Site varchar(255),
@Organisation varchar(255),
@Keyword varchar(255),
@JobType varchar(255),
@Location varchar(255),
@Recruiter varchar(255)
SELECT
@DateFrom = '2009-01-27',
@DateTo = '2009-01-29',
@Site = '16',
@Organisation = '',
@Keyword = '',
@JobType = '',
@Location = '',
@Recruiter = '';
WITH Jobreps (JobID, OrganisationID ,OrganisationName, [Title], [Reference], [SlotStartDate], [SlotEndDate], JobViews,
/*DirectApplications,*/ EmailApplications, URLApplications, VisitThirdPartyWebLink, ContactDetailsViews, LogoClicks) AS
(
SELECT [JobDetail].JobID
,[JobDetail].OrganisationID
,Organisation.OrganisationName
,[JobDetail].[Title]
,[JobDetail].[Reference]
,[JobDetail].[SlotStartDate]
,[JobDetail].[SlotEndDate]
,SUM(JobMeasure.JobViews) AS JobViews
/*,SUM(JobMeasure.DirectApplications) AS DirectApplications*/
,SUM(JobMeasure.EmailApplications) AS EmailApplications
,SUM(JobMeasure.URLApplications) AS URLApplications
,SUM(JobMeasure.VisitThirdPartyWebLink) AS VisitThirdPartyWebLink
,SUM(JobMeasure.ContactDetailsViews) AS ContactDetailsViews
,0 AS LogoClicks
FROM [dbo].[JobDetail]
INNER JOIN dbo.Site
ON [JobDetail].SiteID = Site.Channel
INNER JOIN dbo.Organisation
ON [JobDetail].OrganisationID = Organisation.OrganisationID
LEFT JOIN dbo.JobMeasure
ON [JobDetail].JobId = JobMeasure.[JobId]
AND CONVERT(varchar,JobMeasure.MeasureDate,112) Between CONVERT(varchar,@DateFrom,112) AND CONVERT(varchar,@DateTo,112)
WHERE CONVERT(varchar,[SlotStartDate],112) = CONVERT(varchar, @DateFrom,112)
AND Site.ID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Site,Site.ID), ','))
AND (Organisation.OrganisationID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.OrganisationID), ','))
OR Organisation.ParentID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.ParentID), ',')))
--AND dbo.fn_SalaryRange([JobDetail].JobId, @SalaryType, @SalaryFrom,@SalaryTo) = 1
AND dbo.fn_Keyword([JobDetail].JobId,@Keyword) = 1
AND dbo.fn_Category([JobDetail].JobId,@JobType) = 1
AND dbo.fn_Category([JobDetail].JobId,@Location) = 1
--AND dbo.fn_Category([JobDetail].JobId,@JobPosition) = 1
--AND dbo.fn_Category([JobDetail].JobId,@Sector) = 1
AND JobDetail.Status IN ('Published','Archived')
GROUP BY [JobDetail].JobID
,[JobDetail].OrganisationID
,Organisation.OrganisationName
,[JobDetail].[Title]
,[JobDetail].[Reference]
,[JobDetail].[SlotStartDate]
,[JobDetail].[SlotEndDate]
UNION
SELECT NULL AS JobID
,Organisation.OrganisationID
,Organisation.OrganisationName
,NULL AS [Title]
,NULL AS [Reference]
,NULL AS [SlotStartDate]
,NULL AS [SlotEndDate]
,0 AS JobViews
/*,0 AS DirectApplications*/
,0 AS EmailApplications
,0 AS URLApplications
,0 AS VisitThirdPartyWebLink
,SUM(RecruiterMeasure.ContactDetailsViews) AS ContactDetailsViews
,SUM(RecruiterMeasure.LogoClicks) AS LogoClicks
FROM dbo.Organisation
INNER JOIN dbo.RecruiterMeasure
ON Organisation.OrganisationID = RecruiterMeasure.OrganisationID
AND CONVERT(varchar,RecruiterMeasure.MeasureDate,112) Between CONVERT(varchar,@DateFrom,112) AND CONVERT(varchar,@DateTo,112)
INNER JOIN dbo.Site
ON RecruiterMeasure.SiteID = Site.ID
WHERE Site.ID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Site,Site.ID), ','))
AND (Organisation.OrganisationID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.OrganisationID), ','))
OR Organisation.ParentID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.ParentID), ',')))
AND Organisation.OrganisationID IN
(SELECT DISTINCT Organisation.OrganisationID
FROM dbo.Organisation
INNER JOIN [dbo].[JobDetail]
ON Organisation.OrganisationID = [JobDetail].OrganisationID
INNER JOIN dbo.Site
ON [JobDetail].SiteID = Site.Channel
WHERE CONVERT(varchar,[SlotStartDate],112) = CONVERT(varchar, @DateFrom,112)
AND Site.ID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Site,Site.ID), ','))
AND (Organisation.OrganisationID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.OrganisationID), ','))
OR Organisation.ParentID IN (SELECT Item FROM dbo.fn_Split(ISNULL(@Organisation,Organisation.ParentID), ',')))
--AND dbo.fn_SalaryRange([JobDetail].JobId, @SalaryType, @SalaryFrom,@SalaryTo) = 1
AND dbo.fn_Keyword([JobDetail].JobId,@Keyword) = 1
AND dbo.fn_Category([JobDetail].JobId,@JobType) = 1
AND dbo.fn_Category([JobDetail].JobId,@Location) = 1
--AND dbo.fn_Category([JobDetail].JobId,@JobPosition) = 1
--AND dbo.fn_Category([JobDetail].JobId,@Sector) = 1
AND JobDetail.Status IN ('Published','Archived')
)
GROUP BY Organisation.OrganisationID
,Organisation.OrganisationName
)
SELECT Jobreps.JobID
,OrganisationID
,OrganisationName
,[Title]
,[Reference]
,[SlotStartDate]
,[SlotEndDate]
,SUM(JobViews) AS JobViews
,SUM(EmailApplications) AS EmailApplications
,SUM(URLApplications) AS URLApplications
,SUM(VisitThirdPartyWebLink) AS VisitThirdPartyWebLink
,SUM(ContactDetailsViews) AS ContactDetailsViews
,SUM(LogoClicks) AS LogoClicks
,(SELECT COUNT(JobApplication.JobId) FROM jobapplication
WHERE jobapplication.jobid = Jobreps.JobID
AND CONVERT(varchar,DateApplication,112) Between CONVERT(varchar,@DateFrom,112) AND CONVERT(varchar,@DateTo,112)
) AS DirectApplications
FROM Jobreps
WHERE Jobreps.JobID IS NOT NULL
GROUP BY Jobreps.JobID
,OrganisationID
,OrganisationName
,[Title]
,[Reference]
,[SlotStartDate]
,[SlotEndDate]
January 29, 2009 at 8:07 am
From a quick first glance, yes.
Function calls in your where clause are usually a performance killer (do they do data access? Post them if possible.)
Convert'ing datetimes to varchars will kill your index possibilities.
Also, if you could post an XML execution plan of the query, (The performance guru's are gonna ask for it anyways, so I'll get the ball rolling), you will get a lot more targeted responses.
January 29, 2009 at 8:09 am
Here are some of the functions
CREATE FUNCTION [dbo].[fn_Category]
(@JobID int, @Category varchar(500))
RETURNS bit
BEGIN
DECLARE @Rows int, @Found bit
SELECT @Rows = 0, @Found = 0
IF @Category IS NULL
SELECT @Found = 1
ELSE
BEGIN
SELECT @Rows = COUNT(Jobid)
FROM dbo.JobCategory
INNER JOIN dbo.Category
ON JobCategory.CategoryID = Category.CategoryID
LEFT JOIN dbo.Category AS ParentCategory
ON Category.ParentId = ParentCategory.CategoryID
LEFT JOIN dbo.Category AS ParentCategory2
ON ParentCategory.ParentId = ParentCategory2.CategoryID
LEFT JOIN dbo.Category AS ParentCategory3
ON ParentCategory2.ParentId = ParentCategory3.CategoryID
LEFT JOIN dbo.Category AS ParentCategory4
ON ParentCategory3.ParentId = ParentCategory4.CategoryID
LEFT JOIN dbo.Category AS ParentCategory5
ON ParentCategory4.ParentId = ParentCategory5.CategoryID
WHERE JobCategory.JobId = @JobId
AND (Category.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ','))
OR ParentCategory.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ','))
OR ParentCategory2.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ','))
OR ParentCategory3.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ','))
OR ParentCategory4.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ','))
OR ParentCategory5.CategoryID IN (SELECT Item FROM dbo.fn_Split(@Category, ',')))
IF @Rows > 0
SELECT @Found = 1
ELSE
SELECT @Found = 0
END
RETURN (@Found)
END
CREATE FUNCTION [dbo].[fn_Keyword]
(@JobID int, @Keyword varchar(255))
RETURNS bit
BEGIN
DECLARE @Rows int, @Found bit
SELECT @Rows = 0, @Found = 0
IF @Keyword IS NULL
SELECT @Found = 1
ELSE
BEGIN
SELECT @Keyword = REPLACE(REPLACE(@Keyword,'''',''),'"','')
SELECT @Keyword = '"'+@Keyword+'"'
SELECT @Rows = COUNT(Jobid)
FROM dbo.JobDetail
WHERE JobID = @JobID
AND CONTAINS([JobDetail].*, @Keyword)
IF @Rows > 0
SELECT @Found = 1
ELSE
SELECT @Found = 0
END
RETURN (@Found)
END
CREATE FUNCTION [dbo].[fn_Split]
(
@ItemList VARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(4000))
AS
BEGIN
DECLARE @tempItemList VARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item VARCHAR(4000)
--SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
January 29, 2009 at 8:39 am
January 29, 2009 at 8:47 am
Yip I agree, Multi-line functions in where clause are not good for performance at all as they tend to run for every row.
as mentioned the Converting of dates on your joins clause is going to make most of your indexes useless for the query optimizer.
Could you also post the query plan and any indexes that are used in this query. if you want more help.
however I would suggest looking into those functions first
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 9:07 am
I have pasted the query plan in. I have attach a text file. Would there be an improvement if perhaps I populated temporary tables with the funtion results and also created a temporary variable with the common CONVERT results. I could then refernce these entities in the main query?
January 29, 2009 at 9:35 am
Could you not try and chagne the functions to be inline functions?
Secondly, could you save the graphical execution plan from SSMS and attach that file, as it's easier to read.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 9:41 am
graphical Plan attached. You need to rename .txt extension to .sqlplan
Thanks
January 29, 2009 at 9:56 am
OK cool thanks
Could you supply the indexes on these three tables please:
JobDetails
JobMeasures
Organisation
Also here is another way to do the split function without using a loop it requires a tally table and it's inline so I think it might perform better.
CREATE FUNCTION dbo.fn_StringtoTable2
(
@String NVARCHAR(MAX) ,
@Delimiter CHAR(1)
)
RETURNS TABLE
AS RETURN(
--INSERT INTO @Results
SELECT SUBSTRING(@String+@Delimiter, n,
CHARINDEX(@Delimiter, @String+@Delimiter, n) - n) as String
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING(@Delimiter + @String,
n, 1) = @Delimiter )
GO
Thanks
chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
January 29, 2009 at 12:01 pm
Since the functions seem well in hand, I'll address other stuff.
You're using CONVERT on your columns in the WHERE clause. This will absolutely prevent the indexes, if any from being used. You're getting an index scan on idx_JobApplication that is probably caused by this. It's taking up 24% of the estimated cost (most of the cost is probably the functions, but once you get clear of them, this will be an issue too).
You also have a key lookup on the JobDetail table. There are only three columns in the output list from the index seek operation on IX_JobDetail_1. You might consider making them part of the index through an INCLUDE statement. Or, you might want to restructure the index key structure itself to include those columns. Testing is the only way to be sure, but probably the INCLUDE is the way to go.
That's about it from this estimated plan. I'd be curious to see an actual plan instead. Can you post that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 1:01 pm
Grant Fritchey (1/29/2009)You're using CONVERT on your columns in the WHERE clause. This will absolutely prevent the indexes, if any from being used. You're getting an index scan on idx_JobApplication that is probably caused by this. It's taking up 24% of the estimated cost (most of the cost is probably the functions, but once you get clear of them, this will be an issue too).
Grant, does the same behavior apply for the Cast keyword? Because the application we use sends me some dynamic SQL that is always Casted as nvarchar(max). Could that be an issue for the index usage?
Thanks,
Cheers,
J-F
January 29, 2009 at 1:05 pm
Jean-François Bergeron (1/29/2009)
Grant, does the same behavior apply for the Cast keyword? Because the application we use sends me some dynamic SQL that is always Casted as nvarchar(max). Could that be an issue for the index usage?Thanks,
Yep. What's worse, it applies to implicit conversions too. So it really can get you. If you CAST the values before you use them in the query, you'll be fine, but parameters and columns should match in data type and length or you might not get the index used properly in the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 1:13 pm
Wow, I did not know that. So when I receive a string, for a where Clause, like this one, for example:
CAST(MfrCode AS NVARCHAR(max)) LIKE 'tel%' AND
CAST(VendorCode AS NVARCHAR(max)) LIKE 'sch%'
It will not use the indexes I have set on MfrCode (nvarchar(5)) or VendorCode (nvarchar(5)). This sounds pretty bad, as we got this design all over the application, you got me worried there...
Cheers,
J-F
January 29, 2009 at 1:45 pm
Jean-François Bergeron (1/29/2009)
Wow, I did not know that. So when I receive a string, for a where Clause, like this one, for example:
CAST(MfrCode AS NVARCHAR(max)) LIKE 'tel%' ANDCAST(VendorCode AS NVARCHAR(max)) LIKE 'sch%'
It will not use the indexes I have set on MfrCode (nvarchar(5)) or VendorCode (nvarchar(5)). This sounds pretty bad, as we got this design all over the application, you got me worried there...
You should be. And the multi-line UDF's are an issue too. They don't have statistics so they only show up as a one line table to the optimizer. Again, this will lead to serious performance issues when they have more than, say, 100 rows (depending).
Probably, right now, that's your #1 problem. But this CAST thing will also be an issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 2:07 pm
Well thanks, Even though it was only a side question, I'm letting the post to the owner =)
I quite stole the answers there, but it helped me understand why sometimes the optimizer did not pick those indexes, Thanks again,
Cheers,
J-F
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply