Existing Table division

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Existing Table division

14 posts, Page 1 of 2

1 2 »»

Existing Table division

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Friday, February 01, 2013 4:54 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

I have a table "Claim" which contains 85+ columns. records in thousand(Not High-transactional table). The data get populated with the help of application (users enter it ) in around 60 columns and for other column's population(updated) based on some calculations (by ssis package) .

first table "Claim" will contain all the columns which get directly populated by users.

second table "claim_summary" will contain all calculation based columns and will get populated by ssis and i am thinking to putting it on another filergroup(separate disk).

There is another the reason of spliting the table that is some basic level recports get the data directly from "claim" table while advanced level reports from "claim_summary" + couple of columns from claims table .

so below are the advantages i can get here
1) better management/maintenance
2) performance gain as individual indexes for basic and advance reports
3) in case of basic reports only claim table will get touched.
4) when SSIS package will run it wil also touch "claim_summary" table.
5) readabiliy will also get increased.

Please correct me if i am wrong.

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1414579

opc.three

Posted Friday, February 01, 2013 6:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

Sounds like a solid plan. Is it true that claim to claim_summary will be a 1 to 1, i.e. you're just offloading some columns from claim into this new table claim_summary?

The tradeoff I am seeing is that while you're reducing the size of the claim you may be adding a JOIN into your batch process and reports to bring the whole row back together. That could amount to a minor amount of complexity and overhead but its worth considering how that would affect things.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1414651

Bhuvnesh

Posted Sunday, February 03, 2013 10:42 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

opc.three (2/1/2013)

Is it true that claim to claim_summary will be a 1 to 1, i.e. you're just offloading some columns from claim into this new table claim_summary?

Yes there would be 1 to 1 relationship

opc.three (2/1/2013)

The tradeoff I am seeing is that while you're reducing the size of the claim you may be adding a JOIN into your batch process and reports to bring the whole row back together. That could amount to a minor amount of complexity and overhead but its worth considering how that would affect things.

Yes that is my concern too. IF i place these two tables on different disk . will i get benefit in IO context ? or should i place them on same disk ? one more advantage which i am seeing here is during the ssis package only claim_summary table/disk will get affected

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1415107

opc.three

Posted Monday, February 04, 2013 6:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

opc.three (2/1/2013)

Yes that is my concern too. IF i place these two tables on different disk . will i get benefit in IO context ? or should i place them on same disk ? one more advantage which i am seeing here is during the ssis package only claim_summary table/disk will get affected
[/quote]
You may see a benefit but I am not sure how much. Also, a trade-up in one area usually means a trade-down in another. Do some reading on vertical partitioning and test to see which method benefits you most.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1415266

nick.mcdermaid

Posted Tuesday, February 05, 2013 2:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:56 PM
Points: 160, Visits: 420

For thousands of records performance will not be an issue. But it is good that you are thinking about it this early.

Post #1415648

Bhuvnesh

Posted Tuesday, February 05, 2013 10:48 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

i just came to know about one problem while reading an steve jone's article http://www.sqlservercentral.com/blogs/steve_jones/2010/02/14/saving-space-_1320_-vertical-partitioning/
lets say we have one table

create table Customers( CustomerID int identity(1,1), Firstname varchar(50), Lastname varchar(50), Address1 varchar(50), Address2 varchar(50), city varchar(50) )

and we split this table into two

create table Customers ( CustomerID int identity(1,1), Firstname varchar(50), Lastname varchar(50))

And

create table Customer_Details ( CustomerID int , Address1 varchar(50), Address2 varchar(50), city varchar(50))

How we are going to manage customerID values in both tables when in one of table it is identity ?

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1416245

nick.mcdermaid

Posted Tuesday, February 05, 2013 11:03 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:56 PM
Points: 160, Visits: 420

The stored procedure (or application layer object) that creates the record takes care of that. For example you create the record in the first table, use SCOPE_IDENTITY() to get the identity just created, and use it in the next table.

But seriously you are not going to require vertical partitioning for a table with thousands of records. In fact it's quite an extreme design measure to take, it adds a lot of complexity so you need some very good reasnos for doing so.

Post #1416248

Bhuvnesh

Posted Wednesday, February 06, 2013 12:24 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

nick.mcdermaid (2/5/2013)

But seriously you are not going to require vertical partitioning for a table with thousands of records. In fact it's quite an extreme design measure to take, it adds a lot of complexity so you need some very good reasnos for doing so.

The prime objective is tio divide the load of SSIS package's population and manual population plus reports will also pull the records.Readability will also have benefit

Suggestion please

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1416276

nick.mcdermaid

Posted Wednesday, February 06, 2013 2:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:56 PM
Points: 160, Visits: 420

Why do want to divide the load? Do you have a performance or design issue requiring this? For population of summary table you could also use views, triggers and/or a stored proc which makes the data real time as opposed to having to run a scheduled external SSIS Package.

I don't really understand the problem you're trying to solve.

Post #1416321

Bhuvnesh

Posted Wednesday, February 06, 2013 3:20 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

nick.mcdermaid (2/6/2013)

so below are the advantages i can get here
1) better management/maintenance
2) performance gain as individual indexes for basic and advance reports
3) in case of basic reports only claim table will get touched.
4) when SSIS package will run it wil also touch "claim_summary" table.
5) readabiliy will also get increased.

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1416364

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions