Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Normalizing-Denormalized Tables Expand / Collapse
Author
Message
Posted Tuesday, November 11, 2008 8:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
Mohit (11/10/2008)
Thanks Jeff. I will play with this later today; if I may can I reference this work in the followup article? I plan to use this and what I did to also show the performance differences. Thanks.


Absolutely no problem. Thanks for asking. How many rows do you intend to use to show the performance differences?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601091
Posted Tuesday, November 11, 2008 10:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Hi Jeff,

I was planning to create 5 databases, each with different number of records. One with the original 30, second with 300, 3rd with 1000, 4th with 10000 and last with 100000. I might do more if my computer can handle it, but I think it will demostrate the point very clearly with that.

Thanks.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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.


How to ask for help .. Read Best Practices here.
Post #601127
Posted Tuesday, November 11, 2008 11:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
Mohit (11/11/2008)
Hi Jeff,

I was planning to create 5 databases, each with different number of records. One with the original 30, second with 300, 3rd with 1000, 4th with 10000 and last with 100000. I might do more if my computer can handle it, but I think it will demostrate the point very clearly with that.

Thanks.


Cool... you got something to generate those rows?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601152
Posted Wednesday, November 12, 2008 5:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
I like using Red-Gate Data Generation for mass row generation :). I find it it works really well, and gives me the flexibility by using the regular expressions and such.

EDIT: When I first used red-gate tool, I was really impressed. I used the tool on a database that had 100+ tables. With FK constraints, check constraints, and field names with familiar keywords like first name, last name, phone number, and the tool was able to take all that into account to generate data that made sense with lists for names, and using regular expression for phone numbers to generate 10 digit numbers. I told it to generate 10,000 rows. So it generated 10000 rows x 100 tables = 1,000,000 rows of data keeping all rules valid in less then 5 min. Really quick and impressive I must say :D.

- Mohit.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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.


How to ask for help .. Read Best Practices here.
Post #601277
Posted Wednesday, November 12, 2008 6:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
Very cool... I didn't know about the product. Thanks.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601289
Posted Wednesday, November 12, 2008 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
Hi. Nice article. I enjoy a challenge to remove RBAR. Since I went ahead and made it, I'll add mine to the non-cursor alternatives with no temp tables.

Toni
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION CombineGroupNames ( @ResourceID int )
RETURNS VarChar(2000)

AS
BEGIN

Declare @list varchar(2000)

set @list = ''

select @list = case @list when null then groupname else @list + groupname + '|' end
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

if Len(rtrim(@list)) > 0
begin
if charindex('|',@list,Len(@list)-1) > 0
select @list = Substring(@list,0,len(@list))
end

return @list
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Post #601368
Posted Wednesday, November 12, 2008 8:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
I removed a post relating to the Coalesce. Figured out I was being a dummy about it. Apologies.

Toni
Post #601420
Posted Wednesday, November 12, 2008 8:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:45 AM
Points: 88, Visits: 369

Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?

--Jeff Moden


I'm just saying it would be nice. Do you have to go to that much effort to sum a numeric field? Why shouldn't accumulating text be as easy.
Post #601431
Posted Wednesday, November 12, 2008 4:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, August 23, 2014 10:43 AM
Points: 942, Visits: 1,064
Toni Thank-you for the suggestion :).

I started working on the part-2 of this article, I will talk about all the suggestions in that article. If there are any more suggestions please feel free to make them. I am really greatful for all the help here :D.

- Mohit.


---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
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.


How to ask for help .. Read Best Practices here.
Post #601777
Posted Wednesday, November 12, 2008 5:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 35,549, Visits: 32,144
Ed Klein (11/12/2008)

Can we see the code for the function, Ed, or are you just saying that it would be nice to have one?

--Jeff Moden


I'm just saying it would be nice. Do you have to go to that much effort to sum a numeric field? Why shouldn't accumulating text be as easy.


Got it... thanks, Ed.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601786
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse