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 ««12

Existing Table division Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 3:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
Those are very general benefits but it sounds like you've convinced yourself anyway.

If you are talking about having a seperate detail and summary table then it's not vertical partitioning because it isn't a 1:1 relationship.

Post #1416386
Posted Wednesday, February 6, 2013 4:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
nick.mcdermaid (2/6/2013)
Those are very general benefits but it sounds like you've convinced yourself anyway.
Honestly speaking database design is under physical design phase so cant tell whether my approach is good or not ? thats the reason i posted the question here.


nick.mcdermaid (2/6/2013)
If you are talking about having a seperate detail and summary table then it's not vertical partitioning because it isn't a 1:1 relationship.
i didnt get this , please explain .what i inderstand here is for every claimID from claim table there must be a record in claim_summary table.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1416389
Posted Wednesday, February 6, 2013 5:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:19 PM
Points: 178, Visits: 581
Honestly speaking database design is under physical design phase so cant tell whether my approach is good or not ? thats the reason i posted the question here.


I really think you're over analysing it for that number of records. As far as DBA performance goes nowadays the focus is less on splitting components of tables over physical disks and more about getting everything into RAM. (as compared to when I did the SQL 2000 MCDBA) You will seriously see no performance advantage in vertically splitting data accross disks if you have only a couple thousand records.

i didnt get this , please explain .what i inderstand here is for every claimID from claim table there must be a record in claim_summary table.


If that's the case then yes it's 1:1. My mistake - usually a 'summary' table is a summary of some kind of detail and has less rows and less detail but in your case the summary appears to be additional generated information.



Looking at your original question, are the claim summary calculations based solely on columns in the claims table? Because you can simply use calculated columns in the same table for that. Then you don't need SSIS at all (and I would argue you still don't need it anyway)


I am of the opinion that a simple design is a good design and adding SSIS and filegroups and vertical table partitioning to a table with a few thousand rows is way over the top. But everyone has made good contributions here and some valid reasons have indeed been given to split it.
Post #1416446
Posted Wednesday, February 6, 2013 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 7,126, Visits: 12,726
I tend to agree that for a few thousand rows you'll find that the effort to split the tables will not be worth the gains you might receive, not to mention that it may actually perform poorly in some scenarios. That said it may still be worth trying to implement if you have the time to code it and test it. If you decide not to implement it in production you will at least have gone through the process of making an informed decision and there is value in that. Just make sure you're weighing the maintenance costs going forward with the performance gains you may achieve.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1416532
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse