November 9, 2008 at 1:23 am
Comments posted to this topic are about the item Normalizing-Denormalized Tables
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
November 9, 2008 at 12:47 pm
The function CombineGroupNames() can be optimzed using this concat-logic:
DECLARE @Groups VarChar(MAX)
SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)
FROM Resource R
INNER JOIN ResourceSecurityGroup RG
ON R.ResourceID = RG.ResourceID
INNER JOIN SecurityGroup G
ON G.GroupID = RG.GroupID
WHERE R.ResourceID = @ResourceID
RETURN @Groups
/Mike
November 9, 2008 at 9:49 pm
I'm all for normalizing data... but if no one is going to use the normalized form, then you shouldn't bother. In this case, it actually provides a negative ROI as well as a performance problem because the solution uses RBAR on steroids and the fact that no one has the nads to smack the vendor.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2008 at 11:30 pm
Micke Schonning (11/9/2008)
The function CombineGroupNames() can be optimzed using this concat-logic:DECLARE @Groups VarChar(MAX)
SELECT @groups = coalesce(@groups +'|' + G.GroupName. G.GroupName)
FROM Resource R
INNER JOIN ResourceSecurityGroup RG
ON R.ResourceID = RG.ResourceID
INNER JOIN SecurityGroup G
ON G.GroupID = RG.GroupID
WHERE R.ResourceID = @ResourceID
RETURN @Groups
/Mike
Thank Mike. I am still new to articles; I don't think I can make adjustment. But this is very helpful hint thanks :).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
November 9, 2008 at 11:34 pm
Hi Jeff,
Thank-you for the comments, I didn't think RBAR would be big issue in my case because the table had limited number of rows and wasn't going to grow. But I forgot to take into account when posting article here that other people reading this article it can be an issue. I am always telling the developers to stop working on the data in SQL Server on row-per-row basis; and I am doing it here myself. Thanks again for the *swift kick*; I will do better next time.
And your comment on Vendor, I have fought more then one battle against vendors. And lost them all, because I always get brought in at the last moment of when a system is going live; or when its been down for a while. So when working on it I find something that should be changed or altered. I get two responses, 1) it costs money to fix it and 2) we don't have time for it right now; we'll look at it later.
- Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
November 10, 2008 at 2:02 am
I also wonder about the praticality of this. Surely it would only be important to normalize a 3rd parties table where the normalised tables would fully utilise CRUD (Create, read, update, & delete). Since the only "benefit" here is the read aspect I would think it would be better off untouched.
November 10, 2008 at 2:13 am
Hi Mihit,
Thank you for a nice example on how to get yourself away from bad table design, when retaining backwards compatibility.
What you could also have shown was the use of Insert Instead-of triggers, so that the view was updateable. Then you would have had 100% backwards compability.
I also wondered over your use of a cursor, but I thought that was because the set-based solution cannot garantee an ordered result.
Best regards,
Henrik Staun Poulsen
Denmark
November 10, 2008 at 4:01 am
Nice article. There are some mistakes in examples which indicates lack of attention to details, but the intent for sharing ideas is most important.
You can expand on this. What you got is backward compatibility for select queries. Additionally, you can create "instead of" triggers on views, so you get update compatibility too. Idea for part II of the article. 🙂
November 10, 2008 at 4:37 am
A good article to rectify the mess of creating unnormalized tables. After going thru this article people should realize the importance of normalization
November 10, 2008 at 4:37 am
The function CombineGroupNames can be dispensed with entirely by defining the view thus:
CREATE View [dbo].[ApplicationResources]
AS
SELECT R.[ResourceName]
,R.[IsEnabled]
,replace(replace(replace( (
SELECT '['+sg.GroupName +']'
FROM ResourceSecurityGroup rsg
JOIN SecurityGroup sg ON rsg.GroupID=sg.GroupID
WHERE rsg.ResourceID=R.ResourceID
ORDER BY GroupName
FOR XML PATH('') ), '][', '|'), '[', ''), ']', '') as 'Groups'
FROM dbo.Resource R
Of course, with such as small dataset it's difficult to determine what's most efficient, but getting rid of the cursor is always a good idea.
[Edit] BTW, I agree with the comments that 'in the real world' it would probably not be worthwhile to do this on such a small dataset, but it does serve as a good example of a workaround to a problem where the vendor won't fix what may be an obvious problem. In fact, I've used similar workarounds (creating views for old tables) when a large application needs various tables restructuring but resource meant that all sections of the application couldn't be changed to use the new structure in one go.
Derek
November 10, 2008 at 6:04 am
If the tables were not being updated to often wouldn't an indexed view allow you to not have the processing of the view unless the the data changes?
November 10, 2008 at 6:31 am
Thanks for the well written, clear article. I like to see new authors give it a go. Consider updating this article to remove the RBAR, as suggested by the discussion. I hope to read your followup article soon, entitled "Normalizing-Denormalized tables Part II: Insert Instead-Of Triggers".
Paul DB
November 10, 2008 at 6:51 am
I want to set the record straight. I just read Mohit Gupta's piece on normalizing a not normalized table (Nov 10, '08). I accidently rated it with one star when I really wanted to rate it 5 stars. I hope I have not screwed up the average too badly. Kayuca.
November 10, 2008 at 7:20 am
I agree with using the SELECT ... FOR XML PATH('') to get a set based operation. Not only does it remove the cursor, but it greatly simplifies the code.
What I would recommend doing for it though is to instead of using all of the replace statements, is to:
declare @Groups varchar(max)
select @Groups = (
select '|' + G.GroupName
FROM Resource R
INNER JOIN ResourceSecurityGroup RG ON R.ResourceID = RG.ResourceID
INNER JOIN SecurityGroup G ON G.GroupID = RG.GroupID
WHERE R.ResourceID = @ResourceID
FOR XML PATH(''))
set @Groups = substring(@Groups, 2, 1024)
return @Groups
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2008 at 7:36 am
Thank-you everyone on the feed back :).
I been working on the 2nd part of this article. Just been busy with studies for MCITP and my masters. But I plan to get that out soon; and I really appreciate the comments. I will address all the comments here.
When I did this solution we didn't have too many updates. We had a few updates where we had to add groups for some resources and remove it for others and our group name followed fully-quantified active-directory name (OU=Domain,OU=Dept,OU=Grp1|OU=Domain,OU=Dept,OU=Grp2|etc...) so if you miss a comma or pipe ("|") it caused issues. So even though I had to take a bit of a performance hit I figured it was worth the headache we kept running into. But I must admin I did not consider XPATH or String concatenation for the function as a solution. Thanks again for all the tips.
I didn't create index for the view to improve the peformance further, I can also look at doing that in part-2.
I'll try talking to SSC editors to see maybe I can revise my original article. So to eliminate the RBAR issues; and fix any other problems that have been identified.
Again, thanks a lot folks.
- Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply