January 4, 2022 at 8:32 pm
I work on SQL server 2014 I need to rewrite update statement with best practice
CAN I Write it without subquery ?
UPDATE FFFFF
SET FamilyGroup = STUFF(
(
SELECT DISTINCT '|' + CAST( ISNULL(REPLACE(f.FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
FROM ExtractReports.dbo.MultiMask FF
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
left JOIN [Parts].partscrubbing LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
LEFT JOIN Parts.ZFamilyGroup f ON f.ID=LC.Generic_3
WHERE ff.Masked_ID=FFFFF.Masked_ID
FOR XML PATH ('')
),1,1,'')
FROM ExtractReports.dbo.MultiMask FFFFF
January 4, 2022 at 10:34 pm
My first question on this is - do you need to remove the subquery?. Is it performing badly? At what point will it perform "good enough"? Basically, do you need to optimize it and if so, by how much.
If you need to optimize it, I would start by posting some sample data and table definitions. You MAY be able to get it to a "good enough" state by just adding some indexes. Now, without sample data and table definitions, we are just guessing in how to write it to be faster. It could be the "optimized" way we suggest tanks performance due to your indexes.
My opinion, I generally don't worry about fixing queries that are running "fast enough" and instead focus on the ones that are problematic.
Now, for best practice, I would avoid cross-database and cross-instance queries unless they are required. Here, I am not sure if that is a cross database query OR if it is just 3 part naming for the sake of being inconsistent. The reason being that cross database queries can't use statistics and usually generate poor execution plans (estimates of 1 row for example). If the tables are small, the estimate may not be too far off, but if you have large tables, you MAY benefit from pulling them into a temp table or table variable. Also, as a general rule, when I DO need to do cross database or cross instance (linked server) updates I do my UPDATEs FROM the specific database/instance. What I mean is I would be running the query FROM the ExtractReports database, not from some other one as I want to use the statistics that are on the table I am updating.
I say "as a general rule", but I mean a general rule to me. I am not certain if it is best practice or not, but it is the way I write all of my cross database/instance queries. Selects start on the place where most of the tables reside and on a tie, where most of the data resides. Insert, update, and delete start where the data will be modified.
And a best practice that I know is a best practice is to not use useless aliases. If you put an alias in, make sure the name is useful. What I mean is the alias ZFamilyGroup of f MAY make sense as f could be for family, but LC for partscubbing or FF for MultiMask doesn't seem to make a lot of sense (to me... maybe does to you).
Another thing is to be consistent. If you are going to use square brackets on the schema on one table/view, use it on all of them. And include it on both the schema and the table/view.
Another thing that is a best practice - be consistent with casing. In GENERAL, capital letters are used for keywords like SELECT, DISTINCT, FROM, WHERE, LEFT, JOIN etc. You have MOST of them capitalized, but you missed a "left".
And in my opinion (not shared by everyone) - NOLOCK should be used sparingly and only when you are certain that either the data is not changing frequently OR you don't care about the data being consistent. There are use cases for NOLOCK otherwise it would be removed from SQL Server, but I tend to try to find alternative solutions to NOLOCK where possible and have successfully removed NOLOCK from queries created before my time (and some created by me before I knew better) without having any impact on the system.
Lastly, if you are looking to remove the subquery, you could replace it with a CTE. Still basically a subquery, but syntax is different and the query optimizer treats it a little bit differently (I believe... don't quote me on that). Alternately, you could take the subquery and insert the results of it into another table (temp, variable, or permanent, depending on how often you will need it and how many rows and how you want to optimize it) and then use that table for your update. Which will perform best? I am not sure; they may all perform the same, or there may be a performance boost by one method over another.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 4, 2022 at 11:34 pm
I work on SQL server 2014 I need to rewrite update statement with best practice CAN I Write it without subquery ?
UPDATE FFFFF
SET FamilyGroup = STUFF(
(
SELECT DISTINCT '|' + CAST( ISNULL(REPLACE(f.FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
FROM ExtractReports.dbo.MultiMask FF
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
left JOIN [Parts].partscrubbing LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
LEFT JOIN Parts.ZFamilyGroup f ON f.ID=LC.Generic_3
WHERE ff.Masked_ID=FFFFF.Masked_ID
FOR XML PATH ('')
),1,1,'')
FROM ExtractReports.dbo.MultiMask FFFFF
remove those nolock - if absolutely required and your business aware of the potential issues use the corresponding "set transaction isolation level" instead
January 5, 2022 at 5:57 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply