SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Splitting


Table Splitting

Author
Message
DBTeam
DBTeam
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 150
Hi All,

I have 28+ million table with 30 columns. Now we want to split into
multiple tables.
It having the information
like name info, address info , company info, employees, sales, webaddress..

This having Rowid as identity data type and
email having primary key.

daily we want all these columns data.


Please suggest us the better way to split the table.
B'coz it hurts the performance of querying process.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)

Group: General Forum Members
Points: 186842 Visits: 33319
Without knowing more about the structure and what exactly is performing badly, it's going to be very hard to suggest solutions. Is the performance bad on inserts or on reads? If on reads, have you examined the execution plans to ensure that your indexes are being properly used? Are you maintaining the indexes and statistics?

If on inserts, it sounds like you might need to come up with a more normalized storage mechanism. That's a big topic. Again, it will be hard to advise you on what to do precisely in a post on a news group especially without adequate information. I would suggest getting more knowledgeable on general database design. Check out an old book, Handbook of Relational Database Design by Fleming and von Halle. That should move you in the right direction.

If you need more help, you'll need to post specifics. What is the existing structure? Where are your performance bottlenecks? That sort of thing.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)

Group: General Forum Members
Points: 432807 Visits: 43494
DBTeam (3/22/2010)
Hi All,

I have 28+ million table with 30 columns. Now we want to split into
multiple tables.
It having the information
like name info, address info , company info, employees, sales, webaddress..

This having Rowid as identity data type and
email having primary key.

daily we want all these columns data.


Please suggest us the better way to split the table.
B'coz it hurts the performance of querying process.


Are you importing this data into some form of staging table daily or ???

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC Guru
SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)SSC Guru (119K reputation)

Group: General Forum Members
Points: 119395 Visits: 9730
RedGate's SQL Refactor can do table splitting for you, but it might have trouble biting off that many rows all at once.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
DBTeam
DBTeam
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 150
Not daily ,monthly 5 lakhs we are inserting into it with the insert statement.

Daily we need to pull the records from that 20Million table.

Table is keep on increasing like this.now we want to split the table.

No idea how to do that.

How is that if we split vertically?(based on ID's and foreign keys)

But dalily we need all the columns data.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)

Group: General Forum Members
Points: 186842 Visits: 33319
If you're seeking to break it up in that way, then instead of ID & foreign key, maybe breaking it up by date makes more sense.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
DBTeam
DBTeam
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 150
we do not have Date column
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)

Group: General Forum Members
Points: 186842 Visits: 33319
Then possibly adding it might be in order. You've pretty much said that the access is by date. If you're principal access is by date, but you split it by foreign key, then instead of isolating the reads, you're going to spread them, making them less efficient instead of more efficient.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
DBTeam
DBTeam
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 150
I did not say that accessing by date.
I mentioned that there is a possibility of adding around 5 lakhs per month.

Only id that is identitity datatype is there
remaining all are text columns.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)SSC Guru (186K reputation)

Group: General Forum Members
Points: 186842 Visits: 33319
I'm sorry, I'm not there and I don't know your structure. When you said this earlier:

But dalily we need all the columns data.


I thought that meant that you needed to access data by time.

I still don't understand where you are seeing slow performance, and without that knowledge and full knowledge of your structure, suggesting how you can partition this data is not really possible.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search