spgaddipati
asked on
Effect of number of columns on databse performance
Hi
I have a question about the performance of databse when an additional colums is added.
The new column is kind of redundent. Both columns never retrieved at same time ever using a query.
only insert occurs simultaniously.
I have a table column name summary this is text data type column.
I planning to add another column summary_onlytext. this is required because the summary column has lot of html tags in it. for keeping format etc... ths new column will have only the text with any html tags. this is required as I donot want to go against the html tags when user do a text serch on this column.
I woulod appriciate if you could provide soem links that will provide additional information
I have a question about the performance of databse when an additional colums is added.
The new column is kind of redundent. Both columns never retrieved at same time ever using a query.
only insert occurs simultaniously.
I have a table column name summary this is text data type column.
I planning to add another column summary_onlytext. this is required because the summary column has lot of html tags in it. for keeping format etc... ths new column will have only the text with any html tags. this is required as I donot want to go against the html tags when user do a text serch on this column.
I woulod appriciate if you could provide soem links that will provide additional information
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
in case you could make that additional column a computed column, you do not loose any storage directly, but you could index it, and query via it. and you don't need to keep it updated "manually" when the main column get's updated...
Don't know if you can have a text column as a computed column, or how you would compute against a text column.
If in SQL 2005, VARCHAR(MAX) probably would work as a computed column.
If in SQL 2005, VARCHAR(MAX) probably would work as a computed column.
ASKER
I am affried i can not use computed column. When you say computed column will it be in the table or in a view.
If in a view then i cancan not do a text search on the view.
Thanks,
Siva
If in a view then i cancan not do a text search on the view.
Thanks,
Siva
>When you say computed column will it be in the table
computed columns are in a table directly
computed columns are in a table directly
ASKER
Thanks for the infor.
Can I create a computed column in sqlserver 2000?
what is the effect on the performance while retrieveing the data using a select statement or view or stored procedure with larger data present in table but not retrieving the all the data (only retrieving few columns)
Can I create a computed column in sqlserver 2000?
what is the effect on the performance while retrieveing the data using a select statement or view or stored procedure with larger data present in table but not retrieving the all the data (only retrieving few columns)
>Can I create a computed column in sqlserver 2000?
yes
>what is the effect on the performance while retrieveing the data using a select statement or view or stored procedure with larger data present in table but not retrieving the all the data (only retrieving few columns)
the more columns you retrieve, the more network bandwidth is used, and the more RAM for storing the intermediate results on the server AND on the client are needed.
however, those differences are quite small, compared to the overall working time.
of course, adding many large columns to the SELECT can influence the time measurably.
yes
>what is the effect on the performance while retrieveing the data using a select statement or view or stored procedure with larger data present in table but not retrieving the all the data (only retrieving few columns)
the more columns you retrieve, the more network bandwidth is used, and the more RAM for storing the intermediate results on the server AND on the client are needed.
however, those differences are quite small, compared to the overall working time.
of course, adding many large columns to the SELECT can influence the time measurably.
ASKER
You mean to say i does not matter how amany columns present and how much data presemt in the table.
All matters how much data you are retrieving?
Can you suggest a good book or article about this.
Thanks,
Siva
All matters how much data you are retrieving?
Can you suggest a good book or article about this.
Thanks,
Siva
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot that clarifies a lot a of questions on mind.
One more clarification.
what is the effect of joins in this scenario. meaning instead of keeping two big colums in one table if I split and join when required what is the effect.
i would appriciate if you could give the explanation technically like above.
I am increasing the poins to 100
Thanks,
Siva
One more clarification.
what is the effect of joins in this scenario. meaning instead of keeping two big colums in one table if I split and join when required what is the effect.
i would appriciate if you could give the explanation technically like above.
I am increasing the poins to 100
Thanks,
Siva
>meaning instead of keeping two big colums in one table if I split and join when required what is the effect.
you will have to perform 2 inserts instead of 1 insert, and potentially have to make it 2 updates instead of 1 update, and 2 deletes instead of 1 delete.
-> you have some considerable overhead for the insert, update and delete statements
if you need both columns, you will have to query 2 tables (ie 2 pages) instead of 1 table (1 page).
if you need only 1 of the 2 columns, you will have 1 page to read, but which can hold more rows potentially...
this looks like this is, for performance point of view, almost never benefical, but don't get confused by those "simple" maths, because in practice, the effects are not that much. the effects of having to read or write to 2 tables (pages) instead of 1 are not doubling the work, as now you have doubled the capacity of the rows per page by splitting it into 2 tables, as you reduce the number of columns per table (page)
also, when you hit the limitations, you have not a lot of choice, anyhow.
you will have to perform 2 inserts instead of 1 insert, and potentially have to make it 2 updates instead of 1 update, and 2 deletes instead of 1 delete.
-> you have some considerable overhead for the insert, update and delete statements
if you need both columns, you will have to query 2 tables (ie 2 pages) instead of 1 table (1 page).
if you need only 1 of the 2 columns, you will have 1 page to read, but which can hold more rows potentially...
this looks like this is, for performance point of view, almost never benefical, but don't get confused by those "simple" maths, because in practice, the effects are not that much. the effects of having to read or write to 2 tables (pages) instead of 1 are not doubling the work, as now you have doubled the capacity of the rows per page by splitting it into 2 tables, as you reduce the number of columns per table (page)
also, when you hit the limitations, you have not a lot of choice, anyhow.