What are solutions for a way-too-big database?
-
Hi guys! I'm a software developer and not very good in database designing. One day, I was asked something like this : "For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?" At first, I thought that was about using multiple database with single app. But I don't know if I was right. I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously? I appreciate any replies. Thanks!
................................................... ............ I have nothing to lose ............... ...................................................
-
Hi guys! I'm a software developer and not very good in database designing. One day, I was asked something like this : "For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?" At first, I thought that was about using multiple database with single app. But I don't know if I was right. I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously? I appreciate any replies. Thanks!
................................................... ............ I have nothing to lose ............... ...................................................
This is a large topic but some keywords you could lookup (taken this is SQL Server): - (better) indexing - partitioning - using filegroups to distribute data across disks - using well optimized views - transferring workload to another database using different techniques to copy the data from original db (may not be possible) - etc etc. - and last but not least, redesign of the database I'd say that the key thing is to try to make such modifications which are not visible to the application (expect in response time of course) so using directly multiple db's will cause large redesigns and also typically leads to different kinds of conflict situations.
The need to optimize rises from a bad design.My articles[^]
-
This is a large topic but some keywords you could lookup (taken this is SQL Server): - (better) indexing - partitioning - using filegroups to distribute data across disks - using well optimized views - transferring workload to another database using different techniques to copy the data from original db (may not be possible) - etc etc. - and last but not least, redesign of the database I'd say that the key thing is to try to make such modifications which are not visible to the application (expect in response time of course) so using directly multiple db's will cause large redesigns and also typically leads to different kinds of conflict situations.
The need to optimize rises from a bad design.My articles[^]
-
Hi guys! I'm a software developer and not very good in database designing. One day, I was asked something like this : "For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?" At first, I thought that was about using multiple database with single app. But I don't know if I was right. I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously? I appreciate any replies. Thanks!
................................................... ............ I have nothing to lose ............... ...................................................
Most importantly, have an expert review the SQL code. At one job I had I was assigned to add some functionality to a (batch) program and noticed some rather odd SQL. I asked about it and got the reponse, "If it ain't broke, don't fix it". In my opinion it was broke so I darn well fixed it. The result was that after I changed the SQL and added the functionality the program ran in a quarter of the time (ten minutes instead of forty).
-
Most importantly, have an expert review the SQL code. At one job I had I was assigned to add some functionality to a (batch) program and noticed some rather odd SQL. I asked about it and got the reponse, "If it ain't broke, don't fix it". In my opinion it was broke so I darn well fixed it. The result was that after I changed the SQL and added the functionality the program ran in a quarter of the time (ten minutes instead of forty).
-
Hi guys! I'm a software developer and not very good in database designing. One day, I was asked something like this : "For example, there is a company with a web application. One day, the database for that application is way too big, caused performance issues and others, what is the solution for that application database?" At first, I thought that was about using multiple database with single app. But I don't know if I was right. I want to ask that what are the solutions? If it's "multiple database" then what should I do? Using two connection to 2 database simutaneously? I appreciate any replies. Thanks!
................................................... ............ I have nothing to lose ............... ...................................................
One common technique is vertical splitting, so you hold only the most recent records in the (main) table and create a history table for the old ones. To achieve this you have to conditionally (based on the time frame) do a union on the history table, and also create a mechanism to shift records from your main table to the history table. Also, you can try to (as another poster wrote) optimize / add indexes, and/or invest in a speedier DB-server ..