Spliting a table.
-
Does it make any performance benefit if we split our records across more tables instead of just one table ?
there could be... provided the database is hosted on raid and the tables are located in diffetent physical files.
-
Does it make any performance benefit if we split our records across more tables instead of just one table ?
Yes, split the records in more than one table is a part of normalization. I am sure if you keeps records in a single table then defintely it will consist redundant (duplicate) information. So that is why we split our records across more that one table on the basis of any keys. This is the part of normalization.
Parwej Ahamad R & D with IIS 5.0/6.0
modified on Thursday, June 5, 2008 1:36 AM
-
Does it make any performance benefit if we split our records across more tables instead of just one table ?
Depends on many factors, such as the amount of data, how/what you'll split up, the set up of the database, how you measure performance etc. Generally speaking you can reduce IO if you split sensibly, take a look at the PARTITIONING of tables in SQL Server 2005 (if you use that, and other vendors might have similar functionality),which automates this a bit and which seriously can increase read performance, but can hurt write performance a little bit. Some simple tests I've done for myself showed in situations that I could get ~66% faster read simply by partitioning. Also as suggested by a previous poster, if your tables aren't normalized, there could be a gain from simply doing that to avoid redundant data, and thus decrease the number of operations with read/write.
--------------------------- Blogging about SQL, Technology and many other things
-
Yes, split the records in more than one table is a part of normalization. I am sure if you keeps records in a single table then defintely it will consist redundant (duplicate) information. So that is why we split our records across more that one table on the basis of any keys. This is the part of normalization.
Parwej Ahamad R & D with IIS 5.0/6.0
modified on Thursday, June 5, 2008 1:36 AM
interesting, I assumed he had a normalised DB and wanted to split a very large table to improve performance. If that is the case then I suggest you use filegroups (sql server) and have each file group on a different physical drive. The next step after that is to use partitioning to segregate your data. Performace tuning and maintenance of a good data structure is an ART and DBAs study for years to achieve a high level of skill, you will need to put in some serious study time to get a feel for this area.
Never underestimate the power of human stupidity RAH
-
Does it make any performance benefit if we split our records across more tables instead of just one table ?
It depends. Are you talking about splitting the rows (for example having 1 table for 2008 data, 1 for 2007 etc) or splitting the columns (for example taking the address out of the customer table into an address table). If its the former you will probably only see gains if you have very large tables, but properly partitioned there can be significant benefits where large volumes of data are involved. If it the latter you are talking normalisation. Before leaping into too much normalisation take a look at your data useage. Normaalised data will result in joins when querying and this may have a detrimental effect on performance. For performance it is sometimes better to use denormalised data. Its all down to volumes and use really. Hope this helps
Bob Ashfield Consultants Ltd
-
there could be... provided the database is hosted on raid and the tables are located in diffetent physical files.
-
Member 3301325 wrote:
the tables are located in diffetent physical files.
How can I know if the tables are located in different physical devices and how can this be possible at all ? I meant the tables in question belong to one database.
this is possible even if they are in same database. alll relational databases, sql server/oracle supports this. create separate tablespaces and let the tablespaces be in separate files in separate disks... & then create the tables in separate tablespaces...
-
this is possible even if they are in same database. alll relational databases, sql server/oracle supports this. create separate tablespaces and let the tablespaces be in separate files in separate disks... & then create the tables in separate tablespaces...
Member 3301325 wrote:
create separate tablespaces and let the tablespaces be in separate files in separate disks... & then create the tables in separate tablespaces...
Can you give me a clue how I can create table spaces in sql server and create tables in them ?