Well I am not sure, what it called in English or any other language but in Pashto we say, A half scholar is deadly danger for your knowledge, and a half doctor is deadly danger for your health.
The same principle apply to every field including database designing, I came to this conclusion when I encounter such practice I am currently analyzing for optimization.
Some people use a technique for publishing data, the data that is formatted for some particular purpose to be access with optimum performance, for this purpose the removed some checking constraints, and de-normalize it.
De-normalize means you will have no need to put join in your query and all your desired data will be in a single table, and that table might be used for a single purpose. The constraints are removed for faster writing, because you might be updating it too quickly in a background/a-sync process, as well the query used for extracting information might be expensive.
But I have seen example of joining tables that were de-normalized and some constraints were removed, the performance of was too bad, there was a lot of I/O read, CPU utilization and more then 20K Operator cast.
I was technically not allowed to make some physical design changes in the current system, so I had to stay with the current design and optimize it to some acceptable level.
I put some clustered and non clustered indices on main table and on associated tables. I get some 40% performance, and when I put some statistics on few columns I was amazed by the performance I got 85 to 90% performance gain.
The conclusion is never max premature concepts and avoid using publish table for different purposes, what I suggest always use a single publishing table for a single purpose.
The same principle apply to every field including database designing, I came to this conclusion when I encounter such practice I am currently analyzing for optimization.
Some people use a technique for publishing data, the data that is formatted for some particular purpose to be access with optimum performance, for this purpose the removed some checking constraints, and de-normalize it.
De-normalize means you will have no need to put join in your query and all your desired data will be in a single table, and that table might be used for a single purpose. The constraints are removed for faster writing, because you might be updating it too quickly in a background/a-sync process, as well the query used for extracting information might be expensive.
But I have seen example of joining tables that were de-normalized and some constraints were removed, the performance of was too bad, there was a lot of I/O read, CPU utilization and more then 20K Operator cast.
I was technically not allowed to make some physical design changes in the current system, so I had to stay with the current design and optimize it to some acceptable level.
I put some clustered and non clustered indices on main table and on associated tables. I get some 40% performance, and when I put some statistics on few columns I was amazed by the performance I got 85 to 90% performance gain.
The conclusion is never max premature concepts and avoid using publish table for different purposes, what I suggest always use a single publishing table for a single purpose.
No comments:
Post a Comment