September 2, 2013 at 8:20 am
Please help me to update the columns using a temp table by using inner join for a set of tables say, activity, activitytype, activitycategory
and left join to update another set of columns, so that the effect of left join does not effect the correctness of values in these columns updated.
An example will be handy.
September 2, 2013 at 8:32 am
Updating with a temp table (# table) is the same as with a normal table.
Can you explain a bit clearer where you're stuck?
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
September 2, 2013 at 8:33 am
It's funny, your last sentence could be my answer. An example of what you need with DDL, sample data and expected results will be handy to help you. 🙂
September 3, 2013 at 12:38 am
How can I modify the query such that LEFT JOIN on activities, typeofactivities can be removed, so that query performance is good? This is my query:
SELECT S.studyCode,
SOP.operatorName AS StudyDirector,
A.activityCode,
CASE ISNULL(CATCA.activityCategoryName, '') WHEN '' THEN TOA.typeOfActivityName
ELSE TOA.typeOfActivityName + ' ; ' + CATCA.activityCategoryName END AS PhaseType,
AOP.operatorName AS ActivityOwner,
SDAAFV.txtValue AS TQSDComment,
PIAAFV.txtValue AS TQPIComment
FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0
--Filter on AF with extractName 'TrialEvaluation' AND the AFValue 'major issue : trial not valid/cancelled ' (But use the AFV code instead of the name)
LEFT JOIN ActivitiesAdditionalFieldsValues AS AAFV WITH(NOLOCK) ON A.activityIncId=AAFV.activityIncId AND A.activitySqlId=AAFV.activitySqlId AND AAFV.isDeleted=0x0
INNER JOIN ActivitiesAdditionalFields AS AAF WITH(NOLOCK) ON AAFV.activityAdditionalFieldIncId=AAF.activityAdditionalFieldIncId AND AAFV.activityAdditionalFieldValueSqlId=AAF.activityAdditionalFieldSqlId AND AAF.isDeleted=0x0
----AND AAF.extractName = 'TrialEvaluation'
INNER JOIN CboValues CBOV ON AAFV.cboRecordIncId = CBOV.cboValueIncId AND AAFV.cboRecordSqlId = CBOV.cboValueSqlId AND CBOV.isDeleted=0x0
----AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled '
LEFT JOIN Operators AS AOP ON A.todoBySqlId = AOP.operatorSqlId AND A.todoByIncId = AOP.operatorIncId
LEFT JOIN Operators AS SOP ON S.directedBySqlId = SOP.operatorSqlId AND S.directedByIncId = SOP.operatorCategoryIncId
--To create PhaseType: Get the child activity with 'Running' in the typeOfActivityName
LEFT JOIN Activities AS CA
LEFT JOIN ActivitiesCategories AS CATCA ON CATCA.activityCategorySqlId=CA.activityCategorySqlId AND CATCA.activityCategoryIncId=CA.activityCategoryIncId AND CATCA.isDeleted=0x0
LEFT JOIN TypesOfActivities AS TOCA ON CA.typeOfActivitySqlId=TOCA.typeOfActivitySqlId AND CA.typeOfActivityIncId=TOCA.typeOfActivityIncId AND TOCA.isDeleted=0x0
ON A.activitySqlId=CA.fatherActivitySqlId AND A.activityIncId=CA.fatherActivityIncId AND CA.isDeleted=0x0
---AND TOCA.typeOfActivityName Like '%Running%'
--Get TQSD comment
LEFT JOIN ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF WITH(NOLOCK) ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0
ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0
AND SDAAF.extractName = 'TQSDComment'
--Get TQPI comment
LEFT JOIN ActivitiesAdditionalFieldsValues AS PIAAFV
LEFT JOIN ActivitiesAdditionalFields AS PIAAF WITH(NOLOCK) ON PIAAFV.activityAdditionalFieldIncId=PIAAF.activityAdditionalFieldIncId AND PIAAFV.activityAdditionalFieldValueSqlId=PIAAF.activityAdditionalFieldSqlId AND PIAAF.isDeleted=0x0
ON A.activityIncId=PIAAFV.activityIncId AND A.activitySqlId=PIAAFV.activitySqlId AND PIAAFV.isDeleted=0x0
AND PIAAF.extractName = 'TQPIComment'
WHERE A.isDeleted=0x0
AND TOS.typeOfStudyCode = 'EAS-01'
AND TOA.typeOfActivityCode = 'EAS-1'
AND AC.activityCategoryCode = 'EAS-1F'
AND AAF.extractName = 'TrialEvaluation'
AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled '
AND (A.activityStartDate < DateAdd(DAY,1,@ToDate) AND A.activityEndDate >= @FromDate)
ORDER BY S.studyCode, A.activityCode
September 3, 2013 at 12:53 am
Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/. Tuning is seldom about removing portions of a query.
Also ditch the nolocks, unless the users know and accept that the data the query returns can be wrong.
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
September 3, 2013 at 12:53 am
Update T1
set T1.col2 = T2.co3
From Table1 T1 left join Table2 T2
on T1.Con1 = T2.Col1
Where some condition
IS that you want?
Neeraj Prasad Sharma
Sql Server Tutorials
September 3, 2013 at 1:06 am
Junglee_George (9/3/2013)
How can I modify the query such that LEFT JOIN on activities, typeofactivities can be removed, so that query performance is good? This is my query:...
What do you want to do? An update, as in your first post, or improve the performance of this query? Or both?
If you want an update, then it would help if you show us what columns of which table you want to update.
If you want to improve the performance of this query, then post the actual execution plan as a .sqlplan attachment.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2013 at 3:16 am
Please help me to modify the query so that temp table can be used for updating the various columns like
PhaseType, TQSDComment, TQPIComment, thus avoiding the left join using the same tables like Activities, ActivitiesCategories, TypesOfActivities in a single select statement.
September 3, 2013 at 3:32 am
Why? What's the purpose of the temp table and how does it get populated?
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
September 3, 2013 at 3:33 am
Junglee_George (9/3/2013)
Please help me to modify the query so that temp table can be used for updating the various columns likePhaseType, TQSDComment, TQPIComment, thus avoiding the left join using the same tables like Activities, ActivitiesCategories, TypesOfActivities in a single select statement.
Which table contains column PhaseType?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2013 at 3:41 am
I want a query like this. Please help..Urgent...
CREATE Table #Tab (studyCode, StudyDirector, activityCode, PhaseType, ActivityOwner,TQSDComment,TQPIComment)
INSERT #Tab (studyCode, StudyDirector, activityCode, PhaseType, ActivityOwner,TQSDComment,TQPIComment)
SELECT S.studyCode,
SOP.operatorName AS StudyDirector,
A.activityCode,0,0,0,0
FROM Activities A WITH(NOLOCK)
INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0
INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0
INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0
INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0
UPDATE #Tab SET TQSDComment = SDAAFV.txtValue AS TQSDComment FROM ActivitiesAdditionalFieldsValues AS SDAAFV
LEFT JOIN ActivitiesAdditionalFields AS SDAAF WITH(NOLOCK) ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0
ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0
AND SDAAF.extractName = 'TQSDComment'
September 3, 2013 at 4:11 am
This isn't a specific problem, it's ordinary day to day TSQL development work. Start with your original query. Remove tables which don't contribute to your result set - including row count. Check if LEFT JOINS are defensive programming or should really be left joins. When you've finished pruning, it's time to optimise your query. I know from reading your posts that you are capable of doing most or all of this.
Asking us to do your day to day work for you is a bit cheeky.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2013 at 4:34 am
Lend me a helping hand rather than preaching.....
September 3, 2013 at 4:46 am
Junglee_George (9/3/2013)
Lend me a helping hand rather than preaching.....
ChrisM@Work (9/3/2013)
...Start with your original query. Remove tables which don't contribute to your result set - including row count. Check if LEFT JOINS are defensive programming or should really be left joins. When you've finished pruning, it's time to optimise your query...
Sure. Crack on with the first part - eliminating tables etc. - which would be next to impossible without sample tables anyway. When you are finished, post up the actual execution plan.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply