June 26, 2015 at 8:40 am
Hi
I want to tune this particular query as it is taking more time to execute and query cost is high. Suggestions are highly appreciated. I think rewriting the query is the only solution.
SELECTROW_NUMBER() OVER (PARTITION BY activityStatusIncId ORDER BY activityIncId) AS sno
,ROW_NUMBER() OVER (ORDER BY activityStatusIncId ) AS sno1
,AC.activityStatusIncId
,AC.activityStatusSqlId
,isnull(AC.budgetedTotalWorkload, 0) as budgetedTotalWorkload
,CONVERT(DATE, ISNULL(activityStartDate, ISNULL(activityScheduledStartDate, activityEarliestStartDate))) AS StartDate
,CONVERT(DATE, ISNULL(activityEndDate, ISNULL(activityScheduledEndDate, activityLatestEndDate))) AS EndDate
,AC.activityIncId
,AC.activitySqlId
,AcC.activityCategoryCode as ActivityCategoryCode
,AC.activityCode
,AC.activityName
,ST.studyCode
,AUn.activityUnitCode as Unit
INTO #Activities2
FROM Activities AS AC
INNER JOIN Studies AS ST
LEFT JOIN StudiesAdditionalFieldsValues AS SAFV
INNER JOIN StudiesAdditionalFields AS SAF
ON (SAF.studyAdditionalFieldSqlId = SAFV.studyAdditionalFieldSqlId AND SAF.studyAdditionalFieldIncId = SAFV.studyAdditionalFieldIncId AND SAF.isDeleted = 0x0 AND SAF.extractName = 'TestSystem')
ON (SAFV.studyIncId = ST.studyIncId AND SAFV.studySqlId = ST.studySqlId AND SAFV.isDeleted = 0)
ON ST.studyIncId = AC.studyIncId AND ST.studySqlId = AC.studySqlId AND ST.isDeleted = 0x0
INNER JOIN SubUnits AS SU ON (SU.subUnitIncId = AC.subUnitWhoDoIncId AND SU.subUnitSqlId = AC.subUnitWhoDoSqlId) AND SU.isDeleted = 0x0
INNER JOIN ActivitiesUnits AS AUn ON (AC.workloadActivityUnitIncId = AUn.activityUnitIncId AND AC.workloadActivityUnitSqlId = AUn.activityUnitSqlId)
INNER JOIN AnalyticalUnits AS AU ON (AU.analyticalUnitIncId = SU.analyticalUnitIncId AND AU.analyticalUnitSqlId = SU.analyticalUnitSqlId) AND AU.isDeleted = 0x0
INNER JOIN Partners AS P ON (P.partnerIncId = AU.partnerIncId AND P.partnerSqlId = AU.partnerSqlId) AND P.isDeleted = 0x0
INNER JOIN ActivitiesCategories AS AcC ON (AC.activityCategoryIncId = AcC.activityCategoryIncId AND AC.activityCategorySqlId = AcC.activityCategorySqlId)
LEFT JOIN Operators AS OP ON (OP.operatorIncId = AC.todoByIncId AND OP.operatorSqlId = AC.todoBySqlId) AND OP.isDeleted = 0x0
WHERE AC.isDeleted = 0x0
AND LTRIM(P.partnerSqlId) + '-' + LTRIM(P.partnerIncId) = @PartnerKey
AND LTRIM(AC.subUnitWhoDoSqlId) + '-' + LTRIM(AC.subUnitWhoDoIncId) = @SubUnitKey
AND (CHARINDEX(LTRIM(SAFV.cboRecordSqlId) + '-' + LTRIM(SAFV.cboRecordIncId), @TestSystemKey) <> 0 OR @TestSystemKey = '')
AND CHARINDEX(ISNULL(OP.operatorCode, 'XXXX'), @operator) > 0
AND NOT EXISTS (SELECT NULL FROM Activities AS A2
WHERE (A2.fatherActivityIncId = AC.activityIncId AND A2.fatherActivitySqlId = AC.activitySqlId)
AND A2.isDeleted = 0x0)
AND AC.activityStatusIncId NOT IN (3,7,8)
AND CONVERT(DATE, ISNULL(activityEndDate, ISNULL(activityScheduledEndDate, activityLatestEndDate))) >= @StartDate
AND CONVERT(DATE, ISNULL(activityStartDate, ISNULL(activityScheduledStartDate, activityEarliestStartDate))) <= @EndDate
Execution Plan is attached.
June 26, 2015 at 9:06 am
I'd start by adding the suggested index.
It's informing you of 79% impact.
June 26, 2015 at 9:16 am
Can you post the execution plan please, not a picture of part of it?
Index and table definitions would also be useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2015 at 9:54 am
You have functions around columns in the WHERE clause. No matter what else, you can't efficient until those are removed.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2015 at 8:00 am
What Kevin is referring to is this:
LTRIM(P.partnerSqlId) + '-' + LTRIM(P.partnerIncId) = @PartnerKey
That is going to destroy your performance and there's no way around it. You need to remove the LTRIM and the addition of the columns, probably with a calculated column. You'll never get index use otherwise and you'll always have scans. Same goes for the CHARINDEX and the CONVERT. All those functions are destroying your performance and there's nothing you can do to help until they're gone.
"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
June 29, 2015 at 12:47 am
Thank You for the comments..
I am a newbie in perf tuning. Please help in getting a alternative for CHARINDEX and LTRIMs.
Also how this part can be rewritten in view of the performance.
AND NOT EXISTS (SELECT NULL FROM Activities AS A2
WHERE (A2.fatherActivityIncId = AC.activityIncId AND A2.fatherActivitySqlId = AC.activitySqlId)
AND A2.isDeleted = 0x0)
June 29, 2015 at 5:48 am
Unfortunately, there isn't an alternative to CHARINDEX and LTRIM. You need to not use functions, at all, of any kind, on your columns. If your data requires you to pull it apart in order to query it, then it's badly structured and you need to adjust the structure. There's no other way to make it run faster.
"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
June 29, 2015 at 7:30 am
Please suggest me what all changes I can make in this SELECT statement given, taking the performance into consideration.
June 29, 2015 at 7:36 am
You could try optimizing this one. One general strategy I use is if its too expensive to make the columns look like the parameter, then make the parameter look like the columns. This means you'll need to take the value in @PartnerKey, split it at the hyphen, pad the two components out to then look just like they would be stored in the database.
LTRIM(P.partnerSqlId) + '-' + LTRIM(P.partnerIncId) = @PartnerKey
becomes
P.partnerSqlID = @partnerkey_first_part_padded_out
AND
P.partnerIncId = @partnerkey_second_part_padded_out
Obviously I myself don't know how you need to split the parameter up, maybe you could post some more info on your tables and data?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply