Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Database Design
Options
-
22-05-2013 12:35pm#1
Comments
-
-
-
Sounds a bit like an assignment question
1. 10k new records per day isn't that large. That's only 3.5m records per year. General idea with most database servers is to avoid rewriting data unless you need to. For example, in order to clear out yesterday-1's data, you still need to put it somewhere because you might still need it. This involves taking it out of the "Live" database and putting it into another database just in case you need to access it. That's big overhead for very little return. For performance improving tables where data sets are unevenly accessed, what you want is table partitioning. Basically what this does is allow you to move data onto different files while the data all still appears to reside in the same table. Data can be partitioned on basically any criteria.
So for example, you can set up 3 partitions - one for data less than a week old, one for data between a week and six months old, and one for all data over six months old. The data up to a week old can sit on an SSD (it's a small dataset), the stuff up to six months old could sit on a standard drive in the server, and the rest of the stuff (which is nearly never accessed) could sit on a 10Gb network link.
When you go to retrieve data from the table, you don't care how old it is, MSSQL will handle the retrieval. Likewise MSSQL can handle the dynamic partitioning - you don't need to go in every day and move data yourself.
Having separate tables/databases for different days is just a complete mess.
2. Long story short, SPs are faster and less likely to be compromised by SQL injection. From the front-end developer's point of view though they're less flexible and harder to debug. SP -v- query is a long running debate. SPs major drawback is that you're inexorably tied to the database platform and possibly even the version of the database software. If you try to move platform, you have to rewrite your SPs (or your frontend code) from the ground up, and software upgrades can kill your application if syntax or native functionality changes. I would tend to say that if you're a programmer who needs a database, avoid stored procedures. If you're a database developer providing functionality to a front-end programmer, use SPs.
3. Transaction logs provide point-in-time recovery of a database. So while a database backup allows you to restore the database to the time you backed it up, the transaction log allows you to "replay" all of the transactions which took place since the last full backup. Meaning that you can restore almost all of your data. Your current process allows you to copy data from a backup database, but presumably this is, "Restore data for row X in table Y". What would you do if your entire live database fell over and the data was unrecoverable? How would you use your secondary database to restore your live database to the condition it was just before it died?
If point-in-time recovery isn't that important, or you're confident that your secondary database could be used to recover the live one completely, then you can turn off transaction logging by changing the database recovery mode to SIMPLE*. This means that if you lose the database, you can only recover data back to the last full backup.
If you decide to stick with transaction logging, there are two ways to keep the size down:
1. Perform more frequent full database backups. A full DB backup empties the transaction log (because it's only needed to restore data from after the last full backup). This however is not ideal from a performance POV.
2. Perform frequent transaction log backups. You can backup the transaction log on its own, moving it to a backup disk or whatever. Transaction log backups don't interfere with performance because you don't need to access any database resources while you're doing it.
1 is quicker than 2 for performing full database restores.
*Unless you are using transaction log shipping for your backup database0 -
Sounds a bit like an assignment question
... SPs major drawback is that you're inexorably tied to the database platform and possibly even the version of the database software. If you try to move platform, you have to rewrite your SPs (or your frontend code) from the ground up, and software upgrades can kill your application if syntax or native functionality changes. I would tend to say that if you're a programmer who needs a database, avoid stored procedures. If you're a database developer providing functionality to a front-end programmer, use SPs.
I'm not quite sure what you are getting at here. If you have all your sql inline in the front end code then you are also tied to the database platform. similarly any version change that requires a rewrite will affect inline sql as much as SPs. Having it all in SPs also means that moving platforms is actually easier. All your database logic is in one place rather than scattered around inside client side code.
Think of a situation where an update to one table requires updates of associated records. would that do that using inline sql and handle the transactions in client side code or would you just write a sp that does this for in the language best suited to writing proper transactions?
Another point worth noting is that if your sql code is in SPs then hotfixing any database related problems is incredibly easy. No code rebuilds required.
There is nothing to be gained by using inline sql. It is the work of the devil and its practitioners should have their developer licenses removed.0 -
-
Advertisement
-
-
-
-
Thanks for all the replies! All the info posted has been useful and given me more to think about.Colonel Panic wrote: »Is this for an actual application? Is the data actually relational or are you just dumping loads of records some data store?
Making multiple databases to me defeats the purpose. Your issue seems to be one of those known problems that is solved with things like indexing, table partitioning and database clustering.
Yes, I am making an actual information system to be used by 50+ users. As you can guess, I am not a pro dev. It is a hobby, but I think I can do a sufficient job on this application rather than paying for it.
Thanks for the hint re table partitioning - I didn't even know I could do that!Sounds a bit like an assignment question
1. 10k new records per day isn't that large. That's only 3.5m records per year. General idea with most database servers is to avoid rewriting data unless you need to. For example, in order to clear out yesterday-1's data, you still need to put it somewhere because you might still need it. This involves taking it out of the "Live" database and putting it into another database just in case you need to access it. That's big overhead for very little return. For performance improving tables where data sets are unevenly accessed, what you want is table partitioning. Basically what this does is allow you to move data onto different files while the data all still appears to reside in the same table. Data can be partitioned on basically any criteria.
So for example, you can set up 3 partitions - one for data less than a week old, one for data between a week and six months old, and one for all data over six months old. The data up to a week old can sit on an SSD (it's a small dataset), the stuff up to six months old could sit on a standard drive in the server, and the rest of the stuff (which is nearly never accessed) could sit on a 10Gb network link.
When you go to retrieve data from the table, you don't care how old it is, MSSQL will handle the retrieval. Likewise MSSQL can handle the dynamic partitioning - you don't need to go in every day and move data yourself.
Having separate tables/databases for different days is just a complete mess.
2. Long story short, SPs are faster and less likely to be compromised by SQL injection. From the front-end developer's point of view though they're less flexible and harder to debug. SP -v- query is a long running debate. SPs major drawback is that you're inexorably tied to the database platform and possibly even the version of the database software. If you try to move platform, you have to rewrite your SPs (or your frontend code) from the ground up, and software upgrades can kill your application if syntax or native functionality changes. I would tend to say that if you're a programmer who needs a database, avoid stored procedures. If you're a database developer providing functionality to a front-end programmer, use SPs.
3. Transaction logs provide point-in-time recovery of a database. So while a database backup allows you to restore the database to the time you backed it up, the transaction log allows you to "replay" all of the transactions which took place since the last full backup. Meaning that you can restore almost all of your data. Your current process allows you to copy data from a backup database, but presumably this is, "Restore data for row X in table Y". What would you do if your entire live database fell over and the data was unrecoverable? How would you use your secondary database to restore your live database to the condition it was just before it died?
If point-in-time recovery isn't that important, or you're confident that your secondary database could be used to recover the live one completely, then you can turn off transaction logging by changing the database recovery mode to SIMPLE*. This means that if you lose the database, you can only recover data back to the last full backup.
If you decide to stick with transaction logging, there are two ways to keep the size down:
1. Perform more frequent full database backups. A full DB backup empties the transaction log (because it's only needed to restore data from after the last full backup). This however is not ideal from a performance POV.
2. Perform frequent transaction log backups. You can backup the transaction log on its own, moving it to a backup disk or whatever. Transaction log backups don't interfere with performance because you don't need to access any database resources while you're doing it.
1 is quicker than 2 for performing full database restores.
*Unless you are using transaction log shipping for your backup database
Excellent info, thanks. I think I will look into your suggested partitioning strategy of having 3 partitions for the different date ranges.
It looks like I will go the Stored Procedure route. The last time I did such a project I used all inline code. It became a bit of a mess to read/debug to be honest.
Another question I have is related to indexes. I ran a query on
sys.dm_db_index_physical_stats on the DB from my last project and the average fragmentation % is 27%!! Is there a way to prevent this or is it just a case of reorganising indexes frequently? Any tips in this regard?
Thanks again to all who have posted.
SS
0 -
-
Advertisement
-
-
-
-
-
-
-
-
Please steer clear of stored procedures..
Use an ORM.
You could do this very simply using hibernate or entity framwork depending on whether you are using java or .NET.
Entity Framework for example will generate a very basic DAL for you, which will done fine for something as small as what you are doing.0 -
Advertisement